jamesxg1 Posted November 20, 2009 Share Posted November 20, 2009 Hiya peeps, Ok so here goes , I have a mysql database and basically here's what I need to do. // foreach store id // foreach product id { // query with where store id and product id // get price row for each products, add them all together // close foreach store & product id {{ // find the lowest price along with what store id has the lowest price and echo it. How would I go about doing this? I have tried so many ways but i just cant seem to do it. Much appreciated, Many thanks, James. Link to comment https://forums.phpfreaks.com/topic/182339-array-querys-helpadvice/ Share on other sites More sharing options...
papaface Posted November 20, 2009 Share Posted November 20, 2009 What code do you have so far? We're not here to write entire scripts for you, at least help us by doing some work... Link to comment https://forums.phpfreaks.com/topic/182339-array-querys-helpadvice/#findComment-962232 Share on other sites More sharing options...
jamesxg1 Posted November 20, 2009 Author Share Posted November 20, 2009 Here's what iv TRIED to do so far, it dont work thoe. Many thanks, James. <?php if(empty($_POST)) { echo '<h1 align="center">There was an error with your order!</h1><h2 align="center"><a href="index.php">Start again</a></h2>'; exit; } $cnt = array(); $products = array(); foreach($_POST as $key => $value) { $products[] = $key; $cnt[$key] = $value; } $pcount = count($products); $getstores = "SELECT `store` FROM `products`"; $rungetstores = mysql_query($getstores) or trigger_error('<font color="red" size="6"><b>Site Error:</b><br />Could not query the selected database.<br /></font>' . mysql_error(), E_USER_ERROR); $stores = array(); while($row = mysql_fetch_array($rungetstores)) { $stores[] = $row['store']; } foreach($stores as $shop) { $query = "SELECT `id`, `name`, `price` FROM `products` WHERE id IN(".join($products,',').") AND store = '$shop' LIMIT $pcount"; $result = mysql_query($query) or trigger_error('<font color="red" size="6"><b>Site Error:</b><br />Could not query the selected database.<br /></font>' . mysql_error(), E_USER_ERROR); while($price = mysql_fetch_array($result)) { $total+=$cnt[$row['id']]*$price['price']; } } // find what the lowest total is and what store id its from. if(!mysql_num_rows($result)) { echo '<h1 align="center">There was an error with your order!</h1><h2 align="center"><a href="index.php">Start again</a></h2>'; } else { echo '<h1><u>You ordered:</u></h1>'; while($row = mysql_fetch_assoc($result)) { echo '<h2>'.$cnt[$row['id']].' x '.$row['name'].'</h2>'; } echo '<h1><u>Results</u></h1>'; echo '<h2>Cheapest at: ****</h2>'; echo '<h2>Amount: £'.$total.'</h2>'; } ?> Link to comment https://forums.phpfreaks.com/topic/182339-array-querys-helpadvice/#findComment-962240 Share on other sites More sharing options...
jamesxg1 Posted November 20, 2009 Author Share Posted November 20, 2009 Anyone know how to do this ? Many thanks, James. Link to comment https://forums.phpfreaks.com/topic/182339-array-querys-helpadvice/#findComment-962249 Share on other sites More sharing options...
jamesxg1 Posted November 21, 2009 Author Share Posted November 21, 2009 Anyone have any ideas, it would be appreciated. Many thanks James. Link to comment https://forums.phpfreaks.com/topic/182339-array-querys-helpadvice/#findComment-962455 Share on other sites More sharing options...
GingerRobot Posted November 21, 2009 Share Posted November 21, 2009 Can you define "doesn't work?" What happens? Do you get any errors? What output does it produce? What output are you expecting? Link to comment https://forums.phpfreaks.com/topic/182339-array-querys-helpadvice/#findComment-962459 Share on other sites More sharing options...
jamesxg1 Posted November 21, 2009 Author Share Posted November 21, 2009 Can you define "doesn't work?" What happens? Do you get any errors? What output does it produce? What output are you expecting? Code change. <?php if(empty($_POST)) { echo '<h1 align="center">There was an error with your order!</h1><h2 align="center"><a href="index.php">Start again</a></h2>'; exit; } $cnt = array(); $products = array(); foreach($_POST as $key => $value) { $key = (int)str_replace('_cnt','',$key); $products[] = $key; $cnt[$key] = $value; } $pcount = count($products); $getstores = "SELECT `store` FROM `stores`"; $rungetstores = mysql_query($getstores) or trigger_error('<font color="red" size="6"><b>Site Error:</b><br />Could not query the selected database.<br /></font>' . mysql_error(), E_USER_ERROR); $stores = array(); while($row = mysql_fetch_array($rungetstores)) { $stores[] = $row['store']; } foreach($stores as $shop) { foreach($products as $item) { $query = "SELECT * FROM `products` WHERE id = '$item' AND store = '$shop' LIMIT $pcount"; $result = mysql_query($query) or trigger_error('<font color="red" size="6"><b>Site Error:</b><br />Could not query the selected database.<br /></font>' . mysql_error(), E_USER_ERROR); while($price = mysql_fetch_array($result)) { $total+=$cnt[$price['id']]*$price['price']; } } } $query = "SELECT `id`, `name`, `price` FROM `products` WHERE id IN(".join($products,',').") LIMIT $pcount"; $result = mysql_query($query) or trigger_error('<font color="red" size="6"><b>Site Error:</b><br />Could not query the selected database.<br /></font>' . mysql_error(), E_USER_ERROR); // find what the lowest total is and what store id its from. if(!mysql_num_rows($result)) { echo '<h1 align="center">There was an error with your order!</h1><h2 align="center"><a href="index.php">Start again</a></h2>'; } else { echo '<h1><u>You ordered:</u></h1>'; while($row = mysql_fetch_assoc($result)) { echo '<h2>'.$cnt[$row['id']].' x '.$row['name'].'</h2>'; } echo '<h1><u>Results</u></h1>'; echo '<h2>Cheapest at: ****</h2>'; echo '<h2>Amount: £'.$total.'</h2>'; } ?> This is what it does. Can you define "doesn't work?" What happens? It gererates a total price, but its adding the price from all of the store id's i need it to add per store. Do you get any errors? Nope. What output does it produce? A total price generated from adding the prices of the items from every store id. What output are you expecting? A uniqe price per store id. Many many thanks James. Link to comment https://forums.phpfreaks.com/topic/182339-array-querys-helpadvice/#findComment-962468 Share on other sites More sharing options...
jamesxg1 Posted November 21, 2009 Author Share Posted November 21, 2009 Anyone ? Cheers James. Link to comment https://forums.phpfreaks.com/topic/182339-array-querys-helpadvice/#findComment-962488 Share on other sites More sharing options...
jamesxg1 Posted November 21, 2009 Author Share Posted November 21, 2009 Code changed. <?php if(empty($_POST)) { echo '<h1 align="center">There was an error with your order!</h1><h2 align="center"><a href="index.php">Start again</a></h2>'; exit; } $cnt = array(); $products = array(); foreach($_POST as $key => $value) { $key = (int)str_replace('_cnt','',$key); $products[] = $key; $cnt[$key] = $value; } $pcount = count($products); $getstores = "SELECT `store` FROM `stores` ORDER BY `store` ASC"; $rungetstores = mysql_query($getstores) or trigger_error('<font color="red" size="6"><b>Site Error:</b><br />Could not query the selected database.<br /></font>' . mysql_error(), E_USER_ERROR); $stores = array(); $prices = array(); while($row = mysql_fetch_array($rungetstores)) { $stores[] = $row['store']; } foreach($stores as $shop) { foreach($products as $item) { $query = "SELECT * FROM `products` WHERE id = '$item' AND store = '$shop' LIMIT $pcount"; $result = mysql_query($query) or trigger_error('<font color="red" size="6"><b>Site Error:</b><br />Could not query the selected database.<br /></font>' . mysql_error(), E_USER_ERROR); while($price = mysql_fetch_array($result)) { $prices[$shop] = $price['price']; print_r($prices); } } } $query = "SELECT `id`, `name`, `price` FROM `products` WHERE id IN(".join($products,',').") LIMIT $pcount"; $result = mysql_query($query) or trigger_error('<font color="red" size="6"><b>Site Error:</b><br />Could not query the selected database.<br /></font>' . mysql_error(), E_USER_ERROR); // find what the lowest total is and what store id its from. if(!mysql_num_rows($result)) { echo '<h1 align="center">There was an error with your order!</h1><h2 align="center"><a href="index.php">Start again</a></h2>'; } else { echo '<h1><u>You ordered:</u></h1>'; while($row = mysql_fetch_assoc($result)) { echo '<h2>'.$cnt[$row['id']].' x '.$row['name'].'</h2>'; } echo '<h1><u>Results</u></h1>'; echo '<h2>Cheapest at: ****</h2>'; echo '<h2>Amount: £'.$total.'</h2>'; } ?> It outputs this, Array ( [1] => 49 ) Array ( [1] => 49 [2] => 39 ) Array ( [1] => 49 [2] => 39 [3] => 59 ) the prices and store id's are 1 = 49£ 2 = 39£ 3 = 59£ i'm really stuck now i have no idea what to do next to get the total price from all the product id's for each store and display only the lowest. Many thanks, James. Link to comment https://forums.phpfreaks.com/topic/182339-array-querys-helpadvice/#findComment-962497 Share on other sites More sharing options...
jamesxg1 Posted November 21, 2009 Author Share Posted November 21, 2009 Anyone ?. Please this is very needed. Many thanks James. Link to comment https://forums.phpfreaks.com/topic/182339-array-querys-helpadvice/#findComment-962523 Share on other sites More sharing options...
jamesxg1 Posted November 21, 2009 Author Share Posted November 21, 2009 Bump. Link to comment https://forums.phpfreaks.com/topic/182339-array-querys-helpadvice/#findComment-962543 Share on other sites More sharing options...
jamesxg1 Posted November 21, 2009 Author Share Posted November 21, 2009 Bump. Link to comment https://forums.phpfreaks.com/topic/182339-array-querys-helpadvice/#findComment-962563 Share on other sites More sharing options...
jamesxg1 Posted November 25, 2009 Author Share Posted November 25, 2009 Bump. Link to comment https://forums.phpfreaks.com/topic/182339-array-querys-helpadvice/#findComment-965509 Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.