Database Buffer

PHPRookie

I have a screen that allows me to enter a date range.

I have a MYSQL database that has records in it that each have a description field, category field and fields for up to seven different dates.

Once I enter a date range on my screen and hit the submit button, a query examines each record in the database looking to match the first day in the date range that I have input. The search examines each record in the database seven times, looking at each the first date fields in each of the record before looking at the second date field of each of the records and then the third date field and so on and so forth.

Immediately after examining the first date field for all of the records, the found records are printed to the screen sorted by the category field.

My problem is that I do not end up with a true sort by the category field for each of the dates of my input date range but a sort by the category field of records that have a date match for the first date field, and then the second field and then the third field.

Can I have the records put into some sort of buffer and then after they have all been pulled and placed in the buffer sort the records before outputting the records to the screen?

ahundiak

To store records in an array use:
$rows = mysql_query($sql);
while($row = mysql_fetch($rows))
{
$data[] = $row;
}
The use usort() to perform a custom sort on $data.

PHPRookie

Thanks. This is helpful. But how do I put a row of information in the array if each query result row has five pieces of information. Here is my code. I'm not sure if the following will get the record information pulled from the database into the array.

$result = mysql_query($sql1)
or die ("Couldn't execute the pull from database query.");
echo mysql_error();

			$num1 = mysql_numrows($result);

			while ($num1>0)
			{
			$row = mysql_fetch_array($result);
			extract ($row);
			$data = array
						(array
   (
						"day" => "$dayone",
						"date" => "$dayonedate",
						"eventtype" => "$eventtype",
						"borough"=>"$borough",
						"eventtitle"=>"$eventtitle"
						)
						);
					$num1--;

			}

ahundiak

You are just building an array of arrays.

Do your extract after doing the sort.

AstroTeg

Is it possible to post the query? Its possible its just a matter of specifying the order clause just right.

Sxooter

Just put the output of your select into a temporary table, then select and order by on that table.

In a more capable rdmbs, you could just use a subselect:

select * from (select ....) order by field1, field2;