creating a dynamic template

[Roarke]

Forgive me if this is too long/too simple - I am new to this!

I have an htm page with a "post" form. This has 2 droplists, the variables of which are posted to a php page. This page queries a mySQL database and echoes in rows any matching records.

At present each result row lists the info from the database fields name, location and type. I had put an "a href=" around these results so if a row is clicked, it opened a seperate htm page with all the details of that particular file.

I have 1000 records on my database and currently have 1000 htm pages. This means I have to update1000 pages every time something changes.

What I would really like to do is have it so that when a row is clicked on, a php page is opened. this page is a template so that all the info that I need from the database is inserted into the page

By having a php template, I only need update 1 page as the info is different every time.

I enclose the code that I am using on the 2 php pages. Can anyone see why it isn't working? What do I need to do. I am right at the boundary of my understanding here!

List Page:

mysql_connect ($host, $user, $password)
or die ('I cannot connect to the database
because: ' . mysql_error());
 
 
mysql_select_db ($db);
 
 
 
$town = $_POST["town"];
$cuisine =$_POST["cuisine"];
 
if ($cuisine == "All Cuisines") {
 
$query = mysql_db_query($db, "SELECT *  FROM `trialrestaurants` 
WHERE `posttown` LIKE CONVERT(_utf8 '$town' USING latin1) COLLATE latin1_german2_ci");
 
}
else{
 
$query = mysql_db_query($db, "SELECT *  FROM `trialrestaurants` 
WHERE `posttown` LIKE CONVERT(_utf8 '$town' USING latin1) COLLATE latin1_german2_ci 
AND `cuisine` LIKE CONVERT(_utf8 '$cuisine' USING latin1) COLLATE latin1_german2_ci ");
 
}
 
 
echo "<table width='100%' border='0'  bordercolor='#000000' cellpadding='4' cellspacing='0'>\n";
 
$id = $row["ID"]; 
while ($row = mysql_fetch_array($query)) 
 
 
 
 
echo " 
<tr>\n<td align='left'><a href='details.php?id=$id'>".$row[name]."</td> 
<td align='left'><a href='details.php?id=$id'>".$row[location]."</td> 
<td align='left'><a href='details.php?id=$id'>".$row[cuisine]."</a></td> 
\n</tr>\n"; 
 
 
echo "</table>";

Template Page:

mysql_connect ($host, $user, $password)
or die ('I cannot connect to the database
because: ' . mysql_error());
 
 
mysql_select_db ($db);
 
$id = $_GET["ID"]; $details_table = "trialrestaurants"; 
$query = mysql_db_query($db, "SELECT * FROM 'trialrestaurants' WHERE 'ID' LIKE CONVERT(_utf8 '$id'USING latin1) COLLATE latin1_german2_ci ");
 $query = mysql_query($query) or die(mysql_error()); 
$numrows = mysql_num_rows($query); 
 
if ( $numrows > 0 ) { // if the id was found 
     while ( $details = mysql_fetch_array($query) ) { 
 
          echo "Town: ". $details['town']; 
          echo "Cuisine: ". $details['cuisine']; 
     } 
}else { // if it was not found 
     echo "error: invalid id."; 
} 
 

Thanks in advance for any help or ideas.

[NoComment]

I have 1000 records on my database and currently have 1000 htm pages. This means I have to update1000 pages every time something changes.

Seriously? You have written 1000 htm pages? :O It's MUCH MUCH easier to use just one PHP page (like you're apparently trying now).

I enclose the code that I am using on the 2 php pages. Can anyone see why it isn't working? What do I need to do. I am right at the boundary of my understanding here!

Please tell us EXACTLY why it isn't working. Do you get any error messages? If so, what do they say? What makes you think it isn't working.

Although I'm not sure about that, you might get rid of the quotes when denoting a column or table name. So

SELECT * FROM `trialrestaurants`
WHERE `posttown` LIKE CONVERT(_utf8 '$town' USING latin1) COLLATE latin1_german2_ci

becomes

SELECT * FROM trialrestaurants
WHERE posttown LIKE CONVERT(_utf8 '$town' USING latin1) COLLATE latin1_german2_ci

Try if this makes any difference.

EDIT:
Also, in your template php:

[PHP]
$query = mysql_db_query($db, "SELECT * FROM 'trialrestaurants' WHERE 'ID' LIKE CONVERT(_utf8 '$id'USING latin1) COLLATE latin1_german2_ci ");
$query = mysql_query($query) or die(mysql_error());
[/PHP]

Leave out the second line. You are trying to do two queries and to make it even worse you are submitting the result of the first one as a query string to the second query. ;)

The PHP manual also states:

This function (mysql_db_query) is deprecated, do not use this function. Use mysql_select_db() and mysql_query() instead.

[Roarke]

thanks for your reply/question.

When I put my mouse over the link the following is what is displayed in the status bar:
www.website.com/details.php?id=

When I click the link all that shows on the page is:
query was empty

Thanks for helping

[mwasif]

You are using single quotes instead of backticks around table name and column. Use backticks or remove them altogether e.g.
[PHP]$query = mysql_db_query($db, "SELECT * FROM trialrestaurants WHERE ID LIKE CONVERT(_utf8 '$id'USING latin1) COLLATE latin1_german2_ci ");[/PHP]

[Roarke]

Thanks for these replies. But no change.

It seems to me that I am not creating the ID variable. The 1st page sends the query to the 2nd page which picks up the info and echos it fine.

What isn't happening is that the id capture so when I go to page 3 there is nothing being carried over. Am I making myself clear?

If I try this:
snippet:

echo "<table width='100%' border='0'  bordercolor='#000000' cellpadding='4' cellspacing='0'>\n";
$id = $row["ID"]; 
 
while ($row = mysql_fetch_array($query)) 

I get query was empty,
but if I try this:
snippet:

echo "<table width='100%' border='0'  bordercolor='#000000' cellpadding='4' cellspacing='0'>\n";
 
while ($row = mysql_fetch_array($query)) 
 
$id = $row["ID"]; 

I get nothing at all.

It is this $id bit that is not working but I don't know why? or how to change it.

Ideas???

[kovik]

Sounds to me like you're not taking the suggestions correctly. That snippet you've given tells us nothing. What does your query look like now? Where is $query coming from?

[Roarke]

Volectricity,
I am sorry if it seems that I am not taking the advice correctly. I am trying to.
This is my first website, I am at the edge of my knowledge and if I seem slow or whatever, it is due to my lack of knowledge rather than anything else.
I only put snippets of the whole code I had previously posted as I didn't want to take too much space.

Here is the whole page I am having issues with.

<?php
$host = "host details";
$user = "user details";
$password = "password";
$db = "database details";
 
mysql_connect ($host, $user, $password)
or die ('I cannot connect to the database
because: ' . mysql_error());
 
mysql_select_db ($db);
 
$town = $_POST["town"];
$cuisine =$_POST["cuisine"];
 
if ($cuisine == "All Cuisines") {
 
$query = mysql_db_query($db, "SELECT *  FROM `trialrestaurants` 
WHERE `posttown` LIKE CONVERT(_utf8 '$town' USING latin1) COLLATE latin1_german2_ci");
 
}
else{
 
$query = mysql_db_query($db, "SELECT *  FROM `trialrestaurants` 
WHERE `posttown` LIKE CONVERT(_utf8 '$town' USING latin1) COLLATE latin1_german2_ci 
AND `cuisine` LIKE CONVERT(_utf8 '$cuisine' USING latin1) COLLATE latin1_german2_ci ");
 
}
 
echo "<table width='100%' border='0'  bordercolor='#000000' cellpadding='4' cellspacing='0'>\n";
 
$id =$_row["name"];
 
while ($row = mysql_fetch_array($query)) 
 
echo " 
<tr>\n
<td align='left'><a href='details.php?name=$id'>".$row[name]."</td> 
<td align='left'><a href='details.php?name=$id'>".$row[location]."</td> 
<td align='left'><a href='details.php?name=$id'>".$row[cuisine]."</a></td> 
\n</tr>\n"; 
 
echo "</table>";
?>
 

What I know does work is the town and cuisine searches. I know this because if I use the form that posts to this page I get rows of information echoed out as it should.

What isn't happening is the $id capture for the next page.

Before, when I had an "a href" straight to an htm page the links worked. Now when I click the link it does not seem to find $id variable.

I notice that the status bar is incomplete when I mouse over the link. I get "...details.php?name= " Surely there should be something after that equals.

Is line 32, $id=$_row["name"]; in the wrong place. Is the line wrong or do I need more.

Thanks if you can help

[kovik]

Is line 32, $id=$_row["name"]; in the wrong place. Is the line wrong or do I need more.

Yes, it is. You have tried to define $id as "$row['name']", but $row doesn't even exist yet. In fact, not once does your code check any variable for existence before attempting to use it.

Either put "$id=$_row["name"];" INSIDE of your loop, or instead of using "$id," just use "$row['name']."