Jump to content

Multiple tables


Eljay

Recommended Posts

I want to take columns from tables 1 (xdata) and 2 (wdata) and compare it to table 3 (my_products) to see which items are not present in table 3 and then list them.

 

The columns I am comparing are

table xdata - ITEM

table wdata - productid

table my_products - products_model

 

I tried the code below but it gives me a 30sec timeout message and I think it crashed my site. I have not inserted any wdata columns in the query below. Not sure if my syntax is correct. Thanks for any help.

$sql = "SELECT p.ITEM, pd.products_model FROM xdata p, my_products pd WHERE p.ITEM != pd.products_model";
$result2 = mysql_query($sql, $connection) or trigger_error("SQL", E_USER_ERROR);

while ($r2 = mysql_fetch_array($result2)) {
$y4 = $r2["ITEM"];
echo "$y4 <br>";
}

Link to comment
https://forums.phpfreaks.com/topic/146598-multiple-tables/
Share on other sites

Try setting a time limit:

 

set_time_limit(0);  // Run as long as needed

$sql = "SELECT p.ITEM, pd.products_model FROM xdata p, my_products pd WHERE p.ITEM != pd.products_model";
$result2 = mysql_query($sql, $connection) or trigger_error("SQL", E_USER_ERROR);

while ($r2 = mysql_fetch_array($result2)) {
   $y4 = $r2["ITEM"];
echo "$y4 <br>";
}

Link to comment
https://forums.phpfreaks.com/topic/146598-multiple-tables/#findComment-769621
Share on other sites

I set the limit and the problem with timing out stopped. However the amount of rows that are selected are 31987456!! I only have 6000 in my table. No idea why this is happening

set_time_limit(180);
$sql = "SELECT xdata.ITEM, my_products.products_model FROM xdata ,my_products  WHERE  xdata.ITEM != my_products.products_model";

 

Basically I want to know what is not present in the my_products table that is present in the xdata table.

Link to comment
https://forums.phpfreaks.com/topic/146598-multiple-tables/#findComment-771328
Share on other sites

Try this:

set_time_limit(0);  // Run as long as needed

$sql = "SELECT p.ITEM, m.products_model FROM xdata p LEFT JOIN my_products m ON (p.ITEM != m.products_model)";
$result2 = mysql_query($sql, $connection) or trigger_error("SQL", E_USER_ERROR);

echo '<h2>'.mysql_num_rows($result2).'</h2>';

while ($r2 = mysql_fetch_array($result2)) {
   $y4 = $r2["ITEM"];
echo "$y4 <br>";
}

Link to comment
https://forums.phpfreaks.com/topic/146598-multiple-tables/#findComment-771337
Share on other sites

Are you using mysql 4+ ?

 

$sql = "SELECT item FROM xdata WHERE item NOT IN(SELECT products_model FROM my_products)";

 

See if that helps ya out a bit.

Thanks Premiso, that did the trick. I am using MYSQL 5. If I wanted to add another table (hdata) into the mix, is there a special way to do it? do I have to write 2 separate SELECT statements? the column that is being compared in the new table is productid.

 

 $sql = "SELECT ITEM FROM xdata WHERE ITEM NOT IN(SELECT products_model FROM my_products)";
$sql2 = "SELECT productid FROM hdata WHERE productid NOT IN(SELECT products_model FROM my_products)";

 

Link to comment
https://forums.phpfreaks.com/topic/146598-multiple-tables/#findComment-771413
Share on other sites

$sql = "SELECT x.item, h.productid FROM xdata x, hdata h WHERE x.item NOT IN(SELECT products_model FROM my_products) OR h.productid NOT IN(SELECT products_model FROM my_products)";

 

Dunno if that will work. If it does there is probably a better way to do it. As to how, I do not know.

Link to comment
https://forums.phpfreaks.com/topic/146598-multiple-tables/#findComment-771415
Share on other sites

$sql = "SELECT x.item, h.productid FROM xdata x, hdata h WHERE x.item NOT IN(SELECT products_model FROM my_products) OR h.productid NOT IN(SELECT products_model FROM my_products)";

 

Dunno if that will work. If it does there is probably a better way to do it. As to how, I do not know.

I got it to work with a 2nd query like I have posted above. That should be enough for what I need to do, just was wondering if they both could have been combined into 1 statement. Thanks to all! Cheers!!

Link to comment
https://forums.phpfreaks.com/topic/146598-multiple-tables/#findComment-771421
Share on other sites

Archived

This topic is now archived and is closed to further replies.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.