array & querys help/advice.

jamesxg1 · 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.

papaface · 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...

jamesxg1 · 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>';
			}
			?>

jamesxg1 · November 20, 2009

Anyone know how to do this ?

Many thanks,

James.

jamesxg1 · November 21, 2009

Anyone have any ideas, it would be appreciated.

Many thanks

James.

GingerRobot · 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?

jamesxg1 · 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.

jamesxg1 · November 21, 2009

Anyone ?

Cheers

James.

jamesxg1 · 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.

jamesxg1 · November 21, 2009

Anyone ?. Please this is very needed.

Many thanks

James.

jamesxg1 · November 21, 2009

Bump.

jamesxg1 · November 21, 2009

Bump.

jamesxg1 · November 25, 2009

Bump.

Sign In

array & querys help/advice.

Recommended Posts

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Join the conversation

Important Information