Counting Specfic Results in MySQL Table

heres what i need to do now

[h3ktlk]

I need to query a column in a database.... then have it look at that column and if say $string = "specific data" it will take a $number string and add 1 to it. It needs to go through all the records and do this.

Is this possible?!?!?

[barns101]

Something like this?

$result = mysql_query('SELECT `column` FROM `table`');
while($row = mysql_fetch_array($result));
{
if($row[column] == 'specific data') $number++;
}

[ytswy]

Or you could do something like this:

$result = mysql_query('SELECT SUM(IF(column = "' . $specific_data . '",1,0)) AS total FROM table');
$row = mysql_fetch_array($result);
$number = $row['total'];

[edited by: ytswy at 1:24 pm (utc) on May 24, 2007]

[h3ktlk]

This is what i have but does not seem to be wroking as it still displays as if under 13 but there are 15+ records with that. Thanks!

$query = mysql_query("SELECT * FROM Eventssession1 WHERE EVENTID= '1004' LIMIT 1")or die(mysql_error());
$row = mysql_fetch_object($query);
$spaceq = mysql_query("SELECT SUM(IF(Session1_9 = 'Art in Narration - 9:00'),1,0) AS total FROM Students");
$space = mysql_fetch_array($spaceq);
$number = $space['total'];
if ($number < '13')
{
echo $row->EventName;}
else
{
echo "Class Full";
}

[ytswy]

I'd messed up my syntax, try changing:

$spaceq = mysql_query("SELECT SUM(IF(Session1_9 = 'Art in Narration - 9:00'),1,0) AS total FROM Students");

to

$spaceq = mysql_query("SELECT SUM(IF(Session1_9 = 'Art in Narration - 9:00',1,0)) AS total FROM Students");

[ytswy]

oh and $number will be an integer, so change:

if ($number < '13')

to

if ($number < 13)

[h3ktlk]

Thank You! I believe that is working now!

[eelixduppy]

Why don't you just use COUNT [dev.mysql.com] in your query? Something like this maybe:

$query = "SELECT COUNT(`Session1_9`) as `Num_students` FROM Students WHERE `Session1_9` = 'Art in Narration - 9:00'";

:)

[ytswy]

*sigh* and I was so pleased with myself when I discovered the SUM(IF,1,0) technique a little while back... now they tell me there's a COUNT function.

I really need to spend a little longer with the manual..

[h3ktlk]

Is it better to use count instead?

[h3ktlk]

Is there a way where inside of this

$spaceq = mysql_query("SELECT SUM(IF(Session1_9 = 'Wild About the Zoo - 9:00',1,0)) AS total FROM Students");

i can substitue 'Wild About the Zoo - 9:00' with a variable assigned to that field?

[eelixduppy]

>> Is it better to use count instead?

I don't know what kind of speed advantages you'd get using COUNT instead of the other method, however it should be a little faster. To count is, after all, what the function is meant to do :) Try the query I showed you to see if it improves the time. (It shows the time if you run the query in the command line).

[ytswy]

$spaceq = mysql_query("SELECT SUM(IF(Session1_9 = '" . $your_variable . "',1,0)) AS total FROM Students");