Eljay Posted February 23, 2009 Share Posted February 23, 2009 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 More sharing options...
The Little Guy Posted February 23, 2009 Share Posted February 23, 2009 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 More sharing options...
Eljay Posted February 25, 2009 Author Share Posted February 25, 2009 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 More sharing options...
The Little Guy Posted February 25, 2009 Share Posted February 25, 2009 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 More sharing options...
Eljay Posted February 25, 2009 Author Share Posted February 25, 2009 Thanks Little for your response. I tried it and it is still giving me a very high number. I noticed that it echos each entry more than 40 times. When I change the != to =, the script runs quick and it gives a number around 5000 instead. Link to comment https://forums.phpfreaks.com/topic/146598-multiple-tables/#findComment-771386 Share on other sites More sharing options...
premiso Posted February 25, 2009 Share Posted February 25, 2009 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. Link to comment https://forums.phpfreaks.com/topic/146598-multiple-tables/#findComment-771396 Share on other sites More sharing options...
Eljay Posted February 25, 2009 Author Share Posted February 25, 2009 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 More sharing options...
premiso Posted February 25, 2009 Share Posted February 25, 2009 $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 More sharing options...
Eljay Posted February 25, 2009 Author Share Posted February 25, 2009 $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 More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.