RE: [PHP] records in db
// untested
$data = mysql_fetch_array(mysql_query(SELECT count(answer_col) as total
FROM answers_table WHERE answer_col='{$answer}'));
echo $data[total];
Put this in a while/foreach statement
Regards
Chris Kay
-Original Message-
From: Justin French [mailto:[EMAIL PROTECTED]]
Sent: Sunday, 11 August 2002 12:09 PM
To: Tyler Durdin; [EMAIL PROTECTED]
Subject: Re: [PHP] records in db
on 11/08/02 3:52 AM, Tyler Durdin ([EMAIL PROTECTED]) wrote:
If I have a field in my DB that can have 4 different
answers, lets say
a,b,c and d. How can I count the number of each in that
field. So if
there are 4 a's 1 b 0 c's and 12 d's how can I get php to
count this?
I'm pretty certain there's a way to do this with just one
MySQL query, but here's a PHP version that does 4 queries:
? // UNTESTED CODE
$answers = array('a','b','c','d');
foreach($answers as $key = $answer)
{
$sql = SELECT * FROM answers_table WHERE answer_col='{$answer}';
$result = mysql_query($sql);
if($result)
{
$count = mysql_num_rows($result);
}
else
{
$count = 0;
}
echo {$count} people selected answer {$answer}br /;
}
?
Should print out something like:
4 people selected answer a
1 people selected answer b
0 people selected answer c
12 people selected answer d
hack it to suit your needs,
Justin
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
[PHP] records in db
If I have a field in my DB that can have 4 different answers, lets say a,b,c and d. How can I count the number of each in that field. So if there are 4 a's 1 b 0 c's and 12 d's how can I get php to count this? _ MSN Photos is the easiest way to share and print your photos: http://photos.msn.com/support/worldwide.aspx -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] records in db
On Sat, Aug 10, 2002 at 05:52:42PM +, Tyler Durdin wrote: If I have a field in my DB that can have 4 different answers, lets say a,b,c and d. How can I count the number of each in that field. So if there are 4 a's 1 b 0 c's and 12 d's how can I get php to count this? Us the database to do that: SELECT FieldName, count(*) FROM TableName GROUP BY FieldName --Dan -- PHP classes that make web design easier SQL Solution | Layout Solution | Form Solution sqlsolution.info | layoutsolution.info | formsolution.info T H E A N A L Y S I S A N D S O L U T I O N S C O M P A N Y 4015 7 Av #4AJ, Brooklyn NY v: 718-854-0335 f: 718-854-0409 -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] records in db
Tyler, If I have a field in my DB that can have 4 different answers, lets say a,b,c and d. How can I count the number of each in that field. So if there are 4 a's 1 b 0 c's and 12 d's how can I get php to count this? You must combine IF() with SUM(). Break it down into smaller problems: 1 getting the data out of the tbl and grab all of the answer rows 2 'count' the number of as 3 do the same for the number of bs The first is trivial, but let's limit our attention to question 1 until we figure out how to word the query: SELECT questionNr FROM tblNm WHERE questionNr = 1 AND answer = 'a' Obviously this won't satisfy you - you don't want line after line of data, and it only works if the chosen answer is a and you read off the number of rows affected. What happens if the answer is b. We'd have to execute the query all over again, but substituting b for a. Is there another way? Yes, there is an IF() in SQL (manual: 6.3 Functions for Use in SELECT and WHERE Clauses). Let's try listing the question number and noting if the answer is a or b or...: SELECT questionNr, IF( answer = 'a', TRUE, FALSE ) AS Answer-A, IF( answer = 'b', TRUE, FALSE ) AS Answer-B, ... FROM tblNm WHERE questionNr = 1 Now we have a list of all of the answers to question 1, but instead of only a answers, we have a column of TRUEs and FALSEs for the a answers, and beside that a column of TRUEs and FALSEs for the bs, through c and d. So beside the questionNr we have four columns telling us which data is relevant to count - if we simply count the TRUEs. So we've saved the repetitious executions, but the machine no longer does any useful counting for us. Substitute 1 for TRUE and 0 for FALSE, and we can have SQL do that last calc for us, ie SUM all of those TRUE values: SELECT questionNr, SUM( IF( answer = 'a', 1, 0 ) ) AS Answer-A, SUM( IF( answer = 'b', 1, 0 ) ) AS Answer-B, ... FROM tblNm WHERE questionNr = 1 Finally, if you get rid of the WHERE clause, and replace it with GROUP BY questionNr, it should work for every question in the test/survey. Regards, =dn -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] records in db
on 11/08/02 3:52 AM, Tyler Durdin ([EMAIL PROTECTED]) wrote:
If I have a field in my DB that can have 4 different answers, lets say a,b,c
and d. How can I count the number of each in that field. So if there are 4
a's 1 b 0 c's and 12 d's how can I get php to count this?
I'm pretty certain there's a way to do this with just one MySQL query, but
here's a PHP version that does 4 queries:
? // UNTESTED CODE
$answers = array('a','b','c','d');
foreach($answers as $key = $answer)
{
$sql = SELECT * FROM answers_table WHERE answer_col='{$answer}';
$result = mysql_query($sql);
if($result)
{
$count = mysql_num_rows($result);
}
else
{
$count = 0;
}
echo {$count} people selected answer {$answer}br /;
}
?
Should print out something like:
4 people selected answer a
1 people selected answer b
0 people selected answer c
12 people selected answer d
hack it to suit your needs,
Justin
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
