Query two tables on same page depending on ID

yinka

Ok I'm confused once agian. I have one table that I am querying products by their ID. Now, I decided to bring ordering information from other tables depending on which ID the product is. Is this possible. Do I need join the tables, or can I just query the other table with an "if-then" statement. For example,

If ID=1 echo table "blah blah"

Here is the code from the first table. Thanks for your help.

    <?php

$query = "select * from products where id = " . $_GET['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))
{

Stoned_Gecko

I don't see why you wouldn't be able to. You query the products table and you get your id, so then you can put it an if-else:

if (id=1) {
  $query = "SELECT * FROM table1 WHERE id='$id'";
} else {
  $query = "SELECT * FROM tables WHERE product_id = '$id'";
}

yinka

Sorry I haven't responded earlier. I had a little blackout problem.

So would I query it like this?

<?php

$query = "select * from products where id = " . $_GET['id'] . ";";
$result = mysql_query($query) or die(mysql_error());

if (id=1) {
$query = "SELECT FROM table1 WHERE id='$id'";
} else {
$query = "SELECT FROM tables WHERE product_id = '$id'";
}

Would I have to name every other table that I want to pull data from. I have 10 products in a table. Then I have 10 tables which I want to relate to each individual product table. Sorry for being so stupid. I'm really trying to get a handle of this. Thanks for your help.

Stoned_Gecko

You could do it that way. You can use the switch statement. There are a couple of ways of doing this and none of them is wrong. I would put the table names and product ids in an array and make a query based on that. So you have:

$product_array = array(
  1 => "table1",
  2 => "tables",
  3 => "chairs");

//Some code here that gets the $product_id

//Then make the query
$query = "SELECT * FROM " . $product_array[$product_id] . " WHERE product_id = '$product_id]";

yinka

Thanks for helping me out Stoned Gecko. I set it up like this but I get 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 'WHERE product_id = ']' at line 1 1

I'm looking through the manual now, but have not found the answer yet.

    <?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());

Stoned_Gecko

Double check the query. There is an error at the end.

yinka

Yeah, I see the syntax error. I changed it to this:
$query = "SELECT * FROM " . $product_array[$product_id] . " WHERE product_id = '$product_id'";

...but I am still getting a error.

Stoned_Gecko

What's the error you're getting?

yinka

I'm gettting this:

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 have been trying to figure this stuff out. Could it be that I have to join these tables somehow?

Stoned_Gecko

Doubt it. Have your script echo the query. That way you can see exactly what is being sent to the SQL server.

yinka

OK, here is the code that I have now, and following is the message that I received. Is that what you meant for me to do?

<?php

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

//Get the $product_id

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

//Make the query
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 {
exho "Product ID is not set.";
}

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

          <?php

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

Product ID is not set.

Warning: mysql_fetch_object(): supplied argument is not a valid MySQL result resource in template5.php on line 79

Stoned_Gecko

That's your whole code? Where is the code that actually gets the product ID? I see the query, but I don't see the code that does anything with it.

yinka

I thought this was getting the ID based on URL that I put in.

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

And then I have a table below that looks like this.

 <td valign="top">
		<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>.

I didn't inlude it to save some space. Obviously I'm getting lost somewhere. I don't mean to waste your time.

Stoned_Gecko

Uh huh. You have the query, but you're not running it. The line

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

Only sets the $query variable. It doesn't actually run the query. You need to run the query and get the result before that will work.

yinka

So you mean like this? If not, how do I get the result?

//Get the $product_id

$query = "SELECT * FROM products WHERE id = " . $_GET['id'] . ";";
$result = mysql_query($query) or die(mysql_error());

Stoned_Gecko

http://php.net/mysql

That should give you enough info to put in everything that's missing.