Page 1 of 2

Demographics

Posted: Fri Aug 06, 2004 12:14 pm
by evilmonkey
Hello. This is my first post in PHP Theory and Design, and I'm posting here not because I have a syntax question, but because I have a theory question. I'm making demographics of memebers on my site. I have a table that contains all the info I'd need to for the demographics, but I pull one demographic at a time with my function. For example, the function can only create data for age on one run. I want it to output something like "15% of the users are 20, 2% are 21, 40% are 30..etc" if the demographic in question is age. I have started coding this, and got this:

Code: Select all

<?php
/**
	 @param string $demographic
	 @returns 
	 @desc Demographics for the site. Parameter options are "age", "sex", and "country".
	 Sample return for age: 15% are 16 years old, 10% are 20 years old, etc...
	 */
	 function demographics ($demographic){
	 	$users = sql_pull("SELECT `$demographic` FROM `users`", $db);
	 	for ($i=0, $finish=sizeof($users);$i<$finish;$i++){
//then i get confused...
}
}
?>
A not on sql_pull(): it performs the MySQL query, and returns the result in a 2D array, through which I can cycle using a for loop.

Okay, I know I need the amount of people of a certain characteristic (i.e. of one age), and I need the total users. I have the total users, (sizeof($users), I'm just uinsure how to get the amount of users of a certain characteristic. Ideas?

Thanks! :D

Posted: Fri Aug 06, 2004 12:25 pm
by hawleyjr
Do one query to get the totals of all of the fields you are going to do % on.

Code: Select all

<?php
$qry = "SELECT COUNT('age') as age_count, COUNT('CITY') as city_count FROM MY_TABLE";
?>
And for each field do the following:

Code: Select all

<?php
$qry = "SELECT DISTINCT('age') as dst_age, COUNT('age') as cnt
FROM MY_TABLE
GROUP BY dst_age";
?>
You will then be able to do the necessary math to get percentages

Posted: Fri Aug 06, 2004 12:28 pm
by hawleyjr
If you want you can also make MYSQL return the %:

Code: Select all

<?php
$qry = "SELECT DISTINCT('age') as dst_age, COUNT('age') as cnt,
((cnt*100)/$total_users)) as age_perct 
FROM MY_TABLE 
GROUP BY dst_age"; 
?>

Posted: Fri Aug 06, 2004 12:33 pm
by evilmonkey
I don't get it. The first query counts hopw many users there are, but the second one pulls just the distinct ages. How will I be able to tell how many users have that age? If 20 is pulled only once, how will I know?

Posted: Fri Aug 06, 2004 12:39 pm
by hawleyjr
hawleyjr wrote:

Code: Select all

<?php
$qry = "SELECT DISTINCT('age') as dst_age, COUNT('age') as cnt
FROM MY_TABLE
GROUP BY dst_age";
?>

Code: Select all

If table looks like this:

|	age  |
|	20	|
|	20	|
|	20	|
|	20	|
|	20	|
|	21	|
|	21	|
|	21	|
|	22	|
|	22	|
|	23	|
|	23	|
|	23	|
|	23	|
|	23	|
|	23	|

The query will return this:


|	dst_age |	cnt |
|	20		|	5	|
|	21		|	4	|
|	22		|	2	|
|	23		|	6	|

Posted: Fri Aug 06, 2004 12:41 pm
by evilmonkey
Oh, okay, now I get it. THANKS!!!! I'll test it out, let you know.

Thank you for the help!

Posted: Fri Aug 06, 2004 12:48 pm
by evilmonkey
Wait a second. How would I read the results of the query you posted above? Suppose I ran mysql_query() on that, normally, I'd do mysql_fetch_assoc and work with that. How would I do it in this case?

Posted: Fri Aug 06, 2004 12:50 pm
by hawleyjr
[php_man]mysql_query[/php_man]

Posted: Fri Aug 06, 2004 12:52 pm
by hawleyjr

Code: Select all

<?php
      $result = mysql_query($qry);

      while($row = mysql_fetch_array($result)) 
      { 
        $a_dst_age[] = $row['dst_age']; 
        $a_cnt[] = $row['cnt']; 
      }
?>

Posted: Fri Aug 06, 2004 12:56 pm
by hawleyjr
Take the returned values and go to town. Do the necessary math.

Code: Select all

<?php
$totalUsers = //Whatever the total is

foreach($a_dst_age as $ak => $val){
 
$perct = ($a_cnt*100)/$totalUsers;
 echo 'Perctentage of users age '.$val.' is '.$perct.'%<BR>';

}
?>

Posted: Fri Aug 06, 2004 1:03 pm
by evilmonkey
Hmmm...I'm still confused. I just tried out this query, and did a print_r($a_dst_age). It returned:

Code: Select all

Array
(
    &#1111;0] =&gt; age
)
$a_cnt did what it was suppossed to though. I'm confused about what information goes into which variables. Here is my whole function code, please tell me which variable is which. Thanks!

Code: Select all

<?php
function demographics ($demographic){
		$total = mysql_query("SELECT COUNT('age') as age_count FROM users");
		$total_users = mysql_num_rows($total);//total amount of users in the table
		$information = mysql_query("SELECT DISTINCT('age') as dst_age, COUNT('age')
	 	as cnt FROM `users` GROUP BY dst_age");

		while($row = mysql_fetch_array($information))
		{
			$a_dst_age[] = $row['dst_age'];
			$a_cnt[] = $row['cnt'];
		}
                                //math here
		}
}
?>
Sorry for bieng so thick, it's my first time working with a query like this. :?

Posted: Fri Aug 06, 2004 1:06 pm
by hawleyjr
Try removing the '' from the fields in the query. I think I put them in the there before but I wrote this in a UltraEdit...Not a MySql tool.

Code: Select all

<?php
$information = mysql_query("SELECT DISTINCT(age) as dst_age, COUNT(age) as cnt FROM `users` GROUP BY dst_age"); 
?>

Posted: Fri Aug 06, 2004 1:09 pm
by evilmonkey
Now print_r() is just not returning anything on either variables...

Posted: Fri Aug 06, 2004 1:10 pm
by evilmonkey
Never mind, it works.

Posted: Fri Aug 06, 2004 1:10 pm
by hawleyjr
show me your function with the print_r