-1

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);
Improve this question
0

2 Answers 2

Reset to default
3

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])
Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

-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().

Improve this answer

4 Comments

How can one get multiple results from a SELECT COUNT query?
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.
I am downvoting for manual error checking. Please, let's start teaching people how to enable automatic error reporting.
@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 php.net/manual/en/mysqli-result.fetch-assoc.php . Do as you please.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.