Page 1 of 1

Using MySQL Groupby Effectively - how?

Posted: Mon Jul 26, 2010 5:49 am
by simonmlewis
Hi
I am inserting content into a database - TWICE - from a shopping cart.
First time round is to enter the users details, (name etc) and the product details.
The second time round is to enter the postage costs (this does not include the name and address details).

Both inserts also insert ORDERID which is a random number generated. So if there are 5 products, you would get 5 rows of products with the same order id, and then 1 row of postage with that same orderid.

This works fine.

Then I want to view a 'groupby' list of products ordered, but for some reason when I use GROUPBY it also groups but uses the LAST entry to group it, and as such, it's using the row with the least data to show in the GROUPBY query.

It doesn't matter if I put the postage in first or last - it always uses the Postage field that has the least information entered.

How do I change this to use the 'most information'? Or moreso, why is it always doing it this way?

CODE HERE:

Code: Select all

<?php
$cookieuserid = $_COOKIE['userid'];
$cookiefirstname = $_COOKIE['firstname'];
$cookielastname = $_COOKIE['lastname'];
$orderid = (rand()%99999999);
$total = $_POST['ordervalue'];
$postagecost = $_POST['postagecost'];
$today = (date('Y-m-d'));
include "dbconn.php";

// INSERTS POSTAGE  
$result = mysql_query ("SELECT * FROM products WHERE catname = 'Postage' AND price = '$postagecost'");
while ($row = mysql_fetch_object($result))
        {
mysql_query("INSERT INTO `orders` (orderid, userid, orderdate, prodid, prodvalue, postage) VALUES 
('$orderid', '$cookieyuserid', '$today', '$row->id', '$postagecost', 'Yes')")or die(mysql_error()); 
}
mysql_free_result($result);


$result = mysql_query ("SELECT * FROM cart WHERE userid = '$cookieuserid'");
while ($row = mysql_fetch_object($result))
      {
      $resultuser = mysql_query ("SELECT * FROM admin WHERE id = '$cookieuserid'");
while ($rowuser = mysql_fetch_object($resultuser))
        {
mysql_query("INSERT INTO orders (orderid, orderdate, userid, prodid, prodvalue, firstname, lastname, address1, address2, town, postcode) VALUES 
('$orderid', '$today', '$row->userid', '$row->prodid', '$row->credits', '$cookiefirstname', '$cookielastname', '$rowuser->address1', '$rowuser->address2', '$rowuser->town', '$rowuser->postcode')");

mysql_query("DELETE FROM cart WHERE userid = '$cookieuserid'");

$resultcred = mysql_query ("SELECT credits FROM admin WHERE id = '$cookieuserid'");
while ($rowcred = mysql_fetch_object($resultcred))
          {
        $newcredits = $rowcred->credits - $total;
        mysql_query("UPDATE admin SET credits = '$newcredits' WHERE id = '$cookieuserid'");
          }mysql_free_result($resultcred);
        } mysql_free_result($resultuser);
      } mysql_free_result($result);
        
mysql_close($sqlconn);   

echo "<meta http-equiv='Refresh' content='0 ;URL=index.php?page=completed&menu=home&head=order completed'>";  
?>

Re: Using MySQL Groupby Effectively - how?

Posted: Mon Jul 26, 2010 6:06 am
by VladSun
I can't see the "SELECT ... GROUP BY" query in your code...

Re: Using MySQL Groupby Effectively - how?

Posted: Mon Jul 26, 2010 6:30 am
by simonmlewis
Sorry - wrong damn code!!

Here's the right code:

Code: Select all

<?php 
$cookietype=$_COOKIE['type'];
$id = $_REQUEST['id'];
$u = $_REQUEST['u'];

include "dbconn.php";
if ($cookietype == "admin")
{

if ($u == "del") 
{
mysql_query ("DELETE FROM orders WHERE id = '$id'");
}

	  // how many rows to show per page
$rowsPerPage = 30;
// by default we show first page
$pageNum = 1;
// if $_GET['pagenum'] defined, use it as page number
if(isset($_GET['pagenum']))
{
    $pageNum = $_GET['pagenum'];
}

$sort=$_REQUEST['sort'];
if ($sort == NULL) { $sort = "orderdate";}


// counting the offset
$offset = ($pageNum - 1) * $rowsPerPage;
$result = mysql_query ("SELECT * FROM `orders` GROUP BY orderid ORDER BY $sort LIMIT $offset, $rowsPerPage") or die (mysql_error());
echo "<table class='table' cellpadding='3' cellspacing='0' width='713'><tr>
<td><a href='index.php?page=a_orders&menu=blank&head=orders&sort=orderid'>Order ID</a></td>
<td><a href='index.php?page=a_orders&menu=blank&head=orders&sort=firstname'>Firstname</a></td>
<td><a href='index.php?page=a_orders&menu=blank&head=orders&sort=lastname'>Lastname</a></td>
<td><a href='index.php?page=a_orders&menu=blank&head=orders&sort=town'>Town</a></td>
<td><a href='index.php?page=a_orders&menu=blank&head=orders&sort=postcode'>Postcode</a></td>
<td><a href='index.php?page=a_orders&menu=blank&head=orders&sort=value'>Total Value</a></td>
<td><a href='index.php?page=a_orders&menu=blank&head=orders&sort=orderdate'>Order date</a></td>
<td><a href='index.php?page=a_orders&menu=blank&head=orders&sort=despatchdate'>Despatch date</a></td>
<td></td>
</tr>";
while ($row = mysql_fetch_object($result))
      {
			echo "<tr bgcolor='#FFFFFF' onMouseOver=\"this.bgColor='#E7E7E7';\" onMouseOut=\"this.bgColor='#FFFFFF';\">
			<td><a href='index.php?page=a_order&menu=blank&id=$row->orderid' style='text-decoration: none'>$row->orderid</a></td>
			<td>$row->firstname</td>
      <td>$row->lastname</td>
      <td>$row->town</td>
      <td>$row->postcode</td>
      <td>";
      $ordervalue = $ordervalue + $row->prodvalue;
      echo "$ordervalue</td>
      <td>$row->orderdate</td>
      <td>";
      if ($row->despatchdate == NULL) { echo "Not despatched.";}
      else { echo "$row->despatchdate";}
      echo "</td>
      <td><a href='index.php?page=a_orders&menu-blank&head=orders&u=del&id=$row->id' style='text-decoration: none; color: #ff0000'>X</a></td>";
      }
mysql_free_result($result);
echo "</tr>";
	echo "</table><div style='clear:both' /><hr noshade size='1' />";
	$query   = "SELECT COUNT(id) AS numrows FROM `orders` GROUP BY id";
$result  = mysql_query($query) or die(mysql_error());
$row     = mysql_fetch_array($result, MYSQL_ASSOC);
$numrows = $row['numrows'];
    // how many pages we have when using paging?
$maxPage = ceil($numrows/$rowsPerPage);
 
// print the link to access each page
$self = $_SERVER['PHP_SELF'];
$nav  = '';
 
for($page = 1; $page <= $maxPage; $page++)
{
   if ($page == $pageNum)
   {
      $nav .= " $page "; // no need to create a link to current page
   }
   else
   {
      $nav .= " <a href=\"index.php?page=categ&menu=sub&c=$c&cname=$mycateg&head=$mycateg&sort=$sort&pagenum=$page\" class='bodylink'>$page</a>";
   }
}
 
// creating previous and next link
// plus the link to go straight to
// the first and last page
 
if ($pageNum > 1)
{
   $page  = $pageNum - 1;
   $prev  = " <a href=\"index.php?page=categ&menu=sub&c=$c&cname=$mycateg&head=$mycateg&sort=$sort&pagenum=$page\" class='bodylink'>[Prev]</a> ";
 
   $first = " <a href=\"index.php?page=categ&menu=sub&c=$c&cname=$mycateg&head=$mycateg&sort=$sort&pagenum=1\" class='bodylink'>[First Page]</a>";
}
else
{
   $prev  = '&nbsp;'; // we're on page one, don't print previous link
   $first = '&nbsp;'; // nor the first page link
}
 
if ($pageNum < $maxPage)
{
   $page = $pageNum + 1;
   $next = " <a href=\"index.php?page=categ&menu=sub&c=$c&cname=$mycateg&head=$mycateg&sort=$sort&pagenum=$page\" class='bodylink'>[Next]</a>";
 
   $last = " <a href=\"index.php?page=categ&menu=sub&c=$c&cname=$mycateg&head=$mycateg&sort=$sort&pagenum=$maxPage\" class='bodylink'>[Last Page]</a>";
}
else
{
   $next = '&nbsp;'; // we're on the last page, don't print next link
   $last = '&nbsp;'; // nor the last page link
}
 
// print the navigation link
echo "<div class='navpages'>" . $first . $prev . $nav . $next . $last . "</div>";
	mysql_close($sqlconn);
}
else
{ echo "Sorry, you have reached this page in error.";}
?>

Re: Using MySQL Groupby Effectively - how?

Posted: Mon Jul 26, 2010 9:30 am
by VladSun
GROUP BY is intended to be used with aggregate functions.
Post some examples of DB data and the expected result.

Re: Using MySQL Groupby Effectively - how?

Posted: Mon Jul 26, 2010 10:30 am
by simonmlewis
Not sure I can do that.

Basically if 30 items are bought, there will be 30 rows each with the firstname, last name etc in each one.
But just ONE row for postage that has far less info in the rows.

It's using that row for the groupby. I want it to use any of the others.