Inconsistent Results with count(*)

[Anon]

Hello,

I have a small mySQL database that I use to keep track of registrations for a copyright seminar series. I want to create a means by which peoplc will be able to get information on the number of registrations for a particular seminar location.

For example, to find out how many people are signed up for the Los Angeles seminar, I can query the database to count the instances in which the value of a field named "USER1" is equal to "LA". When I'm at the mySQL command line and I enter:

select count(*) from main where USER1 = "LA";

I get the result:

count(*)

4

which is correct. However, when I try to do this in PHP, I get inconsistent (and incorrect) responses. The following code, for example, yeilds a count of "2":

<?php

$link_id=mysql_connect("host","user","password");
mysql_select_db("database",$link_id);

if (mysql_select_db("database",$link_id)) echo "Connected to the database...<br><br>";
else die ("Connection failed.");

//This is the section that reports the incorrect value:

$result_a = mysql_query("select count(*) from main where USER1 = 'LA'", $link_id);

if ($result_a) {
echo "The number of people signed up for the Los Angeles session is: ", $result_a, "<P>";
}

//End weird section

$result = mysql_query("select NAME, EMAIL, USER1 from main order by USER1", $link_id);

while ($query_data = mysql_fetch_row($result)) {
echo "Name: ", $query_data[0], "<BR>", "Seminar Location: ", $query_data[2], "<BR>", "and the email address is ", "<a href=mailto:$query_data[23]>", $query_data[1], "</a><p>";
}
?>

And one other strange thing happens: If I take that section of code between the remarks above, and I move that section to the line below where I have "$result = mysql_query("Select NAME, blablahblah", it returns a DIFFERENT, incorrect value, in this case "3".

Does anyone know what I'm doing wrong?

Email responses appreciated.

Thanks in advance,

Larry Trask
[e-mail]

[kparker]

Sure, it's pretty simple. <b>$result_a</b> is not the value of the count query. As far as mysql is concerned, 'select COUNT(...' is no different than any other query, and so $result_a is merely the result ID which you must use to retrieve the results:

&nbsp;&nbsp;&nbsp;list($count) = mysql_fetch_row( $result_id );

(The use of list() is because mysql_fetch_row() returns an array.)

There's nothing magic about the mysql command line utility, either--it's merely bundling the query plus the fetch-until-no-more-rows together for you.