Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.
Moderator: General Moderators
Jim_Bo
Forum Contributor
Posts: 390 Joined: Sat Oct 02, 2004 3:04 pm
Post
by Jim_Bo » Sun Jan 08, 2006 3:02 pm
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
MaNiAC
Forum Newbie
Posts: 20 Joined: Fri Dec 23, 2005 4:20 am
Post
by MaNiAC » Sun Jan 08, 2006 3:06 pm
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
Jim_Bo
Forum Contributor
Posts: 390 Joined: Sat Oct 02, 2004 3:04 pm
Post
by Jim_Bo » Sun Jan 08, 2006 3:35 pm
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
John Cartwright
Site Admin
Posts: 11470 Joined: Tue Dec 23, 2003 2:10 am
Location: Toronto
Contact:
Post
by John Cartwright » Sun Jan 08, 2006 4:33 pm
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
Jim_Bo
Forum Contributor
Posts: 390 Joined: Sat Oct 02, 2004 3:04 pm
Post
by Jim_Bo » Sun Jan 08, 2006 4:39 pm
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
Jim_Bo
Forum Contributor
Posts: 390 Joined: Sat Oct 02, 2004 3:04 pm
Post
by Jim_Bo » Sun Jan 08, 2006 10:23 pm
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
timvw
DevNet Master
Posts: 4897 Joined: Mon Jan 19, 2004 11:11 pm
Location: Leuven, Belgium
Post
by timvw » Mon Jan 09, 2006 7:08 am
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).
Jim_Bo
Forum Contributor
Posts: 390 Joined: Sat Oct 02, 2004 3:04 pm
Post
by Jim_Bo » Mon Jan 09, 2006 10:17 pm
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
raghavan20
DevNet Resident
Posts: 1451 Joined: Sat Jun 11, 2005 6:57 am
Location: London, UK
Contact:
Post
by raghavan20 » Tue Jan 10, 2006 7:47 am
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...
Jim_Bo
Forum Contributor
Posts: 390 Joined: Sat Oct 02, 2004 3:04 pm
Post
by Jim_Bo » Tue Jan 10, 2006 2:32 pm
no that creates the same as
I want it to group all orders with the same orderid into groups like so:
Stuck!
Thanks
feyd
Neighborhood Spidermoddy
Posts: 31559 Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA
Post
by feyd » Tue Jan 10, 2006 3:16 pm
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`
RobertGonzalez
Site Administrator
Posts: 14293 Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA
Post
by RobertGonzalez » Tue Jan 10, 2006 5:14 pm
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.
Jim_Bo
Forum Contributor
Posts: 390 Joined: Sat Oct 02, 2004 3:04 pm
Post
by Jim_Bo » Tue Jan 10, 2006 5:34 pm
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
feyd
Neighborhood Spidermoddy
Posts: 31559 Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA
Post
by feyd » Tue Jan 10, 2006 6:47 pm
oops.. swap out the comma in the where clause for an AND