Jump to content

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


kjtocool

Recommended Posts

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?

Link to comment
Share on other sites

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];
?>

Link to comment
Share on other sites

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!

Link to comment
Share on other sites

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 

Link to comment
Share on other sites

<?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.

Link to comment
Share on other sites

<?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;
?>

Link to comment
Share on other sites

<?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.

Link to comment
Share on other sites

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]

Link to comment
Share on other sites

<?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

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

"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.

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.