very hard to figure out. please help.

[jamesxg1]

Hiya guys,

 

Ok this might take a bit of explaining to do so what im going to do is give you a example,

 

SQL DUMP.

-- phpMyAdmin SQL Dump
-- version 3.2.0.1
-- http://www.phpmyadmin.net
--
-- Host: localhost
-- Generation Time: Nov 20, 2009 at 12:36 AM
-- 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,
  `storename` varchar(32) NOT NULL,
  `price` double NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

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

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

 

ok, say i sent over product id's 4,6,1 i then need to get a list of all the stores in this case it will be 1,2,3 so far then query the database with a WHERE store = 'STORE ID' and i need a WHERE id = 'ID1, ID2, ID3' then i need to add all the prices together for that queryed store then do it for the next store then the next and so on then with the smalled value left over after print it.

 

How would i do this,

 

Many thanks,

 

James.

[jamesxg1]

Anyone got any ideas ?, i need this more than my pc need a power supply guys. lol.

[trq]

Anyone got any ideas ?

 

Its usually better to place your post in the correct board. This is PHP help, your question is Mysql help.

 

Ive reported the thread, it should get moved soon.

[jamesxg1]

Anyone got any ideas ?

 

Its usually better to place your post in the correct board. This is PHP help, your question is Mysql help.

 

Ive reported the thread, it should get moved soon.

 

:S ermmm ok. and ok thanks i guess :S.

[fenway]

I don't follow.

[jamesxg1]

Hiya,

 

Ok i'll see if i can explain in more detail.

 

Ok say i sent PRODUCT ID 4 & 5 over to my process page i then need to make the process page query my database and get a list of all the STORE ID's and put them in an array one i have my PRODUCT ID's & STORE ID's i then need to do these querys ...

 

STORE ID 1 => PRODUCT ID 4 => £10 +

STORE ID 1 => PRODUCT ID 5 => £15 = £25

 

STORE ID 2 => PRODUCT ID 4 => £100 +

STORE ID 2 => PRODUCT ID 5 => £150 = £250

 

STORE ID 3 => PRODUCT ID 4 => £1000 +

STORE ID 3 => PRODUCT ID 5 => £1500 = £2500

 

    ^                      ^                        ^

per store id.      per product id.      the price in mysql.

 

so then the query should return "£25, STORE ID 1".

 

Many thanks

 

James.

[Maq]

Ok say i sent PRODUCT ID 4 & 5 over to my process page i then need to make the process page query my database and get a list of all the STORE ID's and put them in an array one i have my PRODUCT ID's & STORE ID's i then need to do these querys ...

 

I don't see any reason this can't be done directly in your query.  You would have to join the 'products' and 'store' tables, and use SUM to total the prices.

[jamesxg1]

Ok say i sent PRODUCT ID 4 & 5 over to my process page i then need to make the process page query my database and get a list of all the STORE ID's and put them in an array one i have my PRODUCT ID's & STORE ID's i then need to do these querys ...

 

I don't see any reason this can't be done directly in your query.  You would have to join the 'products' and 'store' tables, and use SUM to total the prices.

 

Ok, how would i go about do that?. And how do i join them? in PHPMYADMIN?,

 

Cheers mate

 

James.

[Maq]

Ok say i sent PRODUCT ID 4 & 5 over to my process page i then need to make the process page query my database and get a list of all the STORE ID's and put them in an array one i have my PRODUCT ID's & STORE ID's i then need to do these querys ...

 

I don't see any reason this can't be done directly in your query.  You would have to join the 'products' and 'store' tables, and use SUM to total the prices.

 

Ok, how would i go about do that?. And how do i join them? in PHPMYADMIN?,

 

Cheers mate

 

James.

 

No, in your query.  Something like this:

 

SELECT SUM(p.price) AS total, p.id AS pid, [s.id AS sid] FROM products p LEFT JOIN store s ON p.id = [s.product_id] WHERE p.id IN (4, 5);

 

(NOTE: Columns with brackets [] surrounding them, are guesses from my part.  This has also not been tested.)

[jamesxg1]

Hiya,

 

Cheers for that bud. one problem lol. How do i use it ? :S. Lol. I query it and then fetch_assoc ?.

 

Many thanks,

 

James.

[Maq]

Hiya,

 

Cheers for that bud. one problem lol. How do i use it ? :S. Lol. I query it and then fetch_assoc ?.

 

Many thanks,

 

James.

 

Yes, just like any other query.  That will put the information into an associative array for you.

[jamesxg1]

Hiya,

 

Cheers bud - I queryed it and it had some errors so i tried to write one myself is this ok ?

 

$query = "SELECT SUM(price) AS total, `id`, `name`, `store` FROM `products` LEFT JOIN `stores` ON store = store WHERE id IN(".join($products,',').")";

 

It has errors and i dont understand them.

 

Column 'store' in field list is ambiguous in C:\Program Files\xampp\xampp\htdocs\shopping\order.php on line 71.

 

Many thanks

 

James.

[Maq]

A few problems I see are:

  You should alias your table names (ie products p) so you can use 'p' to reference columns.

  The reason for the error is because I assume 'store' is present in both tables and you have to specify a table.

  The only time you need backticks is to escape a table or column name due to it being a MySQL keyword.

 

Try something like this:

 

$query = "SELECT SUM(price) AS total, p.id, p.name, p.store FROM products p LEFT JOIN stores s ON p.store = s.store WHERE p.id IN(".join($products,',').")";

 

[jamesxg1]

Hiya mate,

 

Cheers for that query had no error's at all.

 

but i have a problem with the total price.

 

The store id for the lowest price is working perfectly but in the database the product i selected is £39 but its returning £445 :S

 

Cheers bud.

 

James.

 

 

                if(!isset($_POST) OR 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>';                
                } else {
            
            $cnt = array();
            $products = array();
            
            foreach($_POST as $key => $value) {
  
               $products[] = $key;
               $cnt[$key] = $value;  
                  
            }
            
                $count = count($products);
                $query = "SELECT SUM(price) AS total, p.id, p.name, p.store FROM products p LEFT JOIN stores s ON p.store = s.store WHERE p.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 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_object($result)) {

                  echo '<h2>'.$cnt[$row->id].' x '.$row->name.'</h2>';
                  
                            $total += $row->total;
                            $store =  $row->store;
                            
                            }

               
               echo '<h1><u>Results</u></h1>';
               echo '<h2>Cheapest at:' . $store . '</h2>';
               echo '<h2>Amount: £'.$total.'</h2>';
                                                            
                                                           if($total > 50) {             
                                           
                            echo '<a href="" onclick="" class="buttonn">Deliver it!</a>';
                                
                            } elseif ($total < 50) {
                                
                             echo '<a href="" onclick="" class="buttonn">Deliver it for £9.99!</a>';    
                                
                            }
            }
                }

 

 

[jamesxg1]

Also. Sorry to re-post but it is also only letting me have one product id. And it seems to be picking to the one with the lowest value :S.

 

Cheers bud.

 

James.

[Maq]

The store id for the lowest price is working perfectly but in the database the product i selected is £39 but its returning £445 :S

 

Add

 

GROUP BY p.id

 

to the end of your query.  It will total prices for each product rather than all of them.

[jamesxg1]

Hiya mate,

 

Not really much diffrence, i was wondering if that is querying the database with the product id PER STORE ID?

 

Cheers bud.

 

James.