rseigel Posted July 29, 2015 Share Posted July 29, 2015 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 Quote Link to comment Share on other sites More sharing options...
requinix Posted July 29, 2015 Share Posted July 29, 2015 (edited) 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 Quote Link to comment Share on other sites More sharing options...
rseigel Posted July 29, 2015 Author Share Posted July 29, 2015 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.... Quote Link to comment Share on other sites More sharing options...
mikosiko Posted July 29, 2015 Share Posted July 29, 2015 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 Quote Link to comment Share on other sites More sharing options...
rseigel Posted July 29, 2015 Author Share Posted July 29, 2015 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 />"; Quote Link to comment Share on other sites More sharing options...
Solution rseigel Posted July 29, 2015 Author Solution Share Posted July 29, 2015 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. Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.