Joins

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

Joins

Post 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
MaNiAC
Forum Newbie
Posts: 20
Joined: Fri Dec 23, 2005 4:20 am

Post 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
Jim_Bo
Forum Contributor
Posts: 390
Joined: Sat Oct 02, 2004 3:04 pm

Post 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
User avatar
John Cartwright
Site Admin
Posts: 11470
Joined: Tue Dec 23, 2003 2:10 am
Location: Toronto
Contact:

Post 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
Jim_Bo
Forum Contributor
Posts: 390
Joined: Sat Oct 02, 2004 3:04 pm

Post 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>&nbsp;</td>
  <td>&nbsp;</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 »

I will use image to explain:

Here is what the code outputs:

Image

Here is what I want to output from the results

Image

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 »

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 »

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
User avatar
raghavan20
DevNet Resident
Posts: 1451
Joined: Sat Jun 11, 2005 6:57 am
Location: London, UK
Contact:

Post 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...
Jim_Bo
Forum Contributor
Posts: 390
Joined: Sat Oct 02, 2004 3:04 pm

Post by Jim_Bo »

no that creates the same as

Image

I want it to group all orders with the same orderid into groups like so:

Image


Stuck!


Thanks
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post 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`
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Post 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.
Jim_Bo
Forum Contributor
Posts: 390
Joined: Sat Oct 02, 2004 3:04 pm

Post 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
User avatar
John Cartwright
Site Admin
Posts: 11470
Joined: Tue Dec 23, 2003 2:10 am
Location: Toronto
Contact:

Post by John Cartwright »

try

Code: Select all

$result = mysql_query($sql_code_here) or die(mysql_error());
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

oops.. swap out the comma in the where clause for an AND
Post Reply