Comparing mySQL Tables

[rseigel]

I have two tables that I want to compare.

 

One is a temporary table that includes the product id, quantity, retail price and wholesale price (product feed from my supplier).

 

The other is a permanent table that includes among other things the same product id.

 

I want to see if an id is in the temp table and not the other one (meaning it's a new product).

 

I also want to see if an id is in the permanent table and not in the temp table (meaning it's a discontinued product).

 

I'd like to be able to output these to the web page so that I can address them.

 

I've searched and I'm getting all kinds of different ideas around the web.

 

Can anyone point me in the right direction? Some sample code, a link, a kick in the right direction - any and all answers are much appreciated.

 

Thanks,

 

Ron

[PFMaBiSmAd]

The most straight-forward query would be to use WHERE column NOT IN (sub-query).

 

To find new products - SELECT * FROM temp_table WHERE id NOT IN (SELECT id from permanent_table)

 

To find discontinued products - SELECT * FROM permanent_table WHERE id NOT IN (SELECT id from temp_table)

[rseigel]

That works perfectly. Thanks a million.

 

Now I just need to figure out how to display it.

[shlumph]

You could also consider using INTERSECT:

 

SELECT id FROM table_1

INTERSECT

SELECT id FROM table_2

[rseigel]

Ok, so this is what I'm trying:

 

$result = mysql_query('SELECT * FROM tmp_price_compare WHERE tmp_price_compare.supplier_reference NOT IN (SELECT product_supplier_reference FROM product_supplier)')
or die(mysql_error());
echo "NEW PRODUCTS<br /><br />";
while($row = mysql_fetch_array($result))
 {
 echo "<a href=https://www.mysupplier.com/item/item.lasso?dsc2=" . $row['supplier_reference'] . echo $row['supplier_reference'] . echo"</a><br />";
 }

 

The SELECT itself works perfectly. I'm just having trouble with the output.

 

I'm sure it's something simple. I'm just not seeing the problem.

 

Anyone?

 

Thanks,

 

Ron

[rseigel]

You could also consider using INTERSECT:

 

SELECT id FROM table_1

INTERSECT

SELECT id FROM table_2

 

Thanks very much for this. I'm going to try it that way as well so I have some more knowledge for the future.

[shlumph]

You only need to echo once, since you're concatenating the string. You also need to quote your href attribute, and put text between the opening and closing of the tag. I believe you meant this:

echo "<a href='https://www.mysupplier.com/item/item.lasso?dsc2=" . $row['supplier_reference'] . "'>" . $row['supplier_reference'] ."</a><br />";

 

Or, simply:

echo "<a href='[url="https://www.mysupplier.com/item/item.lasso?dsc2={$row"]https://www.mysuppli...asso?dsc2={$row[/url]['supplier_reference']}'>{$row['supplier_reference']}</a><br />";

Edited January 29, 2013 by shlumph

[shlumph]

I mean:

echo "<a href='https://www.mysupplier.com/item/item.lasso?dsc2={$row['supplier_reference']}'>{$row['supplier_reference']}</a><br />";

[Jessica]

That question was solved in the OPs other thread.

[rseigel]

Here's the final result for reference.

 

while($row = mysql_fetch_array($result))
 {
 echo "<a href=https://www.bnfusa.com/item/item.lasso?dsc2=";
 echo $row['supplier_reference'];
 echo ">";
 echo $row['supplier_reference'];
 echo"</a><br />";
 }

 

Thanks.

[Jessica]

That is still incorrect, as you were told several times HTML attributes should be quoted.

[rseigel]

Well, it works.

 

I get what you're saying Jessica. I'm just at the point if it works why mess with it.

[Jessica]

Because it's wrong. Just because something works doesn't make it the right way.

[rseigel]

I've tried everything I can think of to make it work your way but I can't get it.

[Jessica]

The answer was literally given to you.

I mean:

echo "<a href='https://www.mysupplier.com/item/item.lasso?dsc2={$row['supplier_reference']}'>{$row['supplier_reference']}</a><br />";

[rseigel]

It sure was.

 

Thanks Jessica and thank you very much shlumph.

 

All is right in the universe again.