Query two tables on same page depending on ID

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

yinka
Forum Newbie
Posts: 12
Joined: Mon Aug 18, 2003 10:15 am

Query two tables on same page depending on ID

Post 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]
User avatar
JayBird
Admin
Posts: 4524
Joined: Wed Aug 13, 2003 7:02 am
Location: York, UK
Contact:

Post 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."'";
yinka
Forum Newbie
Posts: 12
Joined: Mon Aug 18, 2003 10:15 am

Post by yinka »

Hmm..Thanks Bech100, but I'm still getting the same error. This is very frustrating!
User avatar
JayBird
Admin
Posts: 4524
Joined: Wed Aug 13, 2003 7:02 am
Location: York, UK
Contact:

Post 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')
yinka
Forum Newbie
Posts: 12
Joined: Mon Aug 18, 2003 10:15 am

Post 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?
User avatar
JayBird
Admin
Posts: 4524
Joined: Wed Aug 13, 2003 7:02 am
Location: York, UK
Contact:

Post 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
yinka
Forum Newbie
Posts: 12
Joined: Mon Aug 18, 2003 10:15 am

Post 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?
User avatar
JayBird
Admin
Posts: 4524
Joined: Wed Aug 13, 2003 7:02 am
Location: York, UK
Contact:

Post by JayBird »

you need to show me the code that trys to get the $product_id
yinka
Forum Newbie
Posts: 12
Joined: Mon Aug 18, 2003 10:15 am

Post 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'] . ";";
User avatar
JayBird
Admin
Posts: 4524
Joined: Wed Aug 13, 2003 7:02 am
Location: York, UK
Contact:

Post 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
yinka
Forum Newbie
Posts: 12
Joined: Mon Aug 18, 2003 10:15 am

Post 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.
User avatar
JayBird
Admin
Posts: 4524
Joined: Wed Aug 13, 2003 7:02 am
Location: York, UK
Contact:

Post 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
yinka
Forum Newbie
Posts: 12
Joined: Mon Aug 18, 2003 10:15 am

Post by yinka »

Yeah that was my entire code. I thought that I would probably have errors. I am trying your last post right now.
yinka
Forum Newbie
Posts: 12
Joined: Mon Aug 18, 2003 10:15 am

Post 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
User avatar
JayBird
Admin
Posts: 4524
Joined: Wed Aug 13, 2003 7:02 am
Location: York, UK
Contact:

Post 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

}

?>
Post Reply