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:
>
> $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}";
> }
> ?>
>
> 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
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:
$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}";
}
?>
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
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 "a"s 3 do the same for the number of "b"s 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 "b"s, 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 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
[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
