Using MySQL Groupby Effectively - how?
Posted: Mon Jul 26, 2010 5:49 am
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:
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'>";
?>