Counting prices from mysql database

[McMaster]

Hey,

 

I am trying to calculate all of the prices in a database within one variable but it doesn't seem to work. The output I get is only the first price and not the total calculation. The code is as follows:

 

$sql = mysql_query("SELECT * FROM shopping_cart WHERE username = '".$_SESSION['myusername']."'");

$price = 0;

while ($row = mysql_fetch_assoc($sql)) {
  $sql1 = mysql_query("SELECT * FROM products WHERE id = '".$row['product_id']."'");
  $row1 = mysql_fetch_assoc($sql1);

  $price = $price + $row1['price'];
}
echo $price; //Output total price

 

Any help appreciated here guys. Thanks!

[Mchl]

How about doing it the right way?

 

<?php
$query = "SELECT 
  SUM(price) AS totalPrice 
FROM
  shopping_cart AS c
INNER JOIN
  products AS p
ON
  p.id = c.product_id
WHERE
  c.username = {$_SESSION['myusername']}";

if(!$result = mysql_query($query)) {
  die("Query error: ".mysql_error());
}

$row = mysql_fetch_assoc($result);

echo $row['totalPrice'];

[mrMarcus]

ya, Mchl beat me to it.  Rarely (if ever) a reason you should have a query within a loop of any sort.  The equivalent, and highly more efficient way (not to mention, correct way) is to JOIN the queries/tables.

[Psycho]

Just to beat a dead horse:

 

The only thing that irritates me worse than seeing "looping" queries is when people write the SQL statement directly in the mysql_query() function instead of create a string var. By writing directly in the query function you lose the ability to easliy debug database errors.

[Mchl]

Just to beat a dead horse:

 

The only thing that irritates me worse than seeing "looping" queries is when people write the SQL statement directly in the mysql_query() function instead of create a string var. By writing directly in the query function you lose the ability to easliy debug database errors.

 

That's why ext/mysqli is so cool. You can extend it, so that all error handling is done WITHIN query funciton