[PHP] Help grouping query results

C

Calamari

Veteran XV
Ok,

I've got this dataset that's based on a category ID. For the sake of argument, lets say that the dataset contains the following fields:

Code: 
category_id, entry_id, title, month, year

Assuming that I've already pulled all of the data where category_id = x, how would I build a PHP script to group that data based on the month/year that it has. In other words, each month/year has several entries, and I need them to both appear in chronological order (easy), and be grouped together based on the month (part that is driving me insane).

Any ideas?
 
You're going to make a hashtable where the key is a string (categoryid) and the value is an array of data. if you need more than one field of data per array (you want to associate datarows), make a hashtable from the key to a 2-dimensional array.

so you could have this:

hashtable[category1] -> Array{'value1','value2','value3'}

or this

hashtable[category1] -> Array{Array{'value1','field1',},Array{'value2','field2'}}

then you can retrieve the arrays via category id. use a foreach loop to iterate through your months and output the arrays.
 
Get your data, iterrate over those and just simply push each entry to a specific array key:

Code: 
$data = mysql .... => SELECT category_id, entry_id, title, month FROM table ORDER BY month ASC;

$data = array(
    array(
        'category_id' => 1,
        'entry_id' => 1,
        'title' => 'Tribal',
        'month' => 01
    ),
    array(
        'category_id' => 1,
        'entry_id' => 1,
        'title' => 'Tribal',
        'month' => 01
    ),
    array(
        'category_id' => 1,
        'entry_id' => 1,
        'title' => 'Tribal',
        'month' => 01
    ),
    array(
        'category_id' => 1,
        'entry_id' => 1,
        'title' => 'Tribal',
        'month' => 02
    ),
    array(
        'category_id' => 1,
        'entry_id' => 1,
        'title' => 'Tribal',
        'month' => 02
    ),
    array(
        'category_id' => 1,
        'entry_id' => 1,
        'title' => 'Tribal',
        'month' => 02
    ),
);


$groupedByMonth = array();

foreach ($data AS $row) {
    $grouped[$row['month']][] = $row;
}

var_dump($groupedByMonth);
 
Still having some trouble wrapping my brain around this one. The category ID isn't important, I don't need to break down the data by category at all once I've gotten the dataset, I just need the data grouped by month/year.

Littlex - I tried to implement your example by building the arrays dynamically with the following code:
Code: 
$query_entryIds = "query string"
$result_entryIds = mysql_query($query_entryIds) or die (mysql_error());

$count_entryIds = 0;
while($row_entryIds = mysql_fetch_array($result_entryIds))
{
	extract($row_entryIds);
	
	if($count_entryIds == 0)
	{
		$data = array(
					array(
						'entry_id' => $entry_id,
						'title' => $title,
						'year' => $year,
						'month' => $month
						)
					);
	}
	else
	{
		$data = array(
					$data.
					array(
						'entry_id' => $entry_id,
						'title' => $title,
						'year' => $year,
						'month' => $month
						)
					);
	}
	$count_entryIds++;
}

$groupedByMonth = array();
foreach ($data AS $row) {
    $grouped[$row['month']][] = $row;
}

var_dump($groupedByMonth);

And it produced an invalid argument error.

Rayn, in your example, what kind of a loop construct would I use to populate the hashtable based on month (or is that even what I need to do?)

Sorry, I'm venturing into territory that I'm not familiar with here.
 
Monkey_b said:
Give me an example of the array structure you want and I will give you the code.
Click to expand...

I think I need an array that looks like this, that is somehow keyed on the individual month/year combinations, so:

$array[month/year] = array(
array(
entry_id = $entry_id,
title = $title,
year = $year,
month = $month));

Does that make sense? The 'master' array is keyed on the month/year, and that array is populated by an array of arrays (the last array being each individual result).

I *think* that is what I need in the end, but I could be wrong.
 
yep, here you go:
$result = mysql_query("SELECT * FROM table");

$dataSet = Array();

while($record = mysql_fetch_array($result)) {
$dataSet[$record['year'].$record['month']] = $record;
}
Click to expand...
 
Monkey_b said:
yep, here you go:
Click to expand...
Not exactly. He said there may (or will) be multiple records for a given month/year combo. That means whenever you encounter a record with the same year.date signature it will write over the record you had before.

Code: 
$result = mysql_query("SELECT * FROM table");

$dataSet = Array();

while($record = mysql_fetch_array($result )) {
if(isset($dataSet[$record['year'].$record['month']]))
{
$insideArray = $dataSet[$record['year'].$record['month']];
}
else
{
$insidearray = Array();
}
$insidearray[] = $record;
$dataSet[$record['year'].$record['month']] = $insidearray;
}
I believe this should work I didn't test it.
 
duh, I'm an idiot, simple fix though

this will do it for sure
$result = mysql_query("SELECT * FROM table");

$dataSet = Array();

while($record = mysql_fetch_array($result )) {
array_push($dataSet[$record['year'].$record['month']],$record);
}
Click to expand...
no need for all the complicated stuff you just posted :)
 
Monkey_b said:
duh, I'm an idiot, simple fix though

this will do it for sure

no need for all the complicated stuff you just posted :)
Click to expand...

fine ;)

I was just making it a little more verbose for his needs, that was the first function that came to mind.
 
You must log in or register to reply here.
Back
Top