[PHP] Help grouping query results by Calamari - TribalWar Forums
Click Here to find great hosting deals from Branzone.com


Go Back   TribalWar Forums > TribalWar Community > General Discussion
Reload this Page [PHP] Help grouping query results
Thread Tools
Calamari
VeteranXV
Old
1 - 07-03-2008, 09:11
Reply With Quote
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?
 
Calamari is offline
 
Sponsored Links
trop
VeteranX
Old
2 - 07-03-2008, 09:14
Reply With Quote
set x to the derivative of y and then calculate for wind changes
 
trop is offline
 
Rayn
Tribalwar Admin
Contributor
Old
3 - 07-03-2008, 09:18
Reply With Quote
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','fie ld1',},Array{'value2','fi eld2'}}

then you can retrieve the arrays via category id. use a foreach loop to iterate through your months and output the arrays.
 
Rayn is offline
 
Calamari
VeteranXV
Old
4 - 07-03-2008, 09:38
Reply With Quote
Thanks Rayn. I'll give that a try and let you know how it works.
 
Calamari is offline
 
Littlex
VeteranX
Old
5 - 07-03-2008, 09:53
Reply With Quote
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);
 
Littlex is offline
 
Calamari
VeteranXV
Old
6 - 07-03-2008, 10:55
Reply With Quote
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.
 
Calamari is offline
 
Monkey_b
VeteranX
Old
7 - 07-03-2008, 11:24
Reply With Quote
Give me an example of the array structure you want and I will give you the code.
 
Monkey_b is offline
 
Garion
VeteranX
Old
8 - 07-03-2008, 11:42
Reply With Quote
Wow, I'd forgotten how ugly PHP is.
 
Garion is offline
 
Calamari
VeteranXV
Old
9 - 07-03-2008, 12:15
Reply With Quote
Quote:
Originally Posted by Monkey_b View Post
Give me an example of the array structure you want and I will give you the code.
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.
 
Calamari is offline
 
Monkey_b
VeteranX
Old
10 - 07-03-2008, 13:06
Reply With Quote
yep, here you go:
Quote:
$result = mysql_query("SELECT * FROM table");

$dataSet = Array();

while($record = mysql_fetch_array($result )) {
$dataSet[$record['year'].$record['month']] = $record;
}
 
Monkey_b is offline
 
ThisIsNotMyName
VeteranX
Contributor
Old
11 - 07-03-2008, 13:57
Reply With Quote
Quote:
Originally Posted by Garion View Post
Wow, I'd forgotten how ugly PHP is.
are u on crack?
 
ThisIsNotMyName is offline
 
Garion
VeteranX
Old
12 - 07-03-2008, 14:13
Reply With Quote
Quote:
Originally Posted by Wendel View Post
are u on crack?
no r u??
 
Garion is offline
 
Dman
VeteranX
Old
13 - 07-03-2008, 16:28
Reply With Quote
Quote:
Originally Posted by Monkey_b View Post
yep, here you go:
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.
 
Dman is offline
 
Monkey_b
VeteranX
Old
14 - 07-03-2008, 16:32
Reply With Quote
duh, I'm an idiot, simple fix though

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

$dataSet = Array();

while($record = mysql_fetch_array($result )) {
array_push($dataSet[$record['year'].$record['month']],$record);
}
no need for all the complicated stuff you just posted
 
Monkey_b is offline
 
Dman
VeteranX
Old
15 - 07-03-2008, 16:37
Reply With Quote
Quote:
Originally Posted by Monkey_b View Post
duh, I'm an idiot, simple fix though

this will do it for sure

no need for all the complicated stuff you just posted
fine

I was just making it a little more verbose for his needs, that was the first function that came to mind.
 
Dman is offline
 


Go Back   TribalWar Forums > TribalWar Community > General Discussion
Reload this Page [PHP] Help grouping query results

Social Website Bullshit

Tags
matthew dewald is fat


Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
Thread Tools

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off


AGENT: CCBot/2.0 (https://commoncrawl.org/faq/) / Y
All times are GMT -4. The time now is 09:00.