[php/mysql] Generating statistics problem

[alein]

Hello.

I’d be happy if I get some help for a statistics that struggle to make.

My goal is to show how many iterations (set to static array) in every column from mysql result. (hopefully express myself correctly.)

Static arrays are 3 and used to convert numbers into meaningful information.
For example:

[php]<?php
$proffNames =
array
(
1 => “Cook”,
2 => “Guardian”,
3 => “Master”,
4 => “Cashier”,
5 => “Doctor”,
6 => “Driver”,
7 => “Manager”,
8 => “Opreator”,
9 => “Programmer”,
11 => “Service”
);

$genderNames =
array
(
0 => “Male”,
1 => “Woman”
);

$nationalNames =
array
(
1 => “Bulgarian”,
2 => “Romanian”,
3 => “German”,
4 => “Spanish”,
5 => “Italian”,
6 => “French”,
7 => “Russian”,
8 => “Greek”,
10 => “English”,
11 => “Austrian”
);
?>[/php]

With mysql query I care (if at all what I need to show from mysql), but I have difficulties with the calculation.

Mysql query to the server is the following.
I redefined a variable to be used in mysql select.

Suppose that in this case is 34
/* $checkedId = 34; */

SELECT `account`.`national`, `account`.`proff`, `account`.`age`, `account`.`gender` FROM `account` LEFT JOIN `group_account` ON `group_account`.`id` = `account`.`id` WHERE `group_account`.`groupId` = $checkedId AND `account`.`age` >= 10

Results for this query looks like this:

+----------+-------+-----+--------+ | national | proff | age | gender | +----------+-------+-----+--------+ | 4 | 11 | 71 | 0 | | 1 | 2 | 26 | 1 | | 1 | 2 | 46 | 1 | | 4 | 1 | 12 | 0 | | 11 | 2 | 32 | 1 | | 4 | 6 | 62 | 1 | +----------+-------+-----+--------+

My problem has a solution with several requests to mysql server, but I want the query to be optimized (subject to resources)

The goal is as I wrote above I get something like this:

Nationality:
Bulgarian (Count)
Romanian (Count) …

occupations:
Cooks (Count)
Drivers (Count)
Managers (Count) …

Similarly for the other 2 columns.

Can you point me to an example to spare several days searching.
Thanks in advance.

[phphelp]

Well, since you need an aggregated info for each column, I think the only way is to make a query for each column (probably this is how you have it now?). For example, for Nationality your query would look like this:

SELECT `account`.`national`, count(`account`.`id`) as ncount FROM `account` LEFT JOIN `group_account` ON `group_account`.`id` = `account`.`id` WHERE `group_account`.`groupId` = $checkedId AND `account`.`age` >= 10 GROUP BY `account`.`national`
The query above will return the following results (based on your example):

+----------+--------+ | national | ncount | +----------+--------+ | 4 | 3 | | 1 | 2 | | 11 | 1 | +----------+--------+

So, if you have 4 columns, you will need to make 4 queries.

Other solution what I personally often use (if there is not too much data) would be to leave your original query unchanged, and have 1 loop where you count all the stats. This way you have just 1 query to a database. The loop code will be something like this:
[php]// $sql - is the query from the first post
$r = mysql_query($sql);

$stats[‘national’] = array();
$stats[‘proff’] = array();
$stats[‘age’] = array();
$stats[‘gender’] = array();

for($i=0;$i<mysql_num_rows($r);$i++){
$f = mysql_fetch_array($r);
$stats[‘national’][$f[‘national’]]++;
$stats[‘proff’][$f[‘proff’]]++;
$stats[‘age’][$f[‘age’]]++;
$stats[‘gender’][$f[‘gender’]]++;
}

// showing results
echo ‘

’;

print_r($stats);

echo ‘

’;[/php]

[alein]

Thank you for the quick reply.

Your idea is great, but I get only undefined variables notices:
national
proff
age
gender

And the result of all is numbers of rows.

Best regards.

[phphelp]

These notices means that array elements are not defined at given scope of the script. You can define them like this:
[php]$stats = array(‘national’ => array(), ‘proff’ => array(), ‘age’ => array(), ‘gender’ => array());[/php]

But I find it boring to define each variable - PHP does not require variable declaration, though it is recommended. I just have error reporting set to E_ALL ^ E_NOTICE (see details here).

[alein]

Thank you for your generous support.

I wish you all the best.
alein