dpalame Posted August 11, 2011 Share Posted August 11, 2011 Hi, I am looking for the inverse of a select statement based on a variable. This code works fine $at1 = "0007839102188"; echo $at1; $query = "SELECT * FROM wfStoreList LEFT JOIN wfData ON wfStoreList.store=wfData.wfstore WHERE wfData.wfupc LIKE '%$at1%' "; but, I am having trouble figuring out the syntax for the results that are null. This returns all the rows where they match, but I want the rows where they don't match. Second part of this is that $at1 will eventually be an array. How can I run the query for each variable in the array? Any help would be greatly appreciated. Thank you! Quote Link to comment Share on other sites More sharing options...
TeNDoLLA Posted August 11, 2011 Share Posted August 11, 2011 To get inverse results you could use NOT LIKE instead of LIKE. If you just wanna get rid of null values you add to the WHERE part 'AND somefield IS NOT NULL'. You could use implode() to create the query string for the arrayed values or if you need more complicated string you can build the string with foreach(). $vars = array('test1', 'test2', 'test3'); $sql = ''; foreach ($vars as $var) { $sql .= 'some_field LIKE \'%' . $var . '%\' OR '; } $sql = substr($sql, 0, -4); var_dump($sql); Quote Link to comment Share on other sites More sharing options...
dpalame Posted August 11, 2011 Author Share Posted August 11, 2011 Thank you for the response, but I am still having trouble. I think what I am not explaining is that I think I want to compare the results of this query $at1 = "0007839102188"; echo $at1; $query = "SELECT * FROM wfStoreList LEFT JOIN wfData ON wfStoreList.store=wfData.wfstore WHERE wfData.wfupc LIKE '%$at1%' "; to wfStoreList in order to get all the rows of stores where $at1 doesn't exist. If I use NOT LIKE I still get stores I don't want because they are repeated multiple times in the table. Here is a sample of Table - wfData: wfregion wfstore wfcode wfbrand wfupc Florida Plantation 10069 Cafix 75792290741 Florida Plantation 10069 Cafix 7839102188 Florida Plantation 10069 Cafix 7839102211 Florida Plantation 10069 Cool Fruits 65063737400 Florida Plantation 10069 Cool Fruits 65063737401 Florida Plantation 10069 Eddies Pasta 7518100807 Florida Plantation 10069 Eddies Pasta 7518100211 Florida Plantation 10069 Eddies Pasta 7518100809 Florida Plantation 10069 Eddies Pasta 7518100207 Florida Plantation 10069 Familia 7276201222 Florida Plantation 10069 Familia 7276201214 Here is a sample of table - wfStoreList region store Florida Plantation Florida Winter Park Florida Coral Springs Florida Biscayne Florida Ft Lauderdale Florida Boca Raton Florida Palm Beach Gardens Florida Sarasota Florida Coral Gables Florida Naples Florida Bayhill Florida Wellington Florida Jacksonville Florida Pinecrest Florida South Beach Florida Tampa Mid-Atlantic Georgetown Mid-Atlantic Arlington Mid-Atlantic Rockville (Closed) Is there another way to accomplish this? Please let me know if you need any other info and thank you for your help. Quote Link to comment Share on other sites More sharing options...
dpalame Posted August 11, 2011 Author Share Posted August 11, 2011 I thought maybe something like this but it doesn't work. SELECT * FROM wfStoreList WHERE store NOT LIKE (SELECT * FROM wfStoreList LEFT JOIN wfData ON wfStoreList.store=wfData.wfstore WHERE wfData.wfupc LIKE '%$at1%') Thanks again for your help. Quote Link to comment Share on other sites More sharing options...
TeNDoLLA Posted August 11, 2011 Share Posted August 11, 2011 What exactly should be the data out put for your query? Quote Link to comment Share on other sites More sharing options...
dpalame Posted August 11, 2011 Author Share Posted August 11, 2011 The data output should be all the stores from wfStoreList that do not contain the upc (variable). For example, if there are no rows in wfData for the variable then I would like to return the rows from wfStoreList to show that that item is not available in that store. Let me know if you need anything else or more clarity as I am starting to confuse myself. Thanks for all your help. Quote Link to comment Share on other sites More sharing options...
TeNDoLLA Posted August 11, 2011 Share Posted August 11, 2011 This should return the rows from the storeList that do not have UPC like in the variable in the wfData table. SELECT wfStoreList.* FROM wfStoreList LEFT JOIN wfData ON wfStoreList.store=wfData.wfstore WHERE wfData.wfupc NOT LIKE '%$at1%' Quote Link to comment Share on other sites More sharing options...
dpalame Posted August 11, 2011 Author Share Posted August 11, 2011 The problem with that query is that it returns all the rows and there are a lot of duplications of stores in the wfData table. Here is a sample output result: Region Store Florida Plantation Florida Plantation Florida Plantation Florida Plantation Florida Plantation Florida Plantation Florida Plantation Florida Plantation Florida Plantation Florida Plantation Florida Plantation Florida Plantation Florida Plantation Florida Plantation Florida Plantation Florida Plantation Florida Plantation Florida Plantation Florida Plantation Florida Plantation Florida Plantation Florida Plantation Florida Plantation Florida Plantation Florida Plantation Florida Plantation Florida Plantation Florida Plantation Florida Plantation Florida Plantation Florida Plantation Florida Plantation Florida Plantation Florida Plantation Florida Plantation Florida Plantation Florida Plantation Florida Plantation Florida Plantation Florida Plantation Florida Plantation Florida Plantation Florida Plantation Florida Plantation Florida Plantation Florida Plantation Florida Plantation Florida Plantation Florida Plantation Florida Plantation Florida Plantation Florida Plantation Florida Plantation Florida Plantation Florida Plantation Florida Plantation Florida Plantation Florida Plantation Florida Winter Park Florida Winter Park Florida Winter Park Florida Winter Park Florida Winter Park Florida Winter Park Florida Winter Park Florida Winter Park Florida Winter Park Florida Winter Park Florida Winter Park Florida Winter Park Florida Winter Park Florida Winter Park Florida Winter Park Florida Winter Park Florida Winter Park Florida Winter Park Florida Winter Park Florida Winter Park Florida Winter Park Florida Winter Park Florida Winter Park Florida Winter Park Florida Winter Park Florida Winter Park Florida Winter Park Florida Winter Park Florida Winter Park Florida Winter Park Florida Winter Park Florida Winter Park Florida Winter Park Florida Winter Park Florida Winter Park Florida Winter Park Florida Winter Park Florida Winter Park Florida Winter Park Florida Winter Park Florida Winter Park Florida Winter Park Florida Winter Park Florida Coral Springs Florida Coral Springs Florida Coral Springs Florida Coral Springs Florida Coral Springs Florida Coral Springs Florida Coral Springs Florida Coral Springs Florida Coral Springs Florida Coral Springs Florida Coral Springs Florida Coral Springs Florida Coral Springs Florida Coral Springs Florida Coral Springs Florida Coral Springs Florida Coral Springs Florida Coral Springs Florida Coral Springs Florida Coral Springs Florida Coral Springs Florida Coral Springs Florida Coral Springs Florida Coral Springs Florida Coral Springs Florida Coral Springs Florida Coral Springs Florida Coral Springs I think I need to somehow take the result from the join query and then compare that to the wfStoreList table again. Here is an output sample from the join with LIKE $at1: Florida Plantation Florida Coral Springs Florida Biscayne Florida Ft Lauderdale Florida Boca Raton Florida Coral Gables Florida Naples Florida Bayhill Florida Pinecrest Mid-Atlantic Arlington Mid-Atlantic Bethesda Mid-Atlantic Tysons That is good, but it is all the stores that $at1 is in. I need the stores that $at1 is not in. Is there a way to take this query result and compare it to the wfStoreList table again? Thanks Quote Link to comment Share on other sites More sharing options...
TeNDoLLA Posted August 11, 2011 Share Posted August 11, 2011 How about if you do SELECT DISTINCT instead of SELECT in the query I gave you above? Shoud give you result like this: Florida Plantation Florida Winter Park Florida Coral Springs this what you want? Quote Link to comment Share on other sites More sharing options...
dpalame Posted August 11, 2011 Author Share Posted August 11, 2011 That does return single values, but it is still returning all stores because it is selecting all rows that don't have $at1 in them and a lot of those rows have store names that $at1 is not in. Here is the output based on DISTINCT for $at1 = "0007839102188";: Florida Plantation Florida Winter Park Florida Coral Springs Florida Biscayne Florida Ft Lauderdale Florida Boca Raton Florida Palm Beach Gardens Florida Sarasota Florida Coral Gables Florida Naples Florida Bayhill Florida Wellington Florida Jacksonville Florida Pinecrest Florida South Beach Florida Tampa Mid-Atlantic Georgetown Mid-Atlantic Arlington Mid-Atlantic Rockville (Closed) Mid-Atlantic Bethesda Mid-Atlantic Tysons Problem is that $at1 = "0007839102188"; is not in some of these stores (example Florida Jacksonville). It is pulling other items that don't have $at1 = "0007839102188" as there upc but are in Florida Jacksonville. Is there a way to compare the results of this query against the wfStoreList to get the opposite stores? Thanks. Quote Link to comment Share on other sites More sharing options...
dpalame Posted August 12, 2011 Author Share Posted August 12, 2011 Can anybody help with this? Thank you for your assistance. Quote Link to comment Share on other sites More sharing options...
DavidAM Posted August 12, 2011 Share Posted August 12, 2011 I have found this to work by creating a LEFT JOIN that includes the Value we DO NOT want and then filtering for the rows where the RIGHT table's ID is NULL SELECT wfStoreList.* FROM wfStoreList LEFT JOIN wfData ON wfStoreList.store=wfData.wfstore AND wfData.wfupc LIKE '%$at1%' WHERE wfData.wfstore IS NULL The idea is that all rows from the LEFT table (wfStoreList) are returned and are matched to the rows from the RIGHT table (wfData) where the wfUPC is the value we are looking for. When a row does NOT exist in the RIGHT table, all fields from the RIGHT table will be NULL, so we use the IS NULL in the WHERE clause to get the rows that have NO match. Quote Link to comment Share on other sites More sharing options...
dpalame Posted August 12, 2011 Author Share Posted August 12, 2011 Thank you so much that worked perfectly for one variable. If you could help me with the next step that would be great. $at1 will actually be an array of multiple upc values. I assume I would need a foreach statement to accomplish this. I would like the output to look like this $at1 | Region | Store $at1 | Region | Store $at1 | Region | Store Where each $at1 upc would be listed in order. I assume I can use something like ORDER BY $at1,region,store ASC. Any direction you could provide would be great! Thank you for your help. I tried this, but it didn't work: foreach ($at1 as $var) { $sql .= 'wfData.wfupc LIKE \'%' . $var . '%\' OR '; } $sql = substr($sql, 0, -4); var_dump($sql); $query = "SELECT wfStoreList.* FROM wfStoreList LEFT JOIN wfData ON wfStoreList.store=wfData.wfstore AND $sql WHERE wfData.wfstore IS NULL ORDER BY region,store ASC "; Quote Link to comment Share on other sites More sharing options...
dpalame Posted August 12, 2011 Author Share Posted August 12, 2011 Sorry missed the single quote around $sql - This code works but for all values in the array at once. foreach ($at1 as $var) { $sql .= 'wfData.wfupc LIKE \'%' . $var . '%\' OR '; } $sql = substr($sql, 0, -4); var_dump($sql); $query = "SELECT wfStoreList.* FROM wfStoreList LEFT JOIN wfData ON wfStoreList.store=wfData.wfstore AND '$sql' WHERE wfData.wfstore IS NULL ORDER BY region,store ASC "; How can I separate them all and list? Thanks 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.