Multiple tables

[Eljay]

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>";
}

[The Little Guy]

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>";
}

[Eljay]

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.

[The Little Guy]

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>";
}

[Eljay]

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.

[premiso]

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.

[Eljay]

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)";

 

[premiso]

$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.

[Eljay]

$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!!