Using MySQL Groupby Effectively - how?

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
simonmlewis
DevNet Master
Posts: 4435
Joined: Wed Oct 08, 2008 3:39 pm
Location: United Kingdom
Contact:

Using MySQL Groupby Effectively - how?

Post 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'>";  
?>
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Re: Using MySQL Groupby Effectively - how?

Post by VladSun »

I can't see the "SELECT ... GROUP BY" query in your code...
There are 10 types of people in this world, those who understand binary and those who don't
simonmlewis
DevNet Master
Posts: 4435
Joined: Wed Oct 08, 2008 3:39 pm
Location: United Kingdom
Contact:

Re: Using MySQL Groupby Effectively - how?

Post 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.";}
?>
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Re: Using MySQL Groupby Effectively - how?

Post by VladSun »

GROUP BY is intended to be used with aggregate functions.
Post some examples of DB data and the expected result.
There are 10 types of people in this world, those who understand binary and those who don't
simonmlewis
DevNet Master
Posts: 4435
Joined: Wed Oct 08, 2008 3:39 pm
Location: United Kingdom
Contact:

Re: Using MySQL Groupby Effectively - how?

Post 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.
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
Post Reply