find minimum value help.

jamesxg1 · November 19, 2009

Hiya peeps,

I have this -

                <?php

			$cnt = array();
			$products = array();
			$keys = array();

			foreach($_POST as $key => $value) {


				$products[] = $key;

				$cnt[$key] = $value;


			}


			$query = "SELECT * FROM `products` WHERE id IN(".join($products,',').")";

			$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);

			if(!mysql_num_rows($result)) {

				echo '<h1>There was an error with your order!</h1>';

			} else {

				echo '<h1><u>You ordered:</u></h1>';

				while($row = mysql_fetch_assoc($result)) {

					echo '<h2>'.$cnt[$row['id']].' x '.$row['name'].'</h2>';

					$total+=$cnt[$row['id']]*$row['price'];
				}

				echo '<h1><u>Results</u></h1>';
				echo '<h2>Cheapest at: ****</h2>';
				echo '<h2>Amount: £'.$total.'</h2>';
			}
			?>

My mysql table structure is

-- phpMyAdmin SQL Dump
-- version 3.2.0.1
-- http://www.phpmyadmin.net
--
-- Host: localhost
-- Generation Time: Nov 19, 2009 at 07:59 PM
-- Server version: 5.1.37
-- PHP Version: 5.3.0

SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";

--
-- Database: `shopping`
--

-- --------------------------------------------------------

--
-- Table structure for table `products`
--

CREATE TABLE IF NOT EXISTS `products` (
  `id` int(6) NOT NULL AUTO_INCREMENT,
  `image` varchar(32) COLLATE utf8_unicode_ci NOT NULL,
  `name` varchar(64) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
  `description` text COLLATE utf8_unicode_ci NOT NULL,
  `price` double NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  UNIQUE KEY `img` (`image`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=7 ;

--
-- Dumping data for table `products`
--

INSERT INTO `products` (`id`, `image`, `name`, `description`, `price`) VALUES
(1, 'iPod.png', 'iPod', 'The original and popular iPod.', 200),
(1, 'iPod.png', 'iPod', 'The original and popular iPod.', 100),
(2, 'iMac.png', 'iMac', 'The iMac computer.', 1200),
(3, 'iPhone.png', 'iPhone', 'This is the new iPhone.', 400),
(4, 'iPod-Shuffle.png', 'iPod Shuffle', 'The new iPod shuffle.', 49),
(5, 'iPod-Nano.png', 'iPod Nano', 'The new iPod Nano.', 99),
(6, 'Apple-TV.png', 'Apple TV', 'The new Apple TV. Buy it now!', 300);

As you can see I have duplicated row id 1 both with diffrent price's (200 & 100) I am trying to make the code above make $row['price'] the lowest value found for that product id, how would I do this I have tried everything min() (php) | MIN(price) (mysql) ect.

Many thanks,

James.

knsito · November 19, 2009

I think its

$row['MIN(price)'];

to access the min-price value

or do something like

SELECT *, min(price) as min_price FROM `products`

and

$row['min_price'];

will have the min-price value

Mchl · November 19, 2009

You're doing it wrong from the start.

Move prices to another table.

jamesxg1 · November 19, 2009

I think its

$row['MIN(price)'];

to access the min-price value

or do something like

SELECT *, min(price) as min_price FROM `products`

and

$row['min_price'];

will have the min-price value

Nice, cheers for that bud.

jamesxg1 · November 19, 2009

This is my mysql dump.

-- phpMyAdmin SQL Dump
-- version 3.2.0.1
-- http://www.phpmyadmin.net
--
-- Host: localhost
-- Generation Time: Nov 19, 2009 at 08:46 PM
-- Server version: 5.1.37
-- PHP Version: 5.3.0

SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";

--
-- Database: `shopping`
--

-- --------------------------------------------------------

--
-- Table structure for table `products`
--

CREATE TABLE IF NOT EXISTS `products` (
  `id` int(6) NOT NULL,
  `image` varchar(32) NOT NULL,
  `name` varchar(64) NOT NULL,
  `description` text NOT NULL,
  `store` int(1) NOT NULL,
  `price` double NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

--
-- Dumping data for table `products`
--

INSERT INTO `products` (`id`, `image`, `name`, `description`, `store`, `price`) VALUES
(1, 'iPod.png', 'iPod', 'The original and popular iPod.', 2, 100),
(2, 'iMac.png', 'iMac', 'The iMac computer.', 2, 1100),
(3, 'iPhone.png', 'iPhone', 'This is the new iPhone.', 2, 300),
(4, 'iPod-Shuffle.png', 'iPod Shuffle', 'The new iPod shuffle.', 2, 39),
(5, 'iPod-Nano.png', 'iPod Nano', 'The new iPod Nano.', 2, 89),
(6, 'Apple-TV.png', 'Apple TV', 'The new Apple TV. Buy it now!', 2, 200),
(1, 'iPod.png', 'iPod', 'The original and popular iPod.', 1, 200),
(2, 'iMac.png', 'iMac', 'The iMac computer.', 1, 1200),
(3, 'iPhone.png', 'iPhone', 'This is the new iPhone.', 1, 400),
(4, 'iPod-Shuffle.png', 'iPod Shuffle', 'The new iPod shuffle.', 1, 49),
(5, 'iPod-Nano.png', 'iPod Nano', 'The new iPod Nano.', 1, 99),
(6, 'Apple-TV.png', 'Apple TV', 'The new Apple TV. Buy it now!', 1, 300),
(1, 'iPod.png', 'iPod', 'The original and popular iPod.', 3, 300),
(2, 'iMac.png', 'iMac', 'The iMac computer.', 3, 1300),
(3, 'iPhone.png', 'iPhone', 'This is the new iPhone.', 3, 500),
(4, 'iPod-Shuffle.png', 'iPod Shuffle', 'The new iPod shuffle.', 3, 59),
(5, 'iPod-Nano.png', 'iPod Nano', 'The new iPod Nano.', 3, 110),
(6, 'Apple-TV.png', 'Apple TV', 'The new Apple TV. Buy it now!', 3, 400);

what i need to do is foreach store where the products exist in get the total price

$total+=$cnt[$row['id']]*$price['price'];

and then find the lowest $total and display it, and i dont have a clue how to do this.

mikesta707 · November 19, 2009

$lowest;
while ($row = ....){
//do whatever
$total = ....;//however you get total
if ($total < $lowest || empty($lowest)){
$lowest = $total;
}
}//endwhile

jamesxg1 · November 19, 2009

  <?php

			$cnt = array();
			$products = array();
			$keys = array();

			foreach($_POST as $key => $value) {


				$products[] = $key;

				$cnt[$key] = $value;


			}

                $query = "SELECT * FROM `products` WHERE id IN(".join($products,',').")";
                
			$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);

                $pquery = "SELECT * FROM `products` WHERE id IN(".join($products,',').")";
                
                $presult = mysql_query($pquery) 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);
                
                
			if(!mysql_num_rows($result)) {

				echo '<h1>There was an error with your order!</h1>';

			} else {

				echo '<h1><u>You ordered:</u></h1>';

				while($row = mysql_fetch_assoc($result)) {

					echo '<h2>'.$cnt[$row['id']].' x '.$row['name'].'</h2>';
                        
                        while($price = mysql_fetch_assoc($presult)) {
                            
                            foreach($price['store'] as $shop) {
                                
                $squery = "SELECT * FROM `products` WHERE id IN(".join($products,',').") AND store = '$shop'";
                
                $sresult = mysql_query($squery) 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);
                
                                        $lowest;
                        while ($calc = mysql_fecth_assoc($sresult)) {
                            
                        $total+=$cnt[$row['id']]*$calc['price'];
                        
                        
                        if ($total < $lowest || empty($lowest)) {
                            
                        $lowest = $total;
                        
                        }
                        }
                        }
                                        }
                            }


				echo '<h1><u>Results</u></h1>';
				echo '<h2>Cheapest at: ****</h2>';
				echo '<h2>Amount: £'.$lowest.'</h2>';
			}
			?>

I get various errors :S

Warning: Invalid argument supplied for foreach() in C:\Program Files\xampp\xampp\htdocs\path\script.php on line 89

jamesxg1 · November 19, 2009

Anyone know how I can do this ?

Many thanks,

James.

Ken2k7 · November 19, 2009

Hello jamesxg1,

You can alter your SQL to use an INTERSECT that selects all products with the lowest price (regardless of ID). Intersection of that query and the one you have should generate what you want. I haven't tested it, but I think it would work.

Ken

Mchl · November 19, 2009

You're doing it wrong from the start.

Move prices to another table.

Then just

SELECT MIN(price) AS minPrice FROM prices WHERE productID = ?

Normalize your data, and things get easier at once.

Sign In

find minimum value help.

Recommended Posts

jamesxg1

Link to comment

Share on other sites

knsito

Link to comment

Share on other sites

Mchl

Link to comment

Share on other sites

jamesxg1

Link to comment

Share on other sites

jamesxg1

Link to comment

Share on other sites

mikesta707

Link to comment

Share on other sites

jamesxg1

Link to comment

Share on other sites

jamesxg1

Link to comment

Share on other sites

Ken2k7

Link to comment

Share on other sites

Mchl

Link to comment

Share on other sites

Join the conversation

Browse

Activity

Important Information