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?

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