kjtocool Posted December 18, 2007 Share Posted December 18, 2007 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? Quote Link to comment Share on other sites More sharing options...
kopytko Posted December 18, 2007 Share Posted December 18, 2007 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]; ?> Quote Link to comment Share on other sites More sharing options...
kjtocool Posted December 18, 2007 Author Share Posted December 18, 2007 Both return an empty value. Any other ideas? Quote Link to comment Share on other sites More sharing options...
p2grace Posted December 18, 2007 Share Posted December 18, 2007 $query = "SELECT ..."; $run = mysql_query($query); $num = mysql_num_rows($run); Quote Link to comment Share on other sites More sharing options...
kjtocool Posted December 18, 2007 Author Share Posted December 18, 2007 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! Quote Link to comment Share on other sites More sharing options...
p2grace Posted December 18, 2007 Share Posted December 18, 2007 Create two queries, one for total number, the other with the limit. Quote Link to comment Share on other sites More sharing options...
The Little Guy Posted December 18, 2007 Share Posted December 18, 2007 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. Quote Link to comment Share on other sites More sharing options...
emehrkay Posted December 18, 2007 Share Posted December 18, 2007 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 Quote Link to comment Share on other sites More sharing options...
p2grace Posted December 18, 2007 Share Posted December 18, 2007 Nice, I didn't know that trick Quote Link to comment Share on other sites More sharing options...
kjtocool Posted December 18, 2007 Author Share Posted December 18, 2007 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 Quote Link to comment Share on other sites More sharing options...
kjtocool Posted December 18, 2007 Author Share Posted December 18, 2007 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 Quote Link to comment Share on other sites More sharing options...
The Little Guy Posted December 18, 2007 Share Posted December 18, 2007 <?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. Quote Link to comment Share on other sites More sharing options...
kjtocool Posted December 18, 2007 Author Share Posted December 18, 2007 <?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. Quote Link to comment Share on other sites More sharing options...
The Little Guy Posted December 18, 2007 Share Posted December 18, 2007 <?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; ?> Quote Link to comment Share on other sites More sharing options...
kjtocool Posted December 18, 2007 Author Share Posted December 18, 2007 <?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. Quote Link to comment Share on other sites More sharing options...
emehrkay Posted December 18, 2007 Share Posted December 18, 2007 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] Quote Link to comment Share on other sites More sharing options...
The Little Guy Posted December 18, 2007 Share Posted December 18, 2007 <?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? Quote Link to comment Share on other sites More sharing options...
emehrkay Posted December 18, 2007 Share Posted December 18, 2007 <?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 Quote Link to comment Share on other sites More sharing options...
kjtocool Posted December 18, 2007 Author Share Posted December 18, 2007 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. Quote Link to comment Share on other sites More sharing options...
emehrkay Posted December 18, 2007 Share Posted December 18, 2007 "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"; Quote Link to comment Share on other sites More sharing options...
The Little Guy Posted December 18, 2007 Share Posted December 18, 2007 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; ?> Quote Link to comment Share on other sites More sharing options...
kjtocool Posted December 18, 2007 Author Share Posted December 18, 2007 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. Quote Link to comment Share on other sites More sharing options...
kjtocool Posted December 18, 2007 Author Share Posted December 18, 2007 "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. Quote Link to comment Share on other sites More sharing options...
The Little Guy Posted December 18, 2007 Share Posted December 18, 2007 Change "ORDER BY" to "GROUP BY" or this may work too "ORDER BY comment_ID GROUP BY user_ID LIMIT 0,9" Quote Link to comment Share on other sites More sharing options...
emehrkay Posted December 18, 2007 Share Posted December 18, 2007 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 Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.