[SOLVED] How do you get the result of a count?

[kjtocool]

I want to get the result from a count query.

 

I tried the following:

 

<?php
...code
$query = "SELECT COUNT(*) FROM Article_Comments WHERE article_ID = $article_ID";
$result = mysqli_query($databaseConnect, $query);
$row = mysqli_fetch_assoc($result);
$num_comments = $row['Count(*)'];
echo $num_comments;
...code
?>

 

But it doesn't work.

 

Is there another way?

[kopytko]

Try this:

<?php
$query = "SELECT COUNT(*) as ntotal FROM Article_Comments WHERE article_ID = $article_ID";
$result = mysqli_query($databaseConnect, $query);
$row = mysqli_fetch_assoc($result);
$num_comments = $row['ntotal'];
?>

 

or

 

<?php
$query = "SELECT COUNT(*) as ntotal FROM Article_Comments WHERE article_ID = $article_ID";
$result = mysqli_query($databaseConnect, $query);
$row = mysqli_fetch_row($result);
$num_comments = $row[0];
?>

[kjtocool]

Both return an empty value.

 

Any other ideas?

[p2grace]

$query = "SELECT ...";
$run = mysql_query($query);
$num = mysql_num_rows($run);

[kjtocool]

Well, yes, that's a work around if you ignore Count(*).

 

Originally I was using:

 

<?php
$query = "SELECT user_ID, comment FROM Article_Comments WHERE article_ID = $article_ID";
$result = mysqli_query($databaseConnect, $query);
$row = mysqli_fetch_assoc($result);

$num_comments = mysqli_num_rows($result);
?>

 

But Now I want to change that query, and limit the results, so it will no longer give the correct number:

 

<?php $query = "SELECT user_ID, comment FROM Article_Comments WHERE article_ID = $article_ID ORDER BY comment_ID LIMIT 0,9"; ?>

 

Thus now the answer would never be greater than 9, even if there may be more rows that match the statement without the limit.

 

So rather than run the above query once just to get the number of rows returned and then again with a limit, I wanted to do a count, which would be quicker.  But I can't figure out how to get the result!

[p2grace]

Create two queries, one for total number, the other with the limit.

[The Little Guy]

Run this in PHP MyAdmin... see what it returns

 

SELECT COUNT(*) FROM Article_Comments WHERE article_ID = $article_ID

 

You will then have to replace $article_ID

with your own value.

[emehrkay]

you shouldnt need two queries

 

"SELECT COUNT(field) AS field FROM table LIMIT 0,9"

 

field will be the total of what is in the table

[p2grace]

Nice, I didn't know that trick 

[kjtocool]

you shouldnt need two queries

 

"SELECT COUNT(field) AS field FROM table LIMIT 0,9"

 

field will be the total of what is in the table

 

I tried:

 

SELECT COUNT(user_ID) AS count, user_ID, comment FROM Article_Comments WHERE article_ID = $article_ID ORDER BY comment_ID LIMIT 0,9

 

And got this:

 

#1140 - Mixing of GROUP columns (MIN(),MAX(),COUNT(),...) with no GROUP columns is illegal if there is no GROUP BY clause 

[kjtocool]

Run this in PHP MyAdmin... see what it returns

 

SELECT COUNT(*) FROM Article_Comments WHERE article_ID = $article_ID

 

You will then have to replace $article_ID

with your own value.

 

Returns:

 

COUNT(*)

3

 

[The Little Guy]

<?php
...code
$query = "SELECT COUNT(*) FROM Article_Comments WHERE article_ID = $article_ID";
$result = mysqli_query($databaseConnect, $query);
$row = mysqli_fetch_assoc($result);
$num_comments = $row['COUNT(*)'];
echo $num_comments;
...code
?>

 

 

Try that... Remember that Mysql is case sensitive.

[kjtocool]

<?php
...code
$query = "SELECT COUNT(*) FROM Article_Comments WHERE article_ID = $article_ID";
$result = mysqli_query($databaseConnect, $query);
$row = mysqli_fetch_assoc($result);
$num_comments = $row['COUNT(*)'];
echo $num_comments;
...code
?>

 

 

Try that... Remember that Mysql is case sensitive.

 

Oh boy, don't I feel silly.  That's the problem.  Thanks!

 

I would still be very interested to see if I can get emehrkay's method working, as that saves me a query, which is the ultimate goal.

[The Little Guy]

<?php
$query = "SELECT SQL_CALC_FOUND_ROWS * FROM Article_Comments WHERE article_ID = '$article_ID' LIMIT 0,9";
$sql = mysqli_query($databaseConnect,$query)or die(mysql_error($databaseConnect)); 
$result_count = mysqli_query($databaseConnect,"SELECT FOUND_ROWS()")or die(mysqli_error($databaseConnect));
$total = mysqli_fetch_array($result_count);
$totalrows = $total[0];


echo $totalrows;
?>

[kjtocool]

<?php
$query = "SELECT SQL_CALC_FOUND_ROWS * FROM Article_Comments WHERE article_ID = '$article_ID' LIMIT 0,9";
$sql = mysql_query($query)or die(mysql_error()); 
$result_count = mysql_query("SELECT FOUND_ROWS()")or die(mysql_error());
$total = mysql_fetch_array($result_count);
$totalrows = $total[0];


echo $totalrows;
?>

 

I thought of that, but because SQL_CALC_FOUND_ROWS doesn't store the value, it will lead to incorrect results.  My website has too high of traffic to use that method.

 

The problem is that you need to run a second query to get the result, and if someone else loads a separate page, or article at the same time, since it too would use the SQL_CALC function, the results can be all jumbled.

[emehrkay]

you shouldnt need two queries

 

"SELECT COUNT(field) AS field FROM table LIMIT 0,9"

 

field will be the total of what is in the table

 

I tried:

 

SELECT COUNT(user_ID) AS count, user_ID, comment FROM Article_Comments WHERE article_ID = $article_ID ORDER BY comment_ID LIMIT 0,9

 

And got this:

 

#1140 - Mixing of GROUP columns (MIN(),MAX(),COUNT(),...) with no GROUP columns is illegal if there is no GROUP BY clause 

 

 

it looks like if you select more than one column while using the count, you have to use "group by". and using group by, count will only count what is in the group. does that make sense?

 

if you want to save a php run query, you could use a sub query. I am just not sure if there is another way (my sql skillz lack)

 

"SELECT *, (SELECT COUNT(field) FROM table) AS total_count FROM table WHERE ..."
[code]

[/code]

[The Little Guy]

<?php
...code
$query = "SELECT COUNT(*) FROM Article_Comments WHERE article_ID = $article_ID";
$result = mysqli_query($databaseConnect, $query);
$row = mysqli_fetch_assoc($result);
$num_comments = $row['COUNT(*)'];
echo $num_comments;
...code
?>

 

how does this not save you a query?

[emehrkay]

<?php
...code
$query = "SELECT COUNT(*) FROM Article_Comments WHERE article_ID = $article_ID";
$result = mysqli_query($databaseConnect, $query);
$row = mysqli_fetch_assoc($result);
$num_comments = $row['COUNT(*)'];
echo $num_comments;
...code
?>

 

 

how does this not save you a query?

 

read my reply above yours

[kjtocool]

Becauase I need to do:

 

<?php
...code
$query = "SELECT COUNT(*) FROM Article_Comments WHERE article_ID = $article_ID";
$result = mysqli_query($databaseConnect, $query);
$row = mysqli_fetch_assoc($result);
$num_comments = $row['COUNT(*)'];

$query = "SELECT user_ID, comment FROM Article_Comments WHERE article_ID = $article_ID ORDER BY comment_ID LIMIT 0,9";
$result = mysqli_query($databaseConnect, $query);
$row = mysqli_fetch_assoc($result);

do something with result

...code
?>

 

Basically, I need to:

 

A) Get the total number of comments.

B) Get the first 9 comments user_ID and comment

 

I don't know how to go about getting emehrkay's method to work.

[emehrkay]

"SELECT (SELECT COUNT(*) FROM Article_Comments WHERE article_ID = $article_ID) as total_count, user_ID, comment FROM Article_Comments WHERE article_ID = $article_ID ORDER BY comment_ID LIMIT 0,9";

[The Little Guy]

This could possibly work.

 

<?php
$query = "SELECT COUNT(*) as numRows, user_ID, comment 
FROM Article_Comments 
WHERE article_ID = '$article_ID'
ORDER BY comment_ID LIMIT 0,9";
$result = mysqli_query($databaseConnect, $query);
$row = mysqli_fetch_assoc($result);
$num_comments = $row['numRows'];

echo $num_comments;
?>

[kjtocool]

This could possibly work.

 

<?php
$query = "SELECT COUNT(*) as numRows, user_ID, comment 
FROM Article_Comments 
WHERE article_ID = '$article_ID'
ORDER BY comment_ID LIMIT 0,9";
$result = mysqli_query($databaseConnect, $query);
$row = mysqli_fetch_assoc($result);
$num_comments = $row['numRows'];

echo $num_comments;
?>

 

This gives the mysql error I listed above, due to the grouping issue.

[kjtocool]

"SELECT (SELECT COUNT(*) FROM Article_Comments WHERE article_ID = $article_ID) as total_count, user_ID, comment FROM Article_Comments WHERE article_ID = $article_ID ORDER BY comment_ID LIMIT 0,9";

 

This works like a charm, you sir, are a genius who just saved me a query.

 

Muchos Gracias!

 

Thanks to both you and The Little Guy, you both helped a lot.

[The Little Guy]

Change "ORDER BY"

 

to "GROUP BY"

 

or this may work too

 

 

"ORDER BY comment_ID GROUP BY user_ID LIMIT 0,9"

[emehrkay]

Change "ORDER BY"

 

to "GROUP BY"

 

or this may work too

 

 

"ORDER BY comment_ID GROUP BY user_ID LIMIT 0,9"

 

not it wont. it will count what is in the group. if user_id is uniuqe, there will be only one in the group, so the count will be 1