Group And Order by troubles

[artech]

Okay, so this may be so much more simple than I am finding it to be, but I just can't wrap my head around what I need to be doing here.

 

I have records in a database, and only a few fields are relevant here.

 

table = 'tblName'

 

fields: (all variable names and column names have been made generic for clarity)

 

pk_recordID

lastname

groupname

UNIXdatetime

date

 

 

auto_increment integer

string

integer

integer

date

 

1

a

1001

1272266174

2010-04-30

... etc

 

 

I have a form that I should allow my client to create a 'custom' query to pull a batch of records.

The query will select all fields, but the form gives them the following options

 

limit to a start datetime which after processing is stored as a UNIX timestamp in $start_date

limit to an end datetime which after processing is stored as a UNIX timestamp in $end_date

if they don't select any dates, $start_date is set to the earliest UNIX value and $end_date is set to UNIX version of now()

 

if they select only one date, the other is filled as just described.

 

they may also limit the records to a certain groupname (which is an integer)

 

if they don't choose a groupname in the form, it is not included in my where.

 

here's what I've got for the where statement

// create the select portion of the SQL
$select = "SELECT * FROM tblName";

// create the where statement
// start the where statement with a space
	$where = " ";
	$where_end = "";
	if ($filter_group) {
		$where .= "groupname=".$filter_group." AND (";
		$where_end = ")";
	}

	$where .= "UNIXdatetime>=".$start_date." AND UNIXdatetime<=".$end_date.$where_end;

 

I don't know that this is the best way to go about the first part, but there we are.

 

Now for (what I think will be) GROUP BY and ORDER BY:

 

The form gives the client 3 levels (first_sort, second_sort, third_sort) of sorting/grouping all with the same options (date ascending, date descending, lastname ascending, lastname descending, groupname ascending).

 

the way I want this to happen is that the second sort option is 'nested in?' the first sorting option, and the third within the second

 

so, for example, if the client selects:

 

first_sort = groupname ascending

second_sort = date descending

third_sort = lastname ascending

 

I want the results to be grouped first by groupname (1001, then 1002, then 1003, etc)

but then within each groupname grouped by date (1272655073, 1272266174, 1272179667, etc)

but then within each date, sort records by last name (a, b, c, etc)

 

if the client only selects an option for the first sorting level, the records can be further sorted however the DB wishes. This seems like a simple GROUP BY statement

 

if the client selects an option for both the first and second sorting level, the records can be further sorted however the DB wishes. This seems like a GROUP BY statement followed by an ORDER BY statement.

 

This is where I get stuck. I can't wrap my head around what I need to do to get this part working with 3 sorting levels.

 

I will probably just do a php check of the post vars to determine whether I need to group on 1, 2, or 3 options (and then produce different values for a grouping variable based on this check).

 

My final query should built from these vars like this (at least the way I have envisioned this so far)

 

$batch_SQL = $select . $where . $grouping;

 

So, I haven't given the SQL a shot for the grouping because I just can't figure out what I need to do or even really how GROUP and ORDER work together. I've never used even used GROUP BY and have never needed to ORDER BY more than a single field.

 

 

Thanks so much for your guidance/suggestions/etc!  Hopefully I'm not being too much of an idiot here!

 

Cheers,

~me

 

[artech]

By the way:

 

MySQL: 5.1.x (not sure, think x=46?)

PHP: 5.3.2

 

and

 

Table: tblName
Create Table: CREATE TABLE tblName (
  pk_recordID INT(11) default NULL auto_increment,
  lastname char(60) default NULL,
  groupname int(4) default NULL,
  UNIXdatetime int(12) default NULL,
  mydate date default NULL,
  PRIMARY KEY (pk_recordID)
) ENGINE=MyISAM

 

NOTE: the column mydate above is what I called "date" in the original post. YYYY-MM-DD corresponding to the UNIX stamp.

 

thanks again!

[artech]

also by the way,

 

this should say:

$where = " WHERE ";

 

the query up to that point

$sql = $select . $where;

 

is properly returning limited results as expected.

[Ken2k7]

The PHP code is a bit confusing too because you have some code that doesn't really do anything except store some values into variables. I don't even know the final result of the SQL. So please post relevant bits of code or post what SQL you tried but it doesn't return the correct values.

[artech]

The php vars from the original posts, when put together, result in (for example) the following SQL statement:

 

SELECT * FROM tblName WHERE groupname=1002 AND UNIXdatetime>=1270450801 AND UNIXdatetime<=1272351599

 

And this is returning the expected rows (i.e., 30 rows like this:)

Array ( [pk_recordID] => 19 [lname] => lastname [groupname] => 1002 [mydate] => 2010-04-26 [uNIXdatetime] => 1272266174 ) 

 

So. I know it's working up through my WHERE statement.

 

I've been trying something like this:

 

My last attempt: 

SELECT * FROM tblName 
WHERE groupname=1002 AND UNIXdatetime>=1270450801 AND UNIXdatetime<=1272351599 
GROUP BY groupname ASC, mydate DESC 
ORDER BY lname ASC

 

but it's not giving me the results I need.

 

The query I just wrote is intended (although obviously failing) to give me 30 results.

 

Result of that sql should be grouped first by groupname (there are 30 records total, with 10 each for groupname == 1002, 1003, and 1004).

 

within each groupname (10 records) there are 5 dates (2 records per date). So within 1002, there should be 2 records listed as 2010-04-30, 2 records listed as 2010-04-29, etc. and the records should be grouped by these dates within the groupname groups.

 

Within each of these groups of 2 records with the same date within the same groupname, the records should be sorted by lastname in ascending order.

 

The results of my attempts are not coming back as expected. For example, I'm only getting 20 of the 30 records, and they are correctly grouped by ascending groupname, but the order of the date sorting does not change, and within the dates, the lastname is not sorting anything at all. If I try grouping first by date and then by group number, sorting last by lastname instead of groupname first and then date, I get all 30 results but the ASC and DESC doesn't do anything, groupname doesn't do anything, and lastname doesn't do anything...

 

I feel like this should be fairly simple. I know that I've made this question much more difficult than it needed to be.

 

A simplified version of my question is:

 

How do I group by a column (ASC OR DESC depending on client input), group the records inside of each of those groups (ASC OR DESC depending on client input), and sort the records inside of each subgroup (ASC or DESC depending on client input).

 

 

 

 

[Ken2k7]

It doesn't make a lot of sense to GROUP more than one column. You can order by more than one. Can you provide sample data where you need to GROUP multiple columns? So just give an example of the records in the database and give the final result on what the outcome should be.

[artech]

I don't know that I need to GROUP on one column. I didn't know *what* I need to be doing, LOL.

 

If I GROUP BY the first column, and then sort by 2 columns, will they do what I basically want?

 

i.e.,

 

GROUP BY groupname ASC
ORDER BY mydate DESC, lname ASC

 

would produce something that looks like this:

 

1001  |  2010-04-30  |  abc
1001  |  2010-04-30  |  bcd
1001  |  2010-04-29  |  ghi
1001  |  2010-04-29  |  hij
...

...
1002  |  2010-04-30  |  rst
1002  |  2010-04-30  |  stu
1002  |  2010-04-29  |  xyz
1002  |  2010-04-29  |  yza

...etc...

 

 

because this is also not giving me consistent results (which is why I started trying grouping by 2 columns, lol)

 

Thanks!

[Ken2k7]

How did you want the results to look like?

[artech]

They should come up just as in the previous post.

 

The actual records are pretty randomly distributed. Basically the user can pick 3 criteria for sorting/grouping. Each option could be several different things - this will let them create a "custom" query.

 

So I need the results to group by $sort_option_1, the group/sort by $sort_option_2, and then order by $sort_option_3 as shown in the example in my previous post.

 

My example was only one possibility because the client could group by groupname, date, or lastname, then by (one of the remaining 2 options), and then by the third option.

 

And the client can also choose ASC or DESC for each of the options.

 

Does that make sense at all?

 

Thanks !

[Ken2k7]

Do you need GROUP BY then? Why not just ORDER BY groupname DESC, mydate DESC, lastname DESC?

[artech]

Yep, my ignorance made that whole thing WAY too complicated. Thanks for sticking with me on that, I learned a lot! 

 

When would I want to use GROUP BY ?

[Ken2k7]

Usually when you need to count up a set.

[artech]

Thanks again!