Grouping

[Anon]

Hi All,

I am attempting to group records together by the date column. That is the easy part, obviously done by an ORDER command in the mysql query.

Now, what I would liek to do is to just have the date show up once. So, say there are 3 records for February 28, 2002. I want the date to show, and then the 3 records to show below it. Then, March 1, 2002 show up, followed by its records.

I am sure I am missing something fairly obvious, but I have not yet been able to discover a mySQL or a PHP function to do this.

Thanks for any help.

[[deleted]]

GROUP is completely different from ORDER.

What you want to do is print the records sorted by date (using ORDER BY date)

You can only print it the way you want by using PHP. Databases cannot do this (well, then can technically, but that requires DBA knowledge)

Select the records ORDER BY date.
Loop through them and look at the date of each record.
If the date of the next record is different from the date in the last recordm then print the date, else print the record.

[Anon]

Thanks. I know that GROUP is completely different than ORDER.

My hangup is figuring out how to tell it to compare the date of the current and previous record. I am relatively new to PHP Coding. I know how to compare 2 variables, just not when it comes to current or previous records.

Thanks.

[Anon]

Well when outputting the contents of your table, you must have a loop to iterate through each record, so it's really just a matter of using your variables correctly. In pseudocode the algorithm looks like this:

prevdate = 0;

loop throuh all records
get thisdate
if (thisdate!=prevdate) {
print a new date heading
prevdate = thisdate
}
print other fields of the record
next record

Until you have this working I suggest that you do not use a <TABLE> to format the HTML output, since this will complicate matters.

When you DO come to format your output in a <TABLE> tag, it will be tricky making date values appear within a lefthand collumn (since they are appearing only once for each group of records of the same date) ...but I'm sure you'll cross that bridge when you come to it.

Nojer2

[Anon]

There may be a better way of achieving the desired result but try this...

let me know if you need any further explanation of the code:

// get all the distinct date values from the tables (in this case it is colors)
$query = "select distinct (date) from colours";
$query_result = mysql_query_exec($db_handle, $query, true);

// for each row of the results above run another query to get all the
// records for each date coming out, basically running a query for each unique
// date in your table
while ($array_of_dates = mysql_fetch_array($query_result))
{
// build a query to get all the records based upon the date taken
// from the results of the previous query
$date_query = "select * from colours where date = '$array_of_dates[date]'";
$date_query_result = mysql_query_exec($db_handle, $date_query, true);

	// set i to 0s
	$i = 0;

		// now loop around the results of the second query
		while ($all_date_info_array = mysql_fetch_array($date_query_result))
			{
				// check if $i is 0, if it is then output the date
				// if it is not 0 then do not output the date
				// $i is always 0 on the first time round so here we
				// only echoing the date out once
				if ($i == 0)
				{
					echo "<b>" . $all_date_info_array[date] . "</b>";
				}

				// then echo out any other fields taken from the above query
				echo "<font color = red><br>" . $all_date_info_array[name] . "</font><p>";

				// increment $i by one so the date is not echoed out again
				$i++;
			}
}

[[deleted]]

That is not 'better', just cleaner.

'My' method runs only one big query, and your method runs 1 + 'the number of blocks in the result' queries, which could mean 10, 50, a hundred queries? Multiply that by the time it takes to run one query and you may be a lot slower, which could be a problem.