Jump to content

Crazzy Arrazy


Recommended Posts

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'];

		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

Link to comment
Share on other sites

Guest Anonymous

Re: Crazzy Arrazy

Go Floydian!

Sorry to say I use a criminal record table:





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.

Link to comment
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

  • Create New...