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>&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

Posted: Sun Jan 08, 2006 10:23 pm
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

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

Image

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

Image


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