How to return integer of COUNT mysql query result in PHP

Question

I'm just putting a simple PHP program together that simply counts the number of total rows in my database and returns the result as a JSON object when it recieves a get request from my frontend.

This should all be super simple but for the life of me I can't figure out how to retrieve the count result in a usable format. All I get from this is "null"

any ideas?

$con = mysqli_connect($servername, $username, $password, $dbname);
if (!$con) {
    die('Could not connect: ' . mysqli_error($con));
}

$query="SELECT COUNT(*) FROM database";
$result = mysqli_query($con,$query);
$count = mysqli_fetch_assoc($result);
echo json_encode($count);

score 3 · Answer 1 · edited Nov 20 '20 at 18:40

You can use COUNT as to avoid a very long key.

Here's a code example

$sql = "SELECT COUNT(column) as count FROM mytable";
$stmt = mysqli_stmt_init($db);
mysqli_stmt_prepare($stmt, $sql);

mysqli_stmt_execute($stmt);
$result = mysqli_stmt_get_result($stmt);
$count = mysqli_fetch_assoc($result)['count'];

echo $count;

To display data in JSON you can try this:

echo json_encode(['count' => $count])

Telmo Dias · Answer 2 · 2020-11-20T17:48:33.043

-1

That by itself will not give you the count. I would suggest that you start using MySQL object methods instead of functions.

You should also use the "as" satement as way to give a name to the column that has the count value; in my example that column will be known as C .

$mysqli = new mysqli("localhost", "my_user", "my_password", "world");

if ($mysqli->connect_errno) {
    printf("Connect failed: %s\n", $mysqli->connect_error);
    exit();
}

$query = "SELECT COUNT(*) as C FROM database";

if ($result = $mysqli->query($query)) {
    /* fetch associative array */
    $row = $result->fetch_assoc();
    echo $row["C"];
}

EDIT: In the specific case of SELECT COUNT you'll only get 1 row, so for this specific example you could do just fetch_assoc().

edited Nov 20 '20 at 17:48

answered Nov 20 '20 at 17:11

Telmo Dias

3,938
2
36
48

1

How can one get multiple results from a `SELECT COUNT` query? – El_Vanja Nov 20 '20 at 17:25
that's not what I meant, in this case specifically you'll get only 1 row as a result, but in general, when you execute a query, you don't know how many rows you'll get. I'll update my text so it's a bit more clear. – Telmo Dias Nov 20 '20 at 17:44
I am downvoting for manual error checking. Please, let's start teaching people how to enable automatic error reporting. – Dharman Nov 20 '20 at 18:39
@Dharman that is not the purpose of this post and this is exactly as it is presented on PHP documentation as you can verify on https://www.php.net/manual/en/mysqli-result.fetch-assoc.php . Do as you please. – Telmo Dias Nov 20 '20 at 23:49

How to return integer of COUNT mysql query result in PHP

2 Answers2