SQL count all doesn't execute in php

Question

if($sql = $db->query("Count (*) FROM post_items")){
        echo mysqli_num_rows($sql);
}

what's wrong with my code? is this the correct way to echo the total of row in a table?

Missing `SELECT` in your query try this `SELECT Count(*) FROM post_items` — M Khalid Junaid, Feb 19 '14 at 06:54
Does this answer your question? [MySQLi count(\*) always returns 1](https://stackoverflow.com/questions/3613074/mysqli-count-always-returns-1) — Dharman, Mar 06 '20 at 19:47

score 3 · Accepted Answer · edited Mar 06 '20 at 19:46

3

Your query should be

select count(*) FROM post_items

but echoing

mysqli_num_rows($sql);

will always give 1 as the ans, because count function returns only one row.

Rather fetch the details and show the count

$row = $sql->fetch_row();
echo $row[0];

edited Mar 06 '20 at 19:46

Dharman

30,962
25
85
135

answered Feb 19 '14 at 07:00

Deepika Janiyani

1,487
9
17

then that's not what I want. I need to know the total row in the table. like 50 customers has bought an item. – user3318525 Feb 19 '14 at 07:02
echoing the count will do that for you, `mysqli_num_rows` returns no of rows not the data of rows – Deepika Janiyani Feb 19 '14 at 07:03
to get data you will have to fetch the result and then display it, check updated ans – Deepika Janiyani Feb 19 '14 at 07:04

score 2 · Answer 2 · answered Feb 19 '14 at 07:00

No it is not; you will always get a return value of 1.

Why? Because you are in essence double-counting. The query executes a COUNT aggregate on the table returning a single row with the counted number. mysqli_num_rows is then counting the number of rows in this result set - the single row - and returns 1.

Try, the following line instead, which should fetch the first (only) column returned of the first (only) row in the result set.

echo $sql->fetch_row()[0]

Note you're also missing a SELECT keyword in your SQL statement.

score -3 · Answer 3 · answered Feb 19 '14 at 06:57

-3

It should be

if($sql = $db->query("select count(*) FROM post_items")){
        echo mysqli_num_rows($sql);
}

answered Feb 19 '14 at 06:57

Akhil Sidharth

746
1
6
16

SQL count all doesn't execute in php

3 Answers3