dpalame

Thanks for your help again. Next question. My query looks like this: SELECT t1.brand,t1.biz_name,t1.biz_info,t1.biz_info,t1.e_address,t1.e_city,t1.e_state,t1.e_postal,t1.e_zip_full,t1.loc_LAT_centroid,t1.loc_LONG_centroid,t1.biz_phone,t1.biz_phone_ext,t1.biz_fax,t1.biz_email,t1.web_url,t1.upc as upc1,t1.retailprice as retailprice1,t1.dollar_sales as dollar_sales1,t1.dollar_sales_ly as dollar_sales_ly1,t1.todaydate as todaydate1,t1.datetimesql as datetimesql1,t1.shelfposition as shelfposition1,t1.reg_sale as reg_sale1,t1.representative as representative1,t1.notes as notes1,t2.upc as upc2,t2.retailprice as retailprice2,t2.dollar_sales as dollar_sales2,t2.dollar_sales_ly as dollar_sales_ly2,t2.todaydate as todaydate2,t2.datetimesql as datetimesql2,t2.shelfposition as shelfposition2,t2.reg_sale as reg_sale2,t2.representative as representative2,t2.notes as notes2 FROM storeCheckRecords AS t1 JOIN storeCheckRecords AS t2 ON t1.e_address = t2.e_address WHERE (t1.upc = '075172071639' AND t2.upc = '075172079468' ) Now say I want to add where a upc that doesn't exist to this query? I tried adding (t1.upc = '075172071639' AND t2.upc = '075172079468' AND t1.upc<>'075172079307' AND t2.upc<>'075172079307') at the end but it is not producing the results. Is there a way to do this? Thanks for your help.

Thanks for the help here is what I finally went with for anybody interested: //$at1 is an array being passed in from a multiple select box - break it apart $remove_these = array ( ',' , '.' ); $at1 = str_replace ( $remove_these , ' ' , $at1 ); $at1 = str_replace ( ' ' , ' ' , $at1 ); // After we've cleaned the string, we break it down into separate words $at1 = explode ( ' ' , $at1 ); // Set Variables number at 0 $numberofvariables=0; // Find out how many variables are in my array and set the first one into $onevariable foreach ($at1 as $val){ $numberofvariables++; $onevariable = $val; } // If there is only one variable do a simple query else build the more complex JOIN and loop for each variable if ($numberofvariables<2){ $query= "SELECT * FROM storeCheckRecords WHERE upc IN ($onevariable)"; }else{ $rank=1; $joinrank=1; $joinrankstart=1; $joins ="storeCheckRecords AS t" . $joinrank++; $upcs = "("; $upcrank=1; $fields = "t1.*,"; foreach ($at1 as $val){ $fields.="t" . $rank . ".upc as upc". $rank++ . ","; $joins.=" JOIN storeCheckRecords AS " . "t" . $joinrank++ . " ON t" . $joinrankstart++ . ".e_address = t" . $joinrankstart . ".e_address "; $upcs.="t" . $upcrank++ . ".upc = '" . $val . "' AND "; } $fields = substr($fields, 0, -1); $joins = substr($joins, 0, -60); $upcs = substr($upcs, 0, -4) . ')'; $query = "SELECT $fields FROM $joins WHERE $upcs"; } Final queries example: 1 variable = SELECT * FROM storeCheckRecords WHERE upc IN (001) multiple variables = SELECT t1.*,t1.upc as upc1,t2.upc as upc2,t3.upc as upc3 FROM storeCheckRecords AS t1 JOIN storeCheckRecords AS t2 ON t1.e_address = t2.e_address JOIN storeCheckRecords AS t3 ON t2.e_address = t3.e_address WHERE (t1.upc = '001' AND t2.upc = '002' AND t3.upc = '003' ) Probably not the best solution, but it works considering mySql table and data structure. Thanks again for all your help!

I don't think I would. I am trying to have one query where I can plug in variables based on 1 or more selections. I tried your IN query and it searches like an OR statement. How can I find results like an AND query. For example: select * from storeCheckRecords where upc IN (001,002,003) - returns results for 001 and 002. I would like to only return results if all three are IN. Thanks for your help.

I apologize I did read it and modified your skeleton code to the following which does work: SELECT * FROM storeCheckRecords AS t1 JOIN storeCheckRecords AS t2 ON t1.e_address = t2.e_address WHERE (t1.upc = '075172079307' AND t2.upc = '075172079659') My question is two fold: a. how do I take it a step further with 3,4,5.....10 variables? b. is there a way to use this same query statement with only one variable? Thanks for your help

I'm sorry I am still confused. I don't know how to loop through this with many variables. If I query: select * from records where upc='001' or upc='002' that works fine. but if I use select * from records where upc='001' AND upc='002' AND upc='003' then I won't get results because each upc is on a different row. How can I loop through the table looking for rows that contain both upcs and still get a result set? The store address is a good common key that would be in each row? Any help is greatly appreciated. Thank you.

I have ripped my hair out trying to solve my php query issue. Hope someone can help. I have a table that looks like this: storeaddressupc Whole Foods1 road001 Whole Foods1 road002 Whole Foods1 road003 Whole Foods1 road004 Now I have no problem when I query like this: SELECT *,GROUP_CONCAT(upc,' --- $',retailprice,' <br/> ') upcs FROM Records WHERE upc LIKE '001' OR upc LIKE '003' GROUP BY LAT,LONG (this returns all the records) Problem is when a want to query AND instead of OR: SELECT *,GROUP_CONCAT(upc,' --- $',retailprice,' <br/> ') upcs FROM Records WHERE upc LIKE '001' AND upc LIKE '003' GROUP BY LAT,LONG Of course this second query doesn't return any results because my table has the upcs in individual rows. Is there a nested query and/or join statement that would put together the upcs in one field and then I could query that one? Thanks for any and all help.

Thank you so much works perfect!

I am trying to figure out how to assign a number rank to a set of results, but I need to reset the ranking when a certain column changes. For example: Brand Rank a 1 a 2 a 3 b 1 b 2 b 3 I set $rank=0; and then for each item in my loop I add $rank++; How do I get the rank to reset in the loop when the brand changes? Thanks for any help.

Thank you that was very helpful. Made me realize I had 0s in the data. Added $percent=@($subtraction/$salesly)*100; for the rows that contain 0. Thank you again for your help.

I am using the following code: $sales=$row[wfdollarsales]; $sales = preg_replace("/[^a-zA-Z0-9\s]/", "", $sales); $sales = intval($sales); $salesly=$row[wfdollarsalesly]; $salesly = preg_replace("/[^a-zA-Z0-9\s]/", "", $salesly); $salesly = intval($salesly); $subtraction=$sales-$salesly; $percent=$subtraction/$salesly; The $subtraction works fine, but $percent keeps returning "Warning: Division by zero in:". Can somebody tell me what I am doing wrong? Thanks

I knew it was an easy fix! Here is the code for anybody trying to accomplish this: header("Content-type: application/msexcel"); header("Content-Disposition: attachment; filename=whole_foods_void_report.xls"); header("Pragma: no-cache"); header("Expires: 0"); foreach ($at1 as $var) { $query = "SELECT wfStoreList.* FROM wfStoreList LEFT JOIN wfData ON wfStoreList.store=wfData.wfstore AND wfData.wfupc LIKE '%$var%' WHERE wfData.wfstore IS NULL ORDER BY region,store ASC "; $result=mysql_query($query); $num=mysql_num_rows($result); $querySpec = "SELECT * FROM wfItemSpecs WHERE upc LIKE '%$var%'"; $resultSpec=mysql_query($querySpec); $rowSpec = mysql_fetch_array($resultSpec); $brand=$rowSpec['brand']; $description=$rowSpec['description']; $pack=$rowSpec['pack']; //Build Result String $display_string = "<div style=\"width:840px;overflow:auto;\"><table class=\"itemList\">"; $display_string .= "<tr>"; $display_string .= "<th>Brand</th>"; $display_string .= "<th>UPC</th>"; $display_string .= "<th>Description</th>"; $display_string .= "<th>Pack</th>"; $display_string .= "<th>Region</th>"; $display_string .= "<th>Store</th>"; $display_string .= "</tr>"; if ($num == 0) echo ""; else for($i = 0; $i<$num; $i++){ ($row = mysql_fetch_array($result)); if($i % 2) { //this means if there is a remainder $bgcolor='#ffffcc'; } else { //if there isn't a remainder we will do the else $bgcolor='#fff'; } $display_string .= "<tr style=\"background-color:$bgcolor;border: 1px solid #999;\">"; $display_string .= "<td style=\"white-space:normal;font-weight:bold;vertical-align:middle;\">$brand</td>"; $display_string .= "<td style=\"white-space:normal;font-weight:bold;vertical-align:middle;\">$var</td>"; $display_string .= "<td style=\"white-space:normal;font-weight:bold;vertical-align:middle;\">$description</td>"; $display_string .= "<td style=\"white-space:normal;font-weight:bold;vertical-align:middle;\">$pack</td>"; $display_string .= "<td style=\"white-space:normal;font-weight:bold;vertical-align:middle;\">$row[region]</td>"; $display_string .= "<td style=\"white-space:normal;font-weight:bold;vertical-align:middle;\">$row[store]</td>"; $display_string .= "</tr>"; } if ($num == 0){ echo ""; }else{ $display_string .= "</table></div>"; } print "$display_string"; }

Has anybody had a chance at this? Any ideas I am still scouring for an answer. Thank you for any help.

I have been ripping my hair out for a while now trying to find out why the following code only outputs the last variable of the array $at1. foreach ($at1 as $var) { $query = "SELECT wfStoreList.* FROM wfStoreList LEFT JOIN wfData ON wfStoreList.store=wfData.wfstore AND wfData.wfupc LIKE '%$var%' WHERE wfData.wfstore IS NULL ORDER BY region,store ASC "; $result=mysql_query($query); $num=mysql_num_rows($result); $querySpec = "SELECT * FROM wfItemSpecs WHERE upc LIKE '%$var%'"; $resultSpec=mysql_query($querySpec); $rowSpec = mysql_fetch_array($resultSpec); $brand=$rowSpec['brand']; $description=$rowSpec['description']; $pack=$rowSpec['pack']; //Build Result String $display_string = "<div style=\"width:840px;overflow:auto;\"><table class=\"itemList\">"; $display_string .= "<tr>"; $display_string .= "<th>Brand</th>"; $display_string .= "<th>UPC</th>"; $display_string .= "<th>Description</th>"; $display_string .= "<th>Pack</th>"; $display_string .= "<th>Region</th>"; $display_string .= "<th>Store</th>"; $display_string .= "</tr>"; if ($num == 0) echo ""; else for($i = 0; $i<$num; $i++){ ($row = mysql_fetch_array($result)); if($i % 2) { //this means if there is a remainder $bgcolor='#ffffcc'; } else { //if there isn't a remainder we will do the else $bgcolor='#fff'; } $display_string .= "<tr style=\"background-color:$bgcolor;border: 1px solid #999;\">"; $display_string .= "<td style=\"white-space:normal;font-weight:bold;vertical-align:middle;\">$brand</td>"; $display_string .= "<td style=\"white-space:normal;font-weight:bold;vertical-align:middle;\">$var</td>"; $display_string .= "<td style=\"white-space:normal;font-weight:bold;vertical-align:middle;\">$description</td>"; $display_string .= "<td style=\"white-space:normal;font-weight:bold;vertical-align:middle;\">$pack</td>"; $display_string .= "<td style=\"white-space:normal;font-weight:bold;vertical-align:middle;\">$row[region]</td>"; $display_string .= "<td style=\"white-space:normal;font-weight:bold;vertical-align:middle;\">$row[store]</td>"; $display_string .= "</tr>"; } if ($num == 0){ echo ""; }else{ $display_string .= "</table></div>"; } } header("Content-type: application/msexcel"); header("Content-Disposition: attachment; filename=whole_foods_void_report.xls"); header("Pragma: no-cache"); header("Expires: 0"); print "$display_string\n$data"; } If I put the headers in the foreach loop then all the data for each variable in the array is output, but in between variable results in tries to reset the headers and prints out header error messages. I know this can't be too complicated, but I am a noob and can't find an answer. Any help would be appreciated. Thanks.

Yes thank you for your help. Here is the code: foreach ($at1 as $var) { $query = "SELECT wfStoreList.* FROM wfStoreList LEFT JOIN wfData ON wfStoreList.store=wfData.wfstore AND wfData.wfupc LIKE '%$var%' WHERE wfData.wfstore IS NULL ORDER BY region,store ASC "; $result=mysql_query($query); $num=mysql_num_rows($result); $querySpec = "SELECT * FROM wfItemSpecs WHERE upc LIKE '%$var%'"; $resultSpec=mysql_query($querySpec); $rowSpec = mysql_fetch_array($resultSpec); $brand=$rowSpec['brand']; $description=$rowSpec['description']; $pack=$rowSpec['pack']; //Build Result String $display_string = "<div style=\"width:840px;overflow:auto;\"><table class=\"itemList\">"; $display_string .= "<tr>"; $display_string .= "<th>Brand</th>"; $display_string .= "<th>UPC</th>"; $display_string .= "<th>Description</th>"; $display_string .= "<th>Pack</th>"; $display_string .= "<th>Region</th>"; $display_string .= "<th>Store</th>"; $display_string .= "</tr>"; if ($num == 0) echo ""; else for($i = 0; $i<$num; $i++){ ($row = mysql_fetch_array($result)); if($i % 2) { //this means if there is a remainder $bgcolor='#ffffcc'; } else { //if there isn't a remainder we will do the else $bgcolor='#fff'; } $display_string .= "<tr style=\"background-color:$bgcolor;border: 1px solid #999;\">"; $display_string .= "<td style=\"white-space:normal;font-weight:bold;vertical-align:middle;\">$brand</td>"; $display_string .= "<td style=\"white-space:normal;font-weight:bold;vertical-align:middle;\">$var</td>"; $display_string .= "<td style=\"white-space:normal;font-weight:bold;vertical-align:middle;\">$description</td>"; $display_string .= "<td style=\"white-space:normal;font-weight:bold;vertical-align:middle;\">$pack</td>"; $display_string .= "<td style=\"white-space:normal;font-weight:bold;vertical-align:middle;\">$row[region]</td>"; $display_string .= "<td style=\"white-space:normal;font-weight:bold;vertical-align:middle;\">$row[store]</td>"; $display_string .= "</tr>"; } if ($num == 0){ echo ""; }else{ $display_string .= "</table></div>"; } } header("Content-type: application/msexcel"); header("Content-Disposition: attachment; filename=whole_foods_void_report.xls"); header("Pragma: no-cache"); header("Expires: 0"); print "$display_string\n$data"; } That only returns the results for the last variable in the array. Any insight? Thanks

If I move the headers to the outside of the loop I only get the last table in the array. How do I circle back? Thanks for your help and where is the modify button for my previous post. Can't find it anywhere.