Group By

Hello.
First off, I'm not a database guy... I can muddle through, but I'm not all that up on things there.
What I have is a list of purchases.

I have a type of purchase (like - at the register, mail order, online purchase)
Then I have a location (like - Storefront, kiosk, magazine)
Then I have the details of the purchase. Amount of purchase, style etc.

What I want to do is sort by "type of purchase"
And then group all of the purchases together, like.. all storefront purchases together, all kiosk purchases etc.
I do not want to repeat the title "storefront" or "kiosk" for every record.. just once is enough. I don't need anything totaled or anything, just grouped.

Example:
Purchases At Register:

Storefront:
Item 1
Item 2
Item 3

Kiosk:
Item 4
Item 5
Item 6


I'm guessing this isn't going to be terribly hard, but I have no idea how to do it, and I haven't found anything in the forum to suggest this. Can someone please help?

Thanks
Steve

Hi Steve,

It will be something like

<?php
$query = "
  SELECT * 
    FROM `#__purchases`
    GROUP BY `location`
    ORDER BY `type of purchase` ;
";
$db->setQuery($query);
. . .

Bob

PS I forget if GROUP BY comes before or after ORDER BY - the MySQL manual or a simple test will soon tell you.

Hey Bob,
Thanks for getting back to me on this.

Actually, what I am looking for is the "body" for my connectivity page.
It looks like I posted this in the wrong area.

Steve

Hi Steve,

I moved us over to the ChronoConnectivity forum.

You'll still need the GROU BY . . . ORDER BY . . . bit in the CC Order by box

Then in the body you'll need place holders for the results. I usually use a table put <table><thead>. . .</thead> in the header box and </table> in the footer box.

The build a table row in the body box<tr><td>{fieldname_1}/td><td>{fieldname_2}</td>. . .</tr>

Bob

Bob,
Thanks so much for your help...
But... I do not see a "CC Order By Box"

Can you give me a screen shot? Maybe I'm just dense. I do see the "order fields" edit box... but no place where I should put "group by"

Thanks again
Steve

Anybody?

There is no "CC ORDER BY" BOX!

Is there even a MANUAL for this??

Hours and hours trying to figure this SIMPLE thing out.... VERY frustrating

Hi Steve,

It's Saturday here. . .

CC = ChronoConnectivity, and yes the box is called Order fields - it has ORDER BY pretty clearly in the tool tip though


Bo

Hey Bob,
Thanks for the reply.
Ok, I have the "order By" piece... But how do I do the "Group By" piece on the front end?

I'm sorry... I've just been doing "mind numbing" data entry all day.

Steve

Hey Bob,
Now I'm not at all sure what I told you I wanted is really what will work. *sigh*
I have the "ORDER BY" working. No problem there.

What I have right now is something like this:
storefront:
Item #1
storefront:
Item #2
storefront:
Item #3
Kiosk
Item #4
Kiosk
Item #5
Kiosk
Item #6


What I want to get to is:
storefront:
Item #1
Item #2
Item #3
Kiosk
Item #4
Item #5
Item #6

I would like this displayed on the front end.
I don't need anything totaled... I just don't want to repeat the location every time.

Does this make sense?

I have been playing around with php if statements and variables, while loops and that sort of thing now for about 4 hours and I just can't figure this out.

I'm sure it's pretty simple, I just can't seem to wrap my mind around it.

If you can help, I would really appreciate it (or anyone else for that matter)

Steve

Hi Steve,

I'm having trouble visualising this - can you post (here or in a PM ) an example of your body code.

You probably can't run a 'for' loop inside the body box, but you could save the previous title and only show a new one when it changed. In pseudo code:

if ( $this_title == $last_title ) {
  // skip title
} else {
  //  show title
}
$last_title = $this title
// show item

Bob

Hello Bob,
Any more ideas on this?
Thanks for your help.

Steve

Hi Steve,

I've just posted a reply to Bulfn33 on the same issue. My solution here should work but you have to use the $MyRow->title syntax to set the values for $last_title and $this_title. Here's an example using slightly different field names.

<?php
$new_name = $MyRow->select_0; // set the new name
if ($last_name != $new_name) { // test if new name equals last one
<h2>{select_0}</h2>
<?php 
} 
?>
<div>{text_1} : {select_0}</div>
<br clear="both" />
<?php
$last_name = $MyRow->select_0; //update the last name now
?>

Bob

Later: removed a few uneccesary lines from the code

Perfect!!!

Thank you VERY much Bob, that did the trick!!

Steve