Page 1 of 2

Query two tables on same page depending on ID

Posted: Mon Aug 18, 2003 10:15 am
by yinka
Hey. I'm pretty new to php and am having a little trouble. I have one MySql table with 10 products that populates a HTML template based on an ID for each prouct. I also have 10 other tables that have ordering information for each product. What I am trying to do is bring up a product with a url such as ?id=3, and then bring up the ordering information of that page taken from the corresponding table. Here is the code that I am using, and following is the error that I am recieving. I can't seem to figure this out. Any help is greatly appreciated!

<?php

$product_array = array(
1 => "as",
2 => "ep",
3 => "es",
4 => "lc",
5 => "pen",
6 => "sb",
7 => "ssa",
8 => "ssamw",
9 => "ssb",
10 => "ssc",

//Some code here that gets the $product_id

$query = "SELECT * FROM products WHERE id = " . $_GET['id'] . ";";


//Then make the query
$query = "SELECT * FROM " . $product_array[$product_id] . " WHERE product_id = '$product_id'";
$result = mysql_query($query) or die(mysql_error());

?>
<table width="100%" border="0" cellspacing="0" cellpadding="0">
<tr>
<td valign="top">

<?php

while($row = mysql_fetch_object($result))
{

?>


<table width="100%" border="0" cellspacing="0" cellpadding="0">
<tr>
<td width="340" height="30"><?php echo($row->title); ?></td>
</tr>
<tr>
<td width="340"><?php echo($row->description); ?><p></p></td>
</tr>
<tr>
<td height="20">

You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHERE product_id = ''' at line 1[/i]

Posted: Mon Aug 18, 2003 10:24 am
by JayBird
Looks as if the $product_id variable isn't being parsed in your SQL statement. Check to see if $product_id actually contains a value. If it does, you could also try changing this line:

Code: Select all

$query = "SELECT * FROM " . $product_array[$product_id] . " WHERE product_id = '$product_id'";
to

Code: Select all

$query = "SELECT * FROM " . $product_array[$product_id] . " WHERE product_id = '".$product_id."'";

Posted: Mon Aug 18, 2003 10:33 am
by yinka
Hmm..Thanks Bech100, but I'm still getting the same error. This is very frustrating!

Posted: Mon Aug 18, 2003 10:35 am
by JayBird
did you check to make sure that $product_id s actually set?

Cos the error message you are receiving says not ('WHERE product_id = 'Should be a value here')

Posted: Mon Aug 18, 2003 10:39 am
by yinka
Actually I think your right. Maybe it is not a syntax error. I'm not sure how to do it though. Does my code look like it should work, or am I doing it wrong from the get-go?

Posted: Mon Aug 18, 2003 10:45 am
by JayBird
Try replacing this bit

Code: Select all

$query = "SELECT * FROM " . $product_array[$product_id] . " WHERE product_id = '$product_id'"; 
$result = mysql_query($query) or die(mysql_error());
with this

Code: Select all

if (isset($product_id)) {
$query = "SELECT * FROM " . $product_array[$product_id] . " WHERE product_id = '$product_id'"; 
$result = mysql_query($query) or die(mysql_error()); 
} else {
print "Product ID is not set!!!!";
}
If $product_id is set, the query will work, otherwise, it will print an error message.

Mark

Posted: Mon Aug 18, 2003 10:48 am
by yinka
You're right. It's not set. So I guess it is a problem with the whole thing. does it look like I did the array correctly?

Posted: Mon Aug 18, 2003 10:51 am
by JayBird
you need to show me the code that trys to get the $product_id

Posted: Mon Aug 18, 2003 10:53 am
by yinka
It was this part:

<?php

$product_array = array(
1 => "as",
2 => "ep",
3 => "es",
4 => "lc",
5 => "pen",
6 => "sb",
7 => "ssa",
8 => "ssamw",
9 => "ssb",
10 => "ssc",

//Some code here that gets the $product_id

$query = "SELECT * FROM products WHERE id = " . $_GET['id'] . ";";

Posted: Mon Aug 18, 2003 10:54 am
by JayBird
so, where are you actually setting $product_id

you should have a line somewhere that says something like $product_id = "a value";

Mark

Posted: Mon Aug 18, 2003 11:03 am
by yinka
Mark,

I thought that I was doing it when I linked to the URL. Say for instance if you click on Link the link products?id=1. It thought that that would get the ID in the table for the product with the ID of 1. I'm sorry, like I said I am very new to this.

Posted: Mon Aug 18, 2003 11:07 am
by JayBird
put this as your first line

Code: Select all

extract($_GET);
$product_id = $id;
THe variables you pass through the query string won't be automatically available in your script if your register globals setting is off, the above should solve the problem.

Is the code in your first post the entire script? if so, there are a number of errors in it that i have noticed.

If it isn't, could you post the entire code.

Mark

Posted: Mon Aug 18, 2003 11:11 am
by yinka
Yeah that was my entire code. I thought that I would probably have errors. I am trying your last post right now.

Posted: Mon Aug 18, 2003 11:15 am
by yinka
Hmm..I just got this error.

You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'as WHERE product_id = '1'' at line 1

Posted: Mon Aug 18, 2003 11:26 am
by JayBird
The error is because you are selecting from a table called "as", but "as" is a reserved MySQL keyword! :D

i've re-written a bit of your code too

Code: Select all

<?php 

extract($_GET);
$product_id = $id;

$product_array = array( 
1 => "as", 
2 => "ep", 
3 => "es", 
4 => "lc", 
5 => "pen", 
6 => "sb", 
7 => "ssa", 
8 => "ssamw", 
9 => "ssb", 
10 => "ssc",);

//Some code here that gets the $product_id 

$query = "SELECT * FROM products WHERE id = '" . $_GET['id'] . "'";  // Why is this line here? it will never get used!?


//Then make the query 
$query = "SELECT * FROM " . $product_array[$product_id] . " WHERE product_id = '$product_id'"; 
$result = mysql_query($query) or die(mysql_error()); 

?> 
<table width="100%" border="0" cellspacing="0" cellpadding="0"> 


<?php 

while($row = mysql_fetch_object($result)) 
{ 

?> 

<tr> 
<td width="340" height="30"><?php echo($row->title); ?></td> 
</tr> 
<tr> 
<td width="340"><?php echo($row->description); ?><p></p></td> 
</tr> 
<tr> 
<td height="20"> 
<tr>

<?php

}

?>