Page 1 of 3
Joins
Posted: Sun Jan 08, 2006 3:02 pm
by Jim_Bo
Hi,
I cant seem to get this join to work:
Code: Select all
SELECT o.*, c.*
FROM ordercontent as o
LEFT JOIN cart as c ON c.orderid = o.orderid
WHERE c.userid = ".$_SESSION['userid']."
GROUP BY c.orderid
Im trying to get all the records = to $_SESSION['userid'] and group them by "orderid"
Tables:
Code: Select all
CREATE TABLE cart (
orderid int(25) NOT NULL auto_increment,
userid int(25) NOT NULL default '',
total decimal(11,2) NOT NULL default '0',
orderdate datetime NOT NULL default '0000-00-00 00:00:00',
approved varchar(5) NOT NULL default '',
PRIMARY KEY (orderid)
) TYPE=MyISAM;
CREATE TABLE ordercontent (
contentid int(25) NOT NULL auto_increment,
orderid int(25) NOT NULL default '',
userid int(25) NOT NULL default '',
itemdesc text NOT NULL default '',
subtotal decimal(11,2) NOT NULL default '0',
qty int(25) NOT NULL default '',
PRIMARY KEY (contentid)
) TYPE=MyISAM;
Cheers
Posted: Sun Jan 08, 2006 3:06 pm
by MaNiAC
Code: Select all
FROM ordercontent as o
LEFT JOIN cart as c
Don't feel like testing it... but I learned it this way
Code: Select all
FROM ordercontent O
LEFT JOIN cart C
So it would be
Code: Select all
SELECT O.*, C.*
FROM ordercontent O
LEFT JOIN cart C ON O.orderid = C.orderid
WHERE C.userid = ".$_SESSION['userid']."
GROUP BY C.orderid
Also note how I changed O and C around in the ON part
EDIT:
by the way, use as for:
Code: Select all
SELECT O.orderid AS 'oid', C.*
FROM ordercontent O
LEFT JOIN cart C ON O.orderid = C.orderid
WHERE C.userid = ".$_SESSION['userid']."
GROUP BY C.orderid
Posted: Sun Jan 08, 2006 3:35 pm
by Jim_Bo
Thats giving me the same result as the join I posted ..
It looks like it is only bringing back the first record for each orderid?
Thanks
Posted: Sun Jan 08, 2006 4:33 pm
by John Cartwright
Jim_Bo wrote:Thats giving me the same result as the join I posted ..
It looks like it is only bringing back the first record for each orderid?
Thanks
The GROUP BY clause will only return the first record of each group, I think what your after is a ORDER BY clause
Posted: Sun Jan 08, 2006 4:39 pm
by Jim_Bo
Hi,
What I am after is ..
find all records = to userid
group records by orderid
so I loop thru the records and place all records = orderid 10 1st table
orderid 12 2nd table ... and so on
using a ORDER BY clause prins each record into a new table in the loop?
Make sence?
Code: Select all
<?php
$sql = mysql_query("SELECT O.*, C.*
FROM ordercontent O
LEFT JOIN cart C ON O.orderid = C.orderid
WHERE C.userid = ".$_SESSION['userid']."
GROUP BY O.orderid
ORDER BY C.orderid DESC");
while($row = mysql_fetch_array($sql)){
$sub = $row['qty'] * $row['subtotal'];
?>
<table width="98%" border="1" align="center" cellpadding="0" cellspacing="0" bordercolor="#000000">
<tr>
<td width="64%"><center><b><?php echo stripslashes($row['orderdate']); ?>Description</b></center></td>
<td width="12%"><div align="right"><b>Price</b></div></td>
<td width="12%"><center><b>Qty</b></center></td>
<td width="12%"><div align="right"><b>Sub Totals</b></div></td>
</tr>
<tr>
<td><?php echo stripslashes($row['itemdesc']); ?></td>
<td><div align="right"><?php echo stripslashes(number_format($row['subtotal'], 2)); ?></div></td>
<td><center><?php echo stripslashes($row['qty']); ?></center></td>
<td><div align="right"><?php echo number_format($sub, 2); ?></div></td>
</tr>
<tr>
<td> </td>
<td> </td>
<td><div align="right"><b>Total</b></div></td>
<td><div align="right"><?php echo number_format($row['total'], 2); ?></div></td>
</tr>
</table><br />
<?php
}
?>
Cheers
Posted: Sun Jan 08, 2006 10:23 pm
by Jim_Bo
I will use image to explain:
Here is what the code outputs:
Here is what I want to output from the results
The first table displays all records from table "ordercontent" with orderid --> 10
The second table displays all records from table "ordercontent" with orderid --> 14
etc etc?
Thanks
Posted: Mon Jan 09, 2006 7:08 am
by timvw
In that case you don't want to group by... simply order by orderpid.. and calculate the total per orderid yourself..
A more advanced strategy is to use a "pivot table" but i don't think mysql supports that (meaby the WITH ROLLUP statement can be helpful).
Posted: Mon Jan 09, 2006 10:17 pm
by Jim_Bo
Hi,
Any chance of a example .. still havnt managed to make it perform what I need ..
Only thing I can think of is 2 querys, but im sure there is a better way?
Thanks
Posted: Tue Jan 10, 2006 7:47 am
by raghavan20
Code: Select all
$sql = mysql_query("SELECT O.*, C.*
FROM ordercontent O
LEFT JOIN cart C ON O.orderid = C.orderid
WHERE C.userid = ".$_SESSION['userid']."
ORDER BY O.userid DESC");
The first point is you should not be using group by as it would show only record for the field you want to group...for more information look
here
Second, if you want to see the orders made by every user as a group then you should order by userid not by orderid.....try the query i have given you instead...
Posted: Tue Jan 10, 2006 2:32 pm
by Jim_Bo
no that creates the same as
I want it to group all orders with the same orderid into groups like so:
Stuck!
Thanks
Posted: Tue Jan 10, 2006 3:16 pm
by feyd
Code: Select all
SELECT
*
FROM
`ordercontent` o,
`cart` c
WHERE
o.`orderid` = c.`orderid`,
c.`userid` = '{$_SESSION['userid']}'
ORDER BY
c.`orderid` DESC,
o.`contentid` DESC
GROUP BY
o.`contentid`
Posted: Tue Jan 10, 2006 5:14 pm
by RobertGonzalez
Why the opposition to two queries? Performance wise it shouldn't impact your application that much. If you really want to use one query, select all of the records, then walk the array creating an array of groups, then run the grouped array and iterate the entire query through the group array matching ID's as you go along. Seems a little lengthy when you could just run two queries (grouped and not) then walk the result set for the group and within that walk, walk the result set for the entire result.
Posted: Tue Jan 10, 2006 5:34 pm
by Jim_Bo
feyd wrote:Code: Select all
SELECT
*
FROM
`ordercontent` o,
`cart` c
WHERE
o.`orderid` = c.`orderid`,
c.`userid` = '{$_SESSION['userid']}'
ORDER BY
c.`orderid` DESC,
o.`contentid` DESC
GROUP BY
o.`contentid`
Thats doesnt work .. but looks like it would do the job.
Gives me an error on the line:
while($row = mysql_fetch_array($sql)){
?
Thanks
Posted: Tue Jan 10, 2006 6:31 pm
by John Cartwright
try
Code: Select all
$result = mysql_query($sql_code_here) or die(mysql_error());
Posted: Tue Jan 10, 2006 6:47 pm
by feyd
oops.. swap out the comma in the where clause for an AND