[arrays] how so properly split them

[ajdegans]

Hi there, im working on a array issue that rises when generating statistics for a IRC chatroom that i log into mysql.

Im not very experienced with arrays like this...

I create the array holding all channel data:

 
$sql = "SELECT * FROM ".$SQL['prefix']."log WHERE channel = '".$channel."' ORDER BY id";
$query = mysql_query($sql) or die("Getting data for array failed!<br />".mysql_error());
$i=0;
while($data = mysql_fetch_assoc($query)) {
	$RAWDATA[$i] = array(
		"thetime" 	=> $data['thetime'], // unixtime
		"channel" 	=> $data['channel'], // the channelname
		"username" 	=> $data['username'], // the chatters name
		"host"		=> $data['host'], // the chatters host
		"action" 	=> $data['action'], // the action (join,quit,part,privmsg etc)
		"text" 		=> $data['text'], // the text spoken/chatted
	);
	$i++;
}

This goes just fine. The $data['xx'] vars represent db fields or columns or whatever you call it.

This creates a huge array.

I want to split/chunk that array into smaller arrays (or readable data). To get stuff like who typed the most lines, who types the most words, at which time the chatters are active. most used links and stuff like that.

A bit like PISG for those familiar with IRC stats pages.
(http://pisg.sourceforge.net/)

My problem is how to properly split/chunk the array. I thought of splitting the array immediatly into smaller ones like so:

$sql = "SELECT * FROM ".$SQL['prefix']."log WHERE channel = '".$channel."' ORDER BY id";
$query = mysql_query($sql) or die("Getting data for array failed!<br />".mysql_error());
$i=0;
while($data = mysql_fetch_assoc($query)) {
	$RAWDATA[$i] = array(
		"thetime" 	=> $data['thetime'],
		"channel" 	=> $data['channel'],
		"username" 	=> $data['username'],
		"host"		=> $data['host'],
		"action" 	=> $data['action'],
		"text" 		=> $data['text'],
	);
	if($RAWDATA[$i]['action'] == "PRIVMSG") {
		$LINES[$i] = $RAWDATA[$i]['text'];
	}
	if($RAWDATA[$i]['action'] == "JOIN") {
		$JOINS[$i] = $RAWDATA[$i]['text'];
	}
	if($RAWDATA[$i]['action'] == "QUIT") {
		$QUIT[$i] = $RAWDATA[$i]['text'];
	}
	$i++;
}

This creates (for now) 3 other arrays holding all normal talked text, all joins and all quits.

Im not sure on how to continue from there.
Also im a bit fuzzy on how to get the data from those smaller arrays properly and show the stuff i want. like the array which shows quits how do i tell which user has quitted the most times. or how to tell which chatter typed the most lines. (i guess the way of extractging is fairly similiar to the amount of quits)

Can anyone give me some pointers, code snippets, perhaps...

Thanks alot in advance!

[visualAd]

Use SQL, this is what it does best. The cost of executing a few more queries is nothing compared to the amount of runtime processing your script will have to do to manipulate the data. The below query for example will return all users and commands. Grouping by username and command and returning the number of times the user executed each command:

SELECT username,text,COUNT(*) total FROM log WHERE channel=1 GROUP BY username,text;

[ajdegans]

I was under the impression that using more queries was slower in performance.

Is it really the other way around?

[visualAd]

Queries are slower. But it looks to me like you want to load a huge chunk of data into memory and replicate it in several ways in order to obtain different interpretations of the said data. Multiply that by the number of concurrent requests and it will quickly eat up your resources.

Using queries is a better option because databases maintain indexes and mange the memory and data more efficiently.

If you are concerned about the number of queries being executed, consider using a dshared database connection and cache the results of the queries in files, refreshing them say every 5 minutes.

[ajdegans]

i could run a set of queries every 5 or 10 or whatever minutes, write all data ordered and processed to an xml styled file? that might work.
Or would another format be more efficient? what do you think...