Crazzy Arrazy

[Floydian]

Alrighty, here's a little something that is holding me up and I'm pretty stumped trying to find a way to do this that isn't completely wasteful of resources and time.

Here's the deal, each player in my game has a column in the users table for crime stats. This crime stats column is a text column that stores a serialized array.

The array is formatted as follows:

$crime_stats[$category_id][$crime_id] = array('total' => 0, 'successes' => 0, 'failures' => 0);

Of course there are multiples crime categories, and many different crimes in those categories. I would like to find a way to figure out who has the most successes (top 5) for each crime whilst also adding up the total successes for each crime over all players together.

What I've done is query the crime stats array from the users table and store them in an array with the userid as the key and the crime stats for each player as the value, so we end up with a massive array. (Naturally this code will only be executed once per day, possibly only once per week if it really becomes an issue...)

Then I've got a set of three foreach loops, and after that, I begin to lose it lol. I haven't even run this code, number one because it's not complete, and number two because even if it were complete I know it wouldn't work.

 

foreach ($crime_stats as $them_id => $them_data) {
foreach ($them_data as $crime_category => $category_data) {
	foreach ($category_data as $crime_id => $data) {
		if (!isset($crime_total[$crime_category][$crime_id])) {
			$crime_total[$crime_category][$crime_id]['total'] = 0;
			$crime_total[$crime_category][$crime_id]['successes'] = 0;
			$crime_total[$crime_category][$crime_id]['failures'] = 0;
		}

		if (isset($data['total'])) {
			$crime_total[$crime_category][$crime_id]['total'] += $data['total'];
			$total_20[$them_id] 

		}
		if (isset($data['successes'])) {
			$crime_total[$crime_category][$crime_id]['successes'] += $data['successes'];
		}
		if (isset($data['failures'])) {
			$crime_total[$crime_category][$crime_id]['failures'] += $data['failures'];
		}
	}
}
}

 

I'm not so much looking for someone to write this code for me, but I would greatly appreciate a helpful push in the direction I should be going in. Of course if you feel so inclined to write something up, I won't stop you! But seriously, I think I just need to get my perspective on this, and then from there I'll be able to handle it.

Thanks in advance for your help. :D

[Guest Anonymous]

Re: Crazzy Arrazy

Go Floydian!

Sorry to say I use a criminal record table:

UserID : INT UNSIGNED NOT NULL

CrimeID : INT UNSIGNED NOT NULL

SuccessCount : INT UNSIGNED NOT NULL

FailureCount : INT UNSIGNED NOT NULL

Primary (UserID, CrimeID)

Adding data is simple, use the INSERT INTO ... ON DUPLICATE KEY UPDATE statement

Extracting data is a simple of case a join vs. the crime table, nice and fast.

Of course, there will be NumCrimes * NumUsers rows in this, but as each row is in compact format (assuming InnoDB tables or around 17 bytes fr MySQL tables) this should not be an issued.

[Floydian]

Re: Crazzy Arrazy

Yeah, I guess I should go that route. Thanks for the push ;)