Creating static pages from a database

For my most recent project I will be creating a sign-up sheet that will generate static pages when the information is entered. The reason the pages will be static is so they can be crawled by search engine spiders. A spider cannot read dynamically created pages because it only looks at what is stored on the server and that would be the php script which is not friendly at all to them. The only block of information that I will not have to store in the database is the text block because it will only be written out to the pages for that particular user. The rest of the information will have to be called from other pages.

Creating the page for the vendor is easy (if you've used xtemplates before). Its just a matter using xtemplate function xtpl->assign() to replace the text block and other info where it is needed.

The first mildly challenging aspect I encountered was the multiple select list where the user can choose multiple options with ctrl-click as so:
Select an optionoption1option2option3


This data has to be stored somewhere and the most difficult part was getting the array to pass to the php script. In my script I had:

if(isset($cities)){
  foreach($cities as $value){
   $query="INSERT IGNORE INTO `***` (`user`, `cities`) VALUES ('".$user_name."', '".$value."');";
   mysql_query($query) or die('Failed to update user to cities: '.mysql_error());
  }
 }

< br />
Which I was sure would work but I kept getting an error on my foreach statement. After some research on html forms I found that the value to be passed as an array to a script had to be defined as name="my_name[]". Html needs the [] to show the script that it is an array otherwise it will just pass the first value. Obviously a foreach() statement won't work on an non-array type so this is why that code was breaking.

The next bigger challenge I have is to create site maps to all of these pages being created and also create links on the bottom of the user's page to some pages from other users, based on group, as kind of a minimap. Again all my pages have to be static so php cannot reside on the page and call the database. I looked into the Apache server mod_rewrites and there is no way to create a static page with php on it. My first idea was to write a script to parse the php pages using the ob_start() function which will store all proceeding information on an internal buffer which is sent to the browser's buffer on ob_end_flush(). This buffered content (which will now be parsed by the browser) can be stored in any string. I was then going to take this content and write it to a new .html file under the same name. This takes a lot of time because of the amount of pages being created and buffered and space because I would have to keep the .php file in the event the database was updated I would have to update the .html.

But then I got an idea which was much simpler and straight forward and would save all the extra work from my previous idea. My idea is to not even put the php on the page in the first place. I can just use the xtpl->assign('var', 'content') function here as well and put the php code in the content as xtpl->assign('var', 'php_code'). The browser will parse the php code before it gets sent to the xtpl function saving me a lot of work. The only thing I need to do now is to make my code more modular so I can write a script to update the pages using this philosophy every so often for when the database is updated.

So since I can't do php in a comment...
I think that the bitwise or was probably the cause of the problem. I actually didn't notice until you pointed it out. But I just ended up re-doing the database (basically copy and paste) so I could create this much nicer query:

$query = "select * from Artists"
$result = mysql_query($query)
if (mysql_num_rows($result) == 0){
echo 'nothing here...'
}
else{
while ($row = mysql_fetch_array($result, MYSQL_ASSOC)){
echo ""
$out = ""
echo $out
echo $row['state']
echo ""

$out = ""
echo $out
echo $row['city']
echo ""

$out = ""
echo $out
echo $row['venue']
echo "
"
}
}
This was just to get it to work so I could display something. When I'n not working on my new project i will try to work out the original setup (which I still kept incase). As it is right now it is very difficult to update the database because the amount of data for each column is different so there are a lot of blank rows. This just makes messy MySQL updates when trying to find the first blank row which isn't very efficient since I want to be able to upload multiple rows at a time.

Multiple MySQL Queries in PhP

The problem with using multiple queries from multiple tables is that the MYSQL_ASSOC or MYSQL_NUM both only seem to work on the latest query even if the queries and results are stored under different variables. Here is the code:
echo '

';
   require ('dbconnect.inc');
   $query_state = "select state from States;";
   $result_state = mysql_query($query_state);
   $query_city = "select city from Cities;";
   $result_city = mysql_query($query_city);
   $query_venue = "select venue from Venues;";
   $result_venue = mysql_query($query_venue);
   
   if (mysql_num_rows($result_state) == 0 | mysql_num_rows($result_city) == 0 | mysql_num_rows($result_venue) == 0){
    echo 'nothing here...';
   }
   else{
    while ($row1 = mysql_fetch_array($result_state, MYSQL_ASSOC) | $row2 = mysql_fetch_array($result_city, MYSQL_ASSOC) | $row3 = mysql_fetch_array($result_venue, MYSQL_ASSOC)){
     echo "";
     $out = "";
     echo $out;
     echo $row1['state'];
     echo "";
    
     $out = "";
     echo $out;
     echo $row2['city'];
     echo "";
     
     $out = "";
     echo $out;
     echo $row3['venue'];
     echo "
     ";
    }
   }
echo '

';
?>
And all that appears is the last column. The tags are being read correctly because the result remains in the correct column it just doesn't seem to read the first two queries.
My solution for now is just to move the tables into one table which isn't most efficient for the database end but I think that can be sacrificed for now for PhP efficiency.

Dynamically Creating Pages

So here's a funny story...recently I was trying to use PhP inside a page I have created dynamically. I was just using simple echo statements to try to output HTML to the page. Seems simple right? For some reason I couldn't figure out it just wasn't working. So I did a lot of research on single quotes and double quotes and PhP functions such as htmlspechialchars() and addslashes(). None of those worked. Then I decided to look at the function that was creating the page. Turns out it was creating a .HTML page and not a .php page so it could not read the PhP. I felt foolish after that. Anyway I learned some things I would have to deal with later so it wasn't a complete waste of time.