select count error mysqli_num_rows() expects parameter 1 to be mysqli_result, boolean given

Question

i am trying to count "YES" votes from column vote for each projects grouped by project_id. vote can be "YES", "NO" and null value which is a boolean value. i used "like" operator. but it is throwing error this is my table

here is the code

$sql = "SELECT COUNT(vote) as count,project_id FROM tbl_vote group by   project_id where vote like '_Y'";
$result = $conn->query($sql);  if (mysqli_num_rows($result) > 0) {

while($row = mysqli_fetch_assoc($result)) {
    echo "count: " . $row["count"]. " project_id:".$row["project_id"]."    <br>";
  }
} 
else {
echo "0 results";
}

error is in if condition

Warning: mysqli_num_rows() expects parameter 1 to be mysqli_result, boolean given in C:\xampp\htdocs\selection\compute_sums.php on line 18``

That isn't how SELECT/GROUP BY/WHERE works. Please read the documentation/manual on this. https://dev.mysql.com/doc/refman/8.0/en/select.html and its syntax. — Funk Forty Niner, Aug 28 '18 at 11:04
code works if i do not include "like" clause and counts votes for each project_id — Prithvi Tuladhar, Aug 28 '18 at 11:13
thank u. i was looking for specific answer rather the order. got it — Prithvi Tuladhar, Aug 28 '18 at 11:19
is it possible to save the results into another table? i need to subtract count of "yes" votes with total vote for each projects and save into another table. — Prithvi Tuladhar, Aug 29 '18 at 03:11

score 0 · Answer 1 · answered Aug 28 '18 at 11:07

You have place group by clause before where clause

$sql = "SELECT COUNT(vote) as count,project_id FROM tbl_vote where vote = 'Yes' group by   project_id";

The ordering of SQL Clauses is as follows:

SELECT
    [ALL | DISTINCT | DISTINCTROW ]
      [HIGH_PRIORITY]
      [STRAIGHT_JOIN]
      [SQL_SMALL_RESULT] [SQL_BIG_RESULT] [SQL_BUFFER_RESULT]
      SQL_NO_CACHE [SQL_CALC_FOUND_ROWS]
    select_expr [, select_expr ...]
    [FROM table_references
      [PARTITION partition_list]
    [WHERE where_condition]
    [GROUP BY {col_name | expr | position}, ... [WITH ROLLUP]]
    [HAVING where_condition]
    [WINDOW window_name AS (window_spec)
        [, window_name AS (window_spec)] ...]
    [ORDER BY {col_name | expr | position}
      [ASC | DESC], ... [WITH ROLLUP]]
    [LIMIT {[offset,] row_count | row_count OFFSET offset}]
    [INTO OUTFILE 'file_name'
        [CHARACTER SET charset_name]
        export_options
      | INTO DUMPFILE 'file_name'
      | INTO var_name [, var_name]]
    [FOR {UPDATE | SHARE} [OF tbl_name [, tbl_name] ...] [NOWAIT | SKIP LOCKED] 
      | LOCK IN SHARE MODE]]

Please refer: https://dev.mysql.com/doc/refman/8.0/en/select.html

select count error mysqli_num_rows() expects parameter 1 to be mysqli_result, boolean given

1 Answers1