PHP/MySQL Count() Issue

Question

I am trying to create a class registration system for a client that utilizes PHP and MySQL. I have the database and table all set up and that part works just fine, however, the client has requested that upon registration, if there are 3 or fewer students enrolled to warn that the class may not run.

I'm trying to use the count() function as well as passing a dynamic variable from a cookie, set from the registration PHP script. However, I've hit a roadblock. I can't seem to get the count() function to actually count the rows. My select statement is below. Any help would be greatly appreciated.

$class = $_COOKIE["class"];

$min_check = "SELECT class_list, COUNT(class_list) as count 
              FROM T_Student WHERE class_list = '$class' 
              GROUP BY class_list 
              HAVING count < 20";
$result = mysql_query($min_check);
$count = mysql_num_rows($result);

if ($count < 4)
{
  echo "IF THERE ARE 3 OR FEWER PEOPLE SIGNED UP FOR THIS CLASS, IT MAY NOT RUN.\n";
  echo "THERE ARE CURRENTLY " . $count . " PEOPLE SIGNED UP.\n";
}
else if ($count > 4)
{
  echo "There are currently " . $count . " people signed up for this class.";
}
?>

You don't need to use both the MySQL COUNT() procedure and PHP's mysql_num_rows(); use one or the other. Also, what does echo $count give you? — Bojangles
– Bojangles, Commented Aug 28, 2011 at 22:03
You should be a little more careful with naming your variables. You do three different things with variables and field-names you all call count. Use something more descriptive like count_classes or $count_rows to avoid being dazzled. — ty812
– ty812, Commented Aug 28, 2011 at 22:03

OMG Ponies · Accepted Answer · 2011-08-29 00:09:40Z

5

Your SQL query is returning a list of the class_list values, along with a count of each specific instance, where there are less than 20 people registered.

$count = mysql_num_rows($result);

...is getting the number of records returned in the resultset, not the alias count value, which is why you aren't seeing the output you expect. You need to read into your resultset to get the value:

while ($row = mysql_fetch_assoc($result)) {
  $count = $row['count'];

  if($count < 4) { ... }
}

edited Aug 29, 2011 at 0:09

answered Aug 28, 2011 at 22:03

OMG Ponies

334k85 gold badges536 silver badges508 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

jbrown574 Over a year ago

Thanks all for the input. I knew it was something pretty easy that I needed to add, just been looking at it too long to figure it out.

Brian Glaz · Accepted Answer · 2011-08-28 22:04:18Z

0

The count that you want is returned in the row of the query. the mysql_num_rows will count the rows returned, which is not what you want. Use this instead.

$result = mysql_query($min_check);
$count = mysql_fetch_row($result);
$count = $count[0];

answered Aug 28, 2011 at 22:04

Brian Glaz

15.7k4 gold badges40 silver badges55 bronze badges

Comments

feeela · Accepted Answer · 2011-08-28 22:07:37Z

0

On a first glance, the HAVING count < 20 is unnecessary.

You use the MySQL-count-function, but never retrieve it's value!? Use:

$firstRow = mysql_fetch_row($result);
$count = $firstRow[1]; // 1 indicates the second column (0 being the first)

answered Aug 28, 2011 at 22:07

feeela

30k7 gold badges61 silver badges73 bronze badges

Comments

Johan · Accepted Answer · 2011-08-28 22:08:24Z

0

I don't recommend using known MySQL identifiers like count. It's confusing.

$class = mysql_real_escape_string($_COOKIE["class"]);

$min_check = "SELECT class_list, COUNT(class_list) as mycount 
          FROM T_Student WHERE class_list = '$class' 
          GROUP BY class_list 
          HAVING mycount < 20";

Don't forget to escape the contents of that cookie!

~~The error is that count is a reserved word. You need to either surround it in backticks `count` or even better, use a different moniker.~~ It's not an error per se, but it's just too confusing.

Next up, you are not actually retrieving the mycount result from the database. I suggest using code something like this:

$result = mysql_query($min_check);
while( $row = mysql_fetch_assoc($result) ) {
    $people_count = $row['mycount'];
    if ($people_count < 4) { echo "this" }
    else { echo "that" }
}

edited Aug 28, 2011 at 22:08

answered Aug 28, 2011 at 22:03

Johan

77.4k28 gold badges204 silver badges346 bronze badges

2 Comments

ty812 Over a year ago

Factually incorrect. "Names of built-in functions are permitted as identifiers but may require care to be used as such. For example, COUNT is acceptable as a column name. " (from the MYSQL documentation, chapter 8.3

Michael Mior Over a year ago

Count can be used unquoted like this. Try it and see. Very true about escaping though :)

Collectives™ on Stack Overflow

PHP/MySQL Count() Issue

4 Answers 4

1 Comment

Comments

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

1 Comment

Comments

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related