CODEWITHSUNDEEP
phpmysqlsum
Greyhamne
Greyhamne

Reputation: 53

cant get grand total of a shopping cart PHP/MySQL

Im trying to grab the grand total of my shopping cart but i just cant seem to get it to work and im wondering if its the way i have approched my code, I have used a foreach loop to grab the id from a session array and then loop through the results however when i try to do a SUM query the answer just outputs the individual prices as an array as apposed to adding them together. Would it be better to store the price in a 2d array ?

if(!isset($_SESSION['cart'])){//
$_SESSION['cart']=array();//creating session array to store items id
}
    <?php
                        echo'<table class="table">';
                            echo'<tr>';
                            echo'<th>Item</th>';
                            echo'<th>Code</th>';
                            echo'<th>Description</th>';
                            echo'<th>Cost(GBP)</th>';
                            echo'<th>Remove</th>';
                            echo'</tr>';

                            foreach ($_SESSION['cart'] as $value){
                                $z = mysql_query('SELECT * FROM product_item where id="'.$value.'"');
                                while($row = mysql_fetch_array($z)){
                                    echo'<tr>';
                                    echo'<td><img src="images/'.$row['path'].'.jpg" alt="'.$row['alt'].'" width="65" height="65"/></td>';
                                    echo'<td>'.$row['code'].'</td>';
                                    echo'<td>'.$row['description'].'</td>';
                                    echo'<td><p>&pound;'.$row['price'].'</p></td>';
                                    echo'<td><p><a title="remove from shopping cart" href="cart.php?remove='.$value.'">X</a></p></td>';
                                    echo'</tr>';                                
                                }


                                // this is where i want to get total cost
                                $y = mysql_query('SELECT SUM(price) as total_cost FROM product_item where id="'.$value.'"');
                                while($row = mysql_fetch_array($y)){
                                    echo $row['total_cost'];
                                }

                            }

                            echo'<tr>';
                            echo'<td></td>';
                            echo'<td></td>';
                            echo'<td></td>';
                            echo'<td><p>Total</p></td>';
                            echo'<td></td>';
                            echo'</tr>';
                            echo'</table>';
                        }
                    ?>
                    ?>

Upvotes: 0

Views: 3004

Answers (3)

Feysal
Feysal

Reputation: 623

You could just calculate the sum in PHP as you iterate through the items in the cart. If you really want to do it in SQL, you have to do it outside the foreach loop, since that only gives you the price of one item at a time.

Using implode() you can concatenate all item ids in cart into a single list to use in your query:

$y = mysql_query("SELECT SUM(price) AS total_cost FROM product_item WHERE id IN (" . implode(", ", $_SESSION['cart']) . ")";

Upvotes: 0

Michael Laffargue
Michael Laffargue

Reputation: 10314

'SELECT SUM(price) as total_cost FROM product_item where id="'.$value.'"'

If your id is unique, it'll always bring the 'price' value. Moreover your sum is in the foreach. If you want the total price I suggest that you keep price value in php variable and sum up in the foreach, then you just have to display this variable at the end.

And using "select *" is not recommanded, you should put the field you want to retrieve.

Upvotes: 0

Shadlan
Shadlan

Reputation: 254

Why not an external variable?

if(!isset($_SESSION['cart'])){//
$_SESSION['cart']=array();//creating session array to store items id

$cartTotal = 0; //This is where you store the total
}
    <?php
                        echo'<table class="table">';
                            echo'<tr>';
                            echo'<th>Item</th>';
                            echo'<th>Code</th>';
                            echo'<th>Description</th>';
                            echo'<th>Cost(GBP)</th>';
                            echo'<th>Remove</th>';
                            echo'</tr>';

                            foreach ($_SESSION['cart'] as $value){
                                $z = mysql_query('SELECT * FROM product_item where id="'.$value.'"');
                                while($row = mysql_fetch_array($z)){
                                    $cartTotal += $row['price'];
                                    echo'<tr>';
                                    echo'<td><img src="images/'.$row['path'].'.jpg" alt="'.$row['alt'].'" width="65" height="65"/></td>';
                                    echo'<td>'.$row['code'].'</td>';
                                    echo'<td>'.$row['description'].'</td>';
                                    echo'<td><p>&pound;'.$row['price'].'</p></td>';
                                    echo'<td><p><a title="remove from shopping cart" href="cart.php?remove='.$value.'">X</a></p></td>';
                                    echo'</tr>';                                
                                }
                            }
                            echo $cartTotal; //after running through all the items, you can show the total

                            echo'<tr>';
                            echo'<td></td>';
                            echo'<td></td>';
                            echo'<td></td>';
                            echo'<td><p>Total</p></td>';
                            echo'<td></td>';
                            echo'</tr>';
                            echo'</table>';
                        }
                    ?>
                    ?>

Upvotes: 2

Related Questions