SELECT Help

[rseigel]

Hi all,

 

I'm sure this should be simple but for some reason my brain won't cooperate.

 

I have 2 tables:

 

ps_products and tmp_BF

 

Both of them have a field called supplier_reference.

 

I need to know which records are in ps_products that aren't in tmp_BF and vice-versa.

 

Could someone please kick me in the right direction?

 

Thanks,

 

Ron

[requinix]

As two separate queries? Start with ps_products, LEFT JOIN (so it's optional) the tmp_BF table on that field, then filter the results to WHERE tmp_BF's supplier_reference IS NULL. A matching row "can't" possibly have that value as NULL so it'll only return the results without a match. Reverse the process for the other direction.

 

Or are the tables the same and you'd like one query to show which rows from which tables are missing?

Edited July 29, 2015 by requinix

[rseigel]

Thanks very much for the help....

 

I actually have the SELECT working:

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

The problem now is that it outputs nothing.

 

If I do the raw SELECT on the database there is a result.

 

Now I"m really confused....

[mikosiko]

In that code you have 2 issues:

1.- You can not mix mysql_ and mysqli_ APIs

2.- mysqli_query() (and in general all the "mysqli_" procedural methods/functions) need to have the database link as a parameter.... read http://php.net/manual/en/mysqli.query.php

 

 

here are some usage's examples http://php.net/manual/en/mysqli-result.fetch-array.php

[rseigel]

Fixed #1.

 

As far as #2, I'm not sure what you mean.

 

I have the connection at the top of my script.

$con = mysqli_connect("localhost","xxxxxx","xxxxx","xxxxx");

// Check connection
if (mysqli_connect_errno())
  {
  echo "Failed to connect to MySQL: " . mysqli_connect_error();
  }
echo "Connected to Database<br /><br />";

[rseigel]

I'm a moron....

 

This works perfectly:

 

$result = mysqli_query($con,'SELECT * FROM tmp_BF WHERE tmp_BF.supplier_reference NOT IN (SELECT supplier_reference FROM ps_product)')

    or die(mysqli_error());

 

Thanks for the kick in the right direction.