Iterate over database table values

Question

I have a problem with trying to iterate over the amount of results returned in a query,I have a database table called Cart with the following fields:

ItemCode //Unique Code of Item

ItemDesc //Description / Name ofItem

ItemUnitPrice //Unit Price for Item

ItemCategory //Category of Item e.g. Books, CD, DVD etc...

Quantity //Quantity of Item(s) in cart

I want to loop over all the records displayed in my display.php (which simply prints all the data in the Cart table) and then multiply the ItemUnitPrice by the Quantity for every item and store it in a variable to store the total price for everything contained in display.php.

I want something like this:

LOOP
$Total= $ItemUnitPrice * $Quantity;
END LOOP

I am using MySQL and I'm not too sure how I should loop to get the total for each and every item.

So in a nutshell I want to find the total (ItemUnitPrice * Quantity) for each and every item in the database table and store it in a variable.

EDIT:

$query="SELECT * FROM Cart";
$result=mysql_query($query);
$num=mysql_num_rows($result);


$cartTotalPrice = 0;

while($row = mysql_fetch_assoc($result))
{
$cartTotalPrice += ($row['itemUnitPrice']*$row['Quantity']);

}
$_SESSION['totalCost'] = $cartTotalPrice;
mysql_close();
session_start();
echo "<b><center> Islamic Book Store - Your Shopping Cart </b><br/><br/>";

$i=0;

echo "<table border=1><tr><th>Item Code</th><th>Item Desc,</th>";
echo "<th> Item Unit Price</th><th>Item Category</th><th>Quantity</th><th>Image</th>    <th>Update Quantity</th></tr>";

while ($i < $num)

{

$ItemCode = mysql_result($result,$i,"ItemCode");
$ItemDesc = mysql_result($result,$i,"ItemDesc");
$ItemUnitPrice = mysql_result($result,$i,"ItemUnitPrice");
$ItemCategory = mysql_result($result,$i,"ItemCategory");
$Quantity = mysql_result($result,$i,"Quantity");


echo "<tr><td align=center>$ItemCode</td><td align=center>$ItemDesc</td>";
echo "<td align=center>£$ItemUnitPrice</td>";
echo "<td align=center>$ItemCategory</td><td align=center>$Quantity</td>";

$i++;

}



echo "</table><center>";
echo "$num Item(s) found.";

echo "<br/><br/><center><form action = 'clear.php'><input type='submit' value='Clear'>   </form></center>";



?>



<html>
<form action="https://www.paypal.com/cgi-bin/webscr" method="post" target="_BLANK">
<input type="hidden" name="cmd" value="_xclick" />
<input type="hidden" name="business" value="email@example.com" />
<input type="hidden" name="item_name" value="<? echo $ItemDesc ?>" />
<input type="hidden" name="item_number" value="TEST ITEM NUMBER" />
<input type="hidden" name="amount" value="<? echo $cartTotalPrice ?>" />
<input type="hidden" name="currency_code" value="GBP" />
<input type="hidden" name="lc" value="GB" />
<input type="hidden" name="bn" value="PP-BuyNowBF" />
<input src="paypal/purchase.png" name="Submit" type="image" value="purchase"    alt="Purchase" />
</form>
</html>

Do you need `ItemUnitPrice * Quantity` by row or the sum of all rows ?` — Mehdi Karamosly, May 29 '13 at 21:25
in your code put `session_start()` before `$_SESSION['totalCost']=...` and in your input value use `$_SESSION['totalCost']` if all the code is in the same file, you dont have to use Session it should work the way it is unless there is a query result issue try `var_dump($result);` to see the results. — Mehdi Karamosly, May 30 '13 at 15:22
No, result still returns 0, would be useful if you show the code — Ryman Holmes, May 30 '13 at 18:21

score 1 · Answer 1 · edited May 23 '17 at 11:50

1

First of all, get out of the habit of using mysql_* functions! Use PDO or mysqli.

Why shouldn't I use mysql_* functions in PHP?

http://php.net/pdo

Secondly, I'm sad to tell you, your code is completely wrong!

session_start();

$query = "SELECT * FROM Cart";
$result = mysql_query($query);
$num = mysql_num_rows($result);

$cartTotalPrice = 0;

while($row = mysql_fetch_assoc($result))
{
    $cartTotalPrice += ($row['itemUnitPrice']*$row['Quantity']);

    echo "<tr><td align=center>{$row['itemCode']}</td><td align=center>{$row['ItemDesc']}</td>";
    echo "<td align=center>{$row['ItemUnitPrice']}</td>";
    echo "<td align=center>{$row['$ItemCategory']}</td><td align=center>{$row['$Quantity']}</td></tr>";
}

$_SESSION['totalCost'] = $cartTotalPrice;

// $_SESSION['totalCost'] is now available on every page (as long as you use start_session() before any output)

mysql_close();

edited May 23 '17 at 11:50

Community

1
1

answered May 29 '13 at 20:49

Phil Cross

9,017
12
50
84

Hey, thanks for the complement but the PHP code that I've shown is NOT trying to do what I've asked its simply setting the variables for each field in the table so I can later use them for displaying as it shows... So my code is NOT COMPLETELY wrong you simply mistook it for something else – Ryman Holmes May 29 '13 at 20:56
Certainly. If your code above is within a function IE: `function someFunction(){ /* the above code */ }`, then the `$cartTotalPrice` value is only available within that function. However, you may want to store it in a session object. I'll update my answer with how to do this. In short, yes. – Phil Cross May 29 '13 at 21:09
It still returns 0 as `$_SESSION['totalCost'] = $cartTotalPrice;` is picking up `$cartTotalPrice` outside the loop where it is first initialized to 0 so it is giving me a result of 0 back. – Ryman Holmes May 29 '13 at 21:17
I simply want to echo it outside the loop in a `
`
– Ryman Holmes May 29 '13 at 21:19
It's still returning 0, even with the `session_start();` – Ryman Holmes May 29 '13 at 21:43
I can only assume the the while loop isn't being run, either due to an sql error, or no results are being returned from the query. Unfortunately I can't help any further without seeing the entire code being used. – Phil Cross May 29 '13 at 21:46
I've just noticed something, where is `$row` initialized? – Ryman Holmes May 29 '13 at 21:49
Row is automatically populated with the result of `mysql_fetch_assoc ()` – Phil Cross May 29 '13 at 21:51
Have just edited the code, I have now inserted my entire code please have a look – Ryman Holmes May 29 '13 at 21:57
It is in the HTML section where I am referring to the `$cartTotalPrice` in particular the `` row – Ryman Holmes May 29 '13 at 22:01

score 0 · Answer 2 · answered May 30 '13 at 23:51

Try the below code I hope it works, I made the corrections and mentioned them in comments:

$query="SELECT * FROM Cart";
$result=mysql_query($query);
$num=mysql_num_rows($result);


$cartTotalPrice = 0;

while($row = mysql_fetch_assoc($result))
{
$cartTotalPrice += ($row['itemUnitPrice']*$row['Quantity']);

}


 session_start();
 $_SESSION['totalCost'] = $cartTotalPrice;
   // mysql_close(); // here you are closing your mysql connection before using mysql_result refer to the example in http://php.net/manual/en/function.mysql-result.php
   // session_start(); // session start should go above  you dont need session if the whole script is in the same page. no need to save the variable to session
echo "<b><center> Islamic Book Store - Your Shopping Cart </b><br/><br/>";

$i=0;

echo "<table border=1><tr><th>Item Code</th><th>Item Desc,</th>";
echo "<th> Item Unit Price</th><th>Item Category</th><th>Quantity</th><th>Image</th>    <th>Update Quantity</th></tr>";

while ($i < $num)

{

$ItemCode = mysql_result($result,$i,"ItemCode");
$ItemDesc = mysql_result($result,$i,"ItemDesc");
$ItemUnitPrice = mysql_result($result,$i,"ItemUnitPrice");
$ItemCategory = mysql_result($result,$i,"ItemCategory");
$Quantity = mysql_result($result,$i,"Quantity");


echo "<tr><td align=center>$ItemCode</td><td align=center>$ItemDesc</td>";
echo "<td align=center>£$ItemUnitPrice</td>";
echo "<td align=center>$ItemCategory</td><td align=center>$Quantity</td>";

$i++;

}



echo "</table><center>";
echo "$num Item(s) found.";

echo "<br/><br/><center><form action = 'clear.php'><input type='submit' value='Clear'>   </form></center>";


mysql_close();
?>



<html>
<form action="https://www.paypal.com/cgi-bin/webscr" method="post" target="_BLANK">
<input type="hidden" name="cmd" value="_xclick" />
<input type="hidden" name="business" value="info@asianweddingservices.org" />
<input type="hidden" name="item_name" value="<? echo $ItemDesc ?>" />
<input type="hidden" name="item_number" value="TEST ITEM NUMBER" />
<input type="hidden" name="amount" value="<? echo $cartTotalPrice ?>" />
<input type="hidden" name="currency_code" value="GBP" />
<input type="hidden" name="lc" value="GB" />
<input type="hidden" name="bn" value="PP-BuyNowBF" />
<input src="paypal/purchase.png" name="Submit" type="image" value="purchase"    alt="Purchase" />
</form>
</html>

Iterate over database table values

2 Answers2