SELECT COUNT (*) FROM YOUR_TABLE;

AH --
I was right: I didn't know what to ask for -- SELECT was the magic word.

Thanks so much!
Clair

SELECT COUNT (*) FROM YOUR_TABLE;

It will run faster if you instead use:
SELECT COUNT (YOUR_FIELD) FROM YOUR_TABLE;

I know you said you didn't want data just the counts but if you want to see what the count is for.

Select your_field, count(your_field) from table group by your_field

life and dem -- Hi

And thanks for trying to help but no luck so far:

This is what I've used:

$query = 'SELECT COUNT * FROM blog_comments';
$ccount = mysql_query ($query);
print "count = $ccount";

I tried both with and without the parens around the *. I get nothing.
there is one comment for certain in the table. It comes up along with the blog it pertains to. What shows, where I eventrually want the number to show, is two single quotes where the number should be -- but of course there is no number coming back as the print statement shows.

I can't beleive this is so difficult - lol - got the whole bloody system working perfectly and can't do this!

Thanks much!

[edited by: Clair at 12:56 pm (utc) on May 15, 2007]

$query = 'SELECT COUNT * FROM blog_comments';
$ccount = mysql_query ($query);
print "count = $ccount";

I would try replacing the * with the field name

SELECT COUNT(field_name) FROM blog_comments

no space between the word count and the first paren... you for sure need the parens.... if that doesn't work try grouping on the field.

SELECT COUNT(field_name) FROM blog_comments group by field_name

You shouldn't need the group by..... what is the database you are using? which version? If it doesn't work try checking some documentation for that DB and version. This should work as advertised here.

Mole, Life and Dem --
Very early today I posted a report on my try with using the column name instead of the star. It's 12 hours later and I don't see it here, so here goes again.

These are the lines:

$query = 'SELECT COUNT(comment) FROM blog_comments';
$ccount = mysql_query ($query);
print "count = $ccount";

What prints is 'Resource id #5' and it is printed everywhere there should be a comment count -- which mostly would be 0, except for one blog.

This is driving me nuts. And there is one comment in there, as I said, which prints out attached to the blog it is supposed to be attached to. And the entire program works without a hitch. No problems at all with PHP, the HTML, the CSS, or the DB -- grrrrrrrrrr.

Clair

clair this sounds very whacky.

What is your DB type? And what is the version?

Example: MySql 4.02

The only other thing I can think of is if there are null values in that column.... for example if you had 1000 records and then you added this new column later...even if you add it as a text field, what will get put into all the values for the existing records when you do this is a null value. (for some DBs)

I have seen null values mess up counts... sometimes the db sees that the datatype is a varchar or some text field and so it will check for like values... and it will look for empty strings... it may not know what to do with the null values as it isn't an empty string or any type of string for that matter.

For debugging sake will you try this and tell me the output. Try executing this query to the DB directly, leave PHP out of it and let's see what the output is... I am very curious.

SELECT comments, count(comments) from blog_comments group by comments

SELECT COUNT(*) FROM table_name
returns the total number of rows in the table (the * must be in parens)

SELECT COUNT(field_name) FROM table_name
returns the number of rows where field_name does not contain NULL. This can take more time as every row must be examined for the presense of NULL.

Demaestro --

Here's what I used:

$comm = 'SELECT comments, count(comments) FROM blog_comments group by comments';

print "count = $comm";

And the output of the print statement is: (direct copy from scr.)

count = SELECT comments, count(comments) FROM blog_comments group by comments

The MySQL version is 4.0.18

I'm searching other forums -- got a lead from googling the resource msg. that said, MySQL queries return a resource and have to be run through another fuction to get the content. -- I'm working on that idea now. One suggestion for another person who has this same error is to use mysql_fetch_assoc(resource result).

Will post if I get lucky! Thanks!

FINALLY

$query = "SELECT COUNT(comment) FROM blog_comments";
$commcount = mysql_query($query);
$numrow_comm = mysql_num_rows($commcount);

This gave me a count of 1 which is what it should be.

Whew. What an ordeal. Now that it works, I can make it count the number of comments for each blog!

Thanks so much to everybody.

Clair

Nice!

That is strange that a select statement would return anything but a record set.... I don't think I like that

Glad you got it set straight and thanks for posting the solution.

Um, I don't know a whole lot about specific functions in PHP, but to me it sounds like mysql_num_rows returns the number of rows in the query result. A COUNT query will always return 1 row- the resulting count of the rows.

I suggest you do some more testing. Add some comments to the database so you will have a real COUNT of more than 1, then make sure you get the results you want.

^^^^^^

Great catch Life... I would also wonder about that. In fact it is highly probable that this could be what is happening.

Any guessing as to why a select statement wouldn't return a record set?

Not sure... I don't know the limitations of MySQL very well either. But I wonder if it might not allow a COUNT on certain data types.

For example, MS SQL does not allow COUNT on text (allows for many more characters than varchar allows) fields.

Okay Gang --
(First: note to Life -- it's okay, because it only returns 1 for the entry which actually has one comment. The rest of the entries have no comments, and a 0 is returned.)

Here's the full implementation with the bells and whistles. I do wish I could post the link to the page. It's really quite nice! lol
It counts comments for each blog entry and prints the number after each blog entry.

$numrows_comm = 0;
$commsql = "SELECT * FROM blog_comments WHERE blog_id = $bid";
$commresult = mysql_query($commsql);
$numrows_comm = mysql_num_rows($commresult);

if($numrows_comm == 0)
{
print "  (0 comments)  ";
}
else
{
$i = 0;
while($commrow = mysql_fetch_array($commresult))
{
$i++;
}
print "  ($i comments)   ";
}

Many many thanks for getting me over the hump.
Clair

[edited by: Clair at 9:16 pm (utc) on May 16, 2007]

Hey Clair, glad to see it's working.

I don't want to mess up a good thing, but I think this might be faster:

$numrows_comm = 0;
$commsql = "SELECT COUNT(*) FROM blog_comments WHERE blog_id = $bid";
$commresult = mysql_query($commsql);
$row = mysql_fetch_row($commresult);
$numrows_comm = $row[0];

It's almost the exact same thing, except that in this version you're only grabbing one result from the database (the count), putting that resulting row into the $row array, and then moving it to your $numrows_comm.

My guess is that this way is probably faster because you're not throwing every record from your database into your result this way, since all you need is the count and not every single record. In a small database this probably doesn't make much of a difference, but in large databases that result set can get absolutely huge.... better to just grab the count and work with that... if that's all you need.

Duskrider --
Thanks for that. I will try that substitution when I'm less tired (and therefore braver. That will be tomorrow a.m.) Fortunately my set up is such that I can muck about with the code and the public version is never touched.

But, still, I've had a good couple of days enjoying the fact that it works -- don't want to screw it up just yet! lol

Will report back!

Have a good weekend and thanks again.

Hi Clair.

To optimize your solution:

$commsql = "SELECT * FROM blog_comments WHERE blog_id = $bid";
$commresult = mysql_query($commsql);
$numrows_comm = mysql_num_rows($commresult);//here you've already got your desired number:

print "  ($numrows_comm comments)   ";

but the best solution (the fastest and the most optimized) is:

$commsql = "SELECT COUNT(blog_id) AS num FROM blog_comments WHERE blog_id = '$bid'";
$commresult = mysql_query($commsql);
$row = mysql_fetch_assoc($commresult); //there is only one row, we called it num
$numrows_comm = $row['num'];

print "  ($numrows_comm comments)   ";

Glad you solved it on your own!
Regards
Michal

PS. Just so you know, where the error was in the beginning.
This is your first try:

1. $query = 'SELECT COUNT * FROM blog_comments';
2. $ccount = mysql_query ($query);
3. print "count = $ccount";

In the first line you created a variable containing query.
In the second line you asked mysql with that query, and as a result you got an identificator (a pointer to the query result space).
In the third line you echoed that pointer to the browser.

Between 2. and 3. line you should perform two operations:
2a. From the query space choose one record.
there are few mysql functions you can use:

mysql_fetch_row - will fetch record as a numbered array (0 => '53', 1 => '20 Palm Street, London')... etc based on table order in mysql

mysql_fetch_assoc - will fetch record as an associated array ('id' => '53', 'address' => '20 Palm Street, London')

mysql_fetch_array - will return both above fields (0 => '53', 1 => '20 Palm Street, London', 'id' => '53', 'address' => '20 Palm Street, London')

so it should look eg.
$row = mysql_fetch_row($ccount);

all these above functions retrieve one record and move the pointer to the next query. It returns false, where there are no more queries to find.

2b. As $row is an array, then to retrieve one field you just do:
$number_of_blog_comments = $row[0];

Just a short note.
In php 0 - number and '0' - string are "almost" the same (you rarely have to care), as the language supports type translation.

Hope this doesn't mess you up :)

Have fun learning php and mySQL

Thanks mcibor -- very much. I will use this to learn from and experiment with. It does help to know what is happening.

mcibor -- et al.
It works! Thank you. At first it didn't but that was because I needed to access 'num' later on in my code (if num == 0 print 0, else print 'num')
Hmmmm, now that I write that, it looks like I can get rid of the if/else and just print 'num'. Back to the code!

And, as you say, the db is too small to calculate the speed benefit at this point, but, <g> one hopes that it will someday prove to be effective!

I learned a lot here, thanks very much!

Clair