SUM Function

[himesh]

Im trying to get my basket to sum all of the items for a particular username. It will sum them and group them by the various usernames. How can I get it to just display for the current user which is logged in? Do I have to assign the values to an array and then output that array? I have tried using an if statement but that did not work. Here is the if statement that is not working.

<?php if ($_SESSION['MM_Username'] == $row_price['username']){echo $row_price['SUM(price)']; }?>

Judging from previous posts, it is not working as it has not gone through a loop to find it?

[justsomeguy]

You'll probably want to include the user in the SQL statement, so that you only return results from the database for that user instead of returning all of the results and then filtering them in PHP.

[himesh]

Ive just realised that I need to multiply the quantities by the price of the individual items in the cart. Attempting to do this ive tried to make a while loop :

<?php				$count = 0;		$qmp = 0;		$subtotal = 0;		$deliv = 5;		$total = $subtotal + $deliv;				while ($count < $totalRows_cart) {					$qmp = $row_cart['quantity'] * $row_cart['price'];		$subtotal = $subtotal + $qmp;				$count = $count+1;}				echo $total;		echo $count;		echo $qmp;		echo $subtotal;								?>

Ive put the echos in at the end to show what the loop is doing, the numbers that were printed to the screen were: 5 for total, 6 for count 0 for qmp and 0 for subtotal. So the loop is running 6 times as it should as there are 6 items in the cart but its not finding the numbers from the query. When i printed the query nothing was being found for $row_cart['quantity'] and for $row_cart['price']. Evidently this is the problem but ive done everything as I have before

[justsomeguy]

I'm not sure what to say, it just sounds like the query isn't returning the data you expect. So I guess you just need to look at the query to figure out why it's not returning the data. Are you selecting all of the columns you need?

[himesh]

When I echo the same fields above the table I have created, it runs fine but when I input it into the table, it does not work. Ive just tried echoing individual statements. When I echo total it still equals 5, count still equals 7 but when i echo subtotal I get 6380 and qmp I get 2340 which is the first multiplication (13 X 180) So at the minute im really confused as its working but not working at the same time. The total at the end of the loop should equal 8385. The calculations made along the way should be 2340, 4500, 500, 600, 400, 20, 25.

[himesh]

Forgot to add that ive selected all fields

[justsomeguy]

When I echo total it still equals 5, count still equals 7 but when i echo subtotal I get 6380 and qmp I get 2340

So then it sounds like it does work, before you said the subtotal was 0.Is this the loop you're using:

		while ($count < $totalRows_cart) {					$qmp = $row_cart['quantity'] * $row_cart['price'];		$subtotal = $subtotal + $qmp;				$count = $count+1;}

Because $row_cart never changes, it's always going to have the same values every time through the loop.

[himesh]

Ahh I see now, how is it possible to change the line which it is reading? I thought that the $count would do that but ive just realised that it is only increasing the $count.

[justsomeguy]

I don't know, how are you setting $row_cart in the first place?

[himesh]

$colname_cart = "-1";if (isset($_SESSION['MM_Username'])) {  $colname_cart = $_SESSION['MM_Username'];}mysql_select_db($database_conn_comm, $conn_comm);$query_cart = sprintf("SELECT * FROM tblcart WHERE username = %s ORDER BY quantity DESC", GetSQLValueString($colname_cart, "text"));$cart = mysql_query($query_cart, $conn_comm) or die(mysql_error());$row_cart = mysql_fetch_assoc($cart);$totalRows_cart = mysql_num_rows($cart);

[himesh]

Have just noticed that I don't need the order by and have removed it.

[justsomeguy]

If you're trying to loop through the rows in the $cart result, you can just use a regular while loop, you don't need a counter:while ($row_cart = mysql_fetch_assoc($cart)){...}

[himesh]

As I already have a repeat field on the basket which is

<?php do { ?>		<tr>		  <td><?php echo $row_cart['manufacturer']; ?></td>		  <td><?php echo $row_cart['modelnumber']; ?></td>		  <td><?php echo $row_cart['quantity']; ?></td>		  <td><?php echo $row_cart['price']; ?></td>		</tr>		<?php } while ($row_cart = mysql_fetch_assoc($cart)); ?>

It doesnt like having two of the same? So the cart is not printing but when i comment the loop, the basket prints. When i commented the basket part, the loop ran as follows, which is still not the number that it should be count = 6total = 5qmp = 25subtotal = 7785 The loop should also be running 7 times

[justsomeguy]

If you're trying to loop through the same result set more than once, you can use mysql_data_seek to reset the row pointer.

[himesh]

I have tried to use the data seek and found this on the php manual.

for ($i = 0; $i <= mysql_num_rows($cart); $i++) { if (!mysql_data_seek($cart, $i)) {	 echo "Cannot seek to row $i: " . mysql_error() . "\n";   continue;  }}$count = 0;$qmp = 0;$subtotal = 0;$deliv = 5;$total = $subtotal + $deliv;			while ($row_cart = mysql_fetch_assoc($cart)) {			$qmp = $row_cart['quantity'] * $row_cart['price'];		$subtotal = $subtotal + $qmp;				$count = $count+1;		}

Im getting this error message Warning: mysql_data_seek() [function.mysql-data-seek]: Offset 7 is invalid for MySQL result index 3 (or the query data is unbuffered) in /home/himeshs/public_html/communicate/basket.php on line 133Cannot seek to row 7:Line 133 is if (!mysql_data_seek($cart, $i)) {And neither the basket is displaying items or the loop working =s

[justsomeguy]

mysql_data_seek does not go in a loop. That loop will execute mysql_data_seek once for each row, and once the loop ends the pointer will once again be at the end of the result. You only need to use it once, set it to offset 0 to return to the first record.

[himesh]

Ahh, okay, Ive sorted that out so that the basket appears and the total appears but that part is still not working as it should.When i echo count i get 6 which is incorrect as there are 7 items in the cart, unless it is counting 0 as a line too?when i echo qmp i get 25 which is the last line in the cartwhen i echo subtotal i get 7785when i echo total i 7780 which is calculated by adding 5 onto the total for deliverywhat is confusing me is that the answer should be 9485 which I have calculated many times just to double check. I dont have an idea as to what to do next

[justsomeguy]

When i echo count i get 6 which is incorrect as there are 7 items in the cart, unless it is counting 0 as a line too?

$count is going to tell you how many times the loop ran, so if it's 6 then that means the result had 6 records in it. In that case, you'll probably want to look at the SQL statement that gets the data, run it in phpMyAdmin to see how many records it returns. Also make sure that you're resetting the result to position 0, not position 1. The first item in an ordered list is item 0, not item 1.

what is confusing me is that the answer should be 9485 which I have calculated many times just to double check. I dont have an idea as to what to do next

It's best to have the loop print all of the data in the row so that you can verify what it's doing. You can just use print_r in the loop to print all of $row_cart.

[himesh]

Just ran it in myadmin and it retrieves 7 results. This is my data seek

<?php mysql_data_seek($cart,0); ?>

I did the print and it turns out that it is missing the first line of the cart but when I try to put -1 it gives me a nice error message. I think its because of where ive positioned the data seek? This is my entire code, im not sure where to put it to make it work properly.

<?php require_once('Connections/conn_comm.php'); ?><?php//initialize the sessionif (!isset($_SESSION['MM_Username'])) {  session_start();}// ** Logout the current user. **$logoutAction = $_SERVER['PHP_SELF']."?doLogout=true";if ((isset($_SERVER['QUERY_STRING'])) && ($_SERVER['QUERY_STRING'] != "")){  $logoutAction .="&". htmlentities($_SERVER['QUERY_STRING']);}if ((isset($_GET['doLogout'])) &&($_GET['doLogout']=="true")){  //to fully log out a visitor we need to clear the session varialbles  $_SESSION['MM_Username'] = NULL;  $_SESSION['MM_UserGroup'] = NULL;  $_SESSION['PrevUrl'] = NULL;  unset($_SESSION['MM_Username']);  unset($_SESSION['MM_UserGroup']);  unset($_SESSION['PrevUrl']);	  $logoutGoTo = "loggedout.php";  if ($logoutGoTo) {	header("Location: $logoutGoTo");	exit;  }}?><?phpif (!function_exists("GetSQLValueString")) {function GetSQLValueString($theValue, $theType, $theDefinedValue = "", $theNotDefinedValue = "") {  if (PHP_VERSION < 6) {	$theValue = get_magic_quotes_gpc() ? stripslashes($theValue) : $theValue;  }  $theValue = function_exists("mysql_real_escape_string") ? mysql_real_escape_string($theValue) : mysql_escape_string($theValue);  switch ($theType) {	case "text":	  $theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";	  break;		case "long":	case "int":	  $theValue = ($theValue != "") ? intval($theValue) : "NULL";	  break;	case "double":	  $theValue = ($theValue != "") ? doubleval($theValue) : "NULL";	  break;	case "date":	  $theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";	  break;	case "defined":	  $theValue = ($theValue != "") ? $theDefinedValue : $theNotDefinedValue;	  break;  }  return $theValue;}}$colname_cart = "-1";if (isset($_SESSION['MM_Username'])) {  $colname_cart = $_SESSION['MM_Username'];}mysql_select_db($database_conn_comm, $conn_comm);$query_cart = sprintf("SELECT * FROM tblcart WHERE username = %s ", GetSQLValueString($colname_cart, "text"));$cart = mysql_query($query_cart, $conn_comm) or die(mysql_error());$row_cart = mysql_fetch_assoc($cart);$totalRows_cart = mysql_num_rows($cart);$colname_price = "-1";if (isset($_SESSION['MM_Username'])) {  $colname_price = $_SESSION['MM_Username'];}mysql_select_db($database_conn_comm, $conn_comm);$query_price = sprintf("SELECT username, SUM(price) FROM tblcart WHERE username = %s GROUP BY username", GetSQLValueString($colname_price, "text"));$price = mysql_query($query_price, $conn_comm) or die(mysql_error());$row_price = mysql_fetch_assoc($price);$totalRows_price = mysql_num_rows($price);mysql_select_db($database_conn_comm, $conn_comm);$item = $_POST['search'];$query_searchbar = "SELECT producttype, manufacturer, modelnumber, price, image FROM tblproduct WHERE producttype='$item' OR manufacturer='$item' OR modelnumber='$item';";$searchbar = mysql_query($query_searchbar, $conn_comm) or die(mysql_error());$row_searchbar = mysql_fetch_assoc($searchbar);$totalRows_searchbar = mysql_num_rows($searchbar);?><?php// *** Validate request to login to this site.if (!isset($_SESSION)) {  session_start();}$loginFormAction = $_SERVER['PHP_SELF'];if (isset($_GET['accesscheck'])) {  $_SESSION['PrevUrl'] = $_GET['accesscheck'];}if (isset($_POST['username'])) {  $loginUsername=$_POST['username'];  $password=$_POST['password'];  $MM_fldUserAuthorization = "userlevel";  $MM_redirectLoginSuccess = "loggedin.php";  $MM_redirectLoginFailed = "sorrylogin.php";  $MM_redirecttoReferrer = true;  mysql_select_db($database_conn_comm, $conn_comm);	    $LoginRS__query=sprintf("SELECT username, password, userlevel FROM tbluser WHERE username=%s AND password=%s",  GetSQLValueString($loginUsername, "text"), GetSQLValueString($password, "text"));      $LoginRS = mysql_query($LoginRS__query, $conn_comm) or die(mysql_error());  $loginFoundUser = mysql_num_rows($LoginRS);  if ($loginFoundUser) {		$loginStrGroup  = mysql_result($LoginRS,0,'userlevel');		//declare two session variables and assign them	$_SESSION['MM_Username'] = $loginUsername;	$_SESSION['MM_UserGroup'] = $loginStrGroup;		  	if (isset($_SESSION['PrevUrl']) && true) {	  $MM_redirectLoginSuccess = $_SESSION['PrevUrl'];		}	header("Location: " . $MM_redirectLoginSuccess );  }  else {	header("Location: ". $MM_redirectLoginFailed );  }}$count = 0;$qmp = 0;$subtotal = 0;$deliv = 5;		while ($row_cart = mysql_fetch_assoc($cart)) {		$qmp = $row_cart['quantity'] * $row_cart['price'];		$subtotal = $subtotal + $qmp;				$count = $count+1;		print_r ($qmp);			}$total = $subtotal + $deliv;?><!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"><html xmlns="http://www.w3.org/1999/xhtml"><head><meta http-equiv="Content-Type" content="text/html; charset=utf-8" /><title>Communicate - the best in mobile communications</title><link rel="stylesheet" type="text/css" href="_css/main.css" /><link rel="stylesheet" type="text/css" href="_css/style.css" />	<script src="jquery-1.2.1.min.js" type="text/javascript"></script>	<script src="menu.js" type="text/javascript"></script><!--[if IE]><style type="text/css"> /* place css fixes for all versions of IE in this conditional comment */.thrColElsHdr #sidebar1, .thrColElsHdr #sidebar2 { padding-top: 30px; }.thrColElsHdr #mainContent { zoom: 1; padding-top: 15px; }/* the above proprietary zoom property gives IE the hasLayout it needs to avoid several bugs */</style><![endif]--></head><body class="thrColElsHdr"><div id="container">  <div id="header">   <div id="search">   <form id="search" name="search" method="POST" action="<?php echo $loginFormAction; ?>">   <input type="text" name="search" id="search" />   </form>   </div>     <!-- end #header -->  </div>      <div id="sidebar1"><ul id="menu">						<li><a href="http://www.himeshs.co.uk/communicate/index.php">Home</a></li>									 														<li>							<a href="#">Manufacturer</a>							<ul> 							  <li><a href="http://www.himeshs.co.uk/communicate/allmanufacturer.php">All</a></li> 							<li><a href="http://www.himeshs.co.uk/communicate/apple.php">Apple</a></li> 						   	<li><a href="http://www.himeshs.co.uk/communicate/lg.php">LG</a></li> 							<li><a href="http://www.himeshs.co.uk/communicate/nokia.php">Nokia</a></li> 							<li><a href="http://www.himeshs.co.uk/communicate/samsung.php">Samsung</a></li> 							<li><a href="http://www.himeshs.co.uk/communicate/se.php">Sony Ericsson</a></li> 							</ul> 						</li>													 <li>							<a href="#">Type</a>								<ul> 							<li><a href="http://www.himeshs.co.uk/communicate/alltype.php">All</a></li> 							<li><a href="http://www.himeshs.co.uk/communicate/slide.php">Slide</a></li> 							<li><a href="http://www.himeshs.co.uk/communicate/candy.php">Candy Bar</a></li> 							<li><a href="http://www.himeshs.co.uk/communicate/flip.php">Flip</a></li>					  							</ul> 	  </li>																  <li>							<a href="#">Features</a>							<ul> 							<li><a href="http://www.himeshs.co.uk/communicate/camera.php">Camera</a></li> 							<li><a href="http://www.himeshs.co.uk/communicate/music.php">Music</a></li> 							<li><a href="http://www.himeshs.co.uk/communicate/internet.php">Internet</a></li>												  							</ul> 						</li>									<li><a href="http://www.himeshs.co.uk/communicate/aboutus.php">About us</a></li>							<li><a href="http://www.himeshs.co.uk/communicate/contact.php">Contact us</a></li>	</ul>  <!-- end #sidebar1 -->  </div>       <div id="sidebar2">    <?phpif(isset($_SESSION['MM_Username'])){  // Member is logged in so we have to display welcome message with userid and one logout link$html = <<<END_OF_HTMLWelcome {$_SESSION['MM_Username']}   <a href="$logoutAction ">Log out</a>END_OF_HTML;	echo $html; }  else {$form1 = <<<END_OF_HTML	  <form id="form1" name="form1" method="POST" action="{$loginFormAction}">			 <input type="text" name="username" id="username" /><br />	  Password<br />	<input type="password" name="password" id="password" /> <br /><input name="Submit" type="submit" value="Submit" /></form>END_OF_HTML;echo $form1;}?>    <!-- end #sidebar2 --></div>      <div id="mainContent">	<h1> Basket</h1>  		<p> </p>	<table border="0" cellpadding="0">	  <tr>		<td>manufacturer</td>		<td>modelnumber</td>		<td>quantity</td>		<td>price</td>	  </tr>	  	<?php mysql_data_seek($cart,0); ?>	  <?php do { ?>		<tr>		  <td><?php echo $row_cart['manufacturer']; ?></td>		  <td><?php echo $row_cart['modelnumber']; ?></td>		  <td><?php echo $row_cart['quantity']; ?></td>		  <td><?php echo $row_cart['price']; ?></td>		</tr>		<?php } while ($row_cart = mysql_fetch_assoc($cart)); ?>		<tr>		<td colspan="3">		Delivery		</td>		<td>		£5.00		<tr>		<td colspan="3">		Total Price		</td>		<td>				<?php 		echo $count;									?>  		<tr><td><?php echo $total;?></td>		</tr>				<tr><td><?php echo $qmp; ?></td>		</tr>				<tr><td><?php			echo $subtotal; ?></td>		</tr>	 				</td>		</tr>		</td>		</tr>	</table><!-- end #mainContent -->  </div>		<!-- This clearing element should immediately follow the #mainContent div in order to force the #container div to contain all child floats --><br class="clearfloat" />   <div id="footer">	<p>Footer</p>  <!-- end #footer --></div><!-- end #container --></div></body></html><?phpmysql_free_result($cart);mysql_free_result($price);mysql_free_result($searchbar);?>

[justsomeguy]

This is my entire code, im not sure where to put it to make it work properly.

Just put it right before the loop.You get $cart up here:$cart = mysql_query($query_cart, $conn_comm) or die(mysql_error());$row_cart = mysql_fetch_assoc($cart);Look at that, right after you get $cart, you immediately also get the first row. So when you loop farther down, the pointer is already pointing to the second row. If you really need to get the first row immediately for whatever reason, then reset the pointer before you loop through it.

[himesh]

Ive put it before the loop but I am still getting the wrong figure.

$count = 0;$qmp = 0;$subtotal = 0;$deliv = 5;		mysql_data_seek($cart,0);		while ($row_cart = mysql_fetch_assoc($cart)) {		$qmp = $row_cart['quantity'] * $row_cart['price'];		$subtotal = $subtotal + $qmp;				$count = $count+1;		//print_r ($qmp);			}$total = $subtotal + $deliv;

[justsomeguy]

Verify the data. Print the rows out. There's a very specific reason why the total is coming out like it is. That loop is doing a very specific thing, which is exactly what you told it to do. If the outcome is not what you expected, verify the data.

[himesh]

Oh my god what an idiot I am lol I made a miscalculation myself! The computer was right for the past few I think tiredness has finally set in! Thanks for your help yet again