Returning a value 0 from a database row in PHP/MySQL after a query

Question

I am trying to create a page where I can get the record values of a Database. My query is like this:

So I need to get the number of the values 1 on status (as count) from the tableexample if they exist and count them, if they do not exist, I need to get the value as 0 from it. To get the values I use this code and it works great, but I cannot seem to have the value return 0 on my PHP code if no results are found. I am a bit lost.

$user = $_SESSION["user"];
$systems = mysqli_connect("localhost", "root", "", "testdatabase");
$query = "SELECT SUM(count) AS value_sum FROM tableexample WHERE user = '$user' AND status = '1'";
$request = mysqli_query($systems, $query);
if (mysqli_num_rows($request) > 0) {
    while ($row = mysqli_fetch_array($request)) {
        echo '' . $row["value_sum"] . '';
    }
} else {
    echo '0';
}

So this code gets the values without any issue, but when I place "else" it should give me value 0 but does not give me anything.

Any idea on how I can solve this without changing my code so far as much?

Answer 1

Using PDO would it be something like this:

$dsn = "mysql:host=localhost;dbname=myDatabase;charset=utf8mb4";

$pdo = new PDO($dsn, "username", "password", [
    PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
]);

$stmt = $pdo->prepare('
    SELECT SUM(count)
    FROM `table_example` 
    WHERE `user` = :user 
    AND `status` = "1"'
);

$stmt->bindParam(':user', $_SESSION["user"], PDO::PARAM_STR);
$stmt->execute();
$sum = $stmt->fetchColumn();
echo $sum;

You don't need to write any loop or previous check in order to get the SUM value.

Answer 2

You are doing a SUM query which will always have a result row.

SELECT SUM(count) AS value_sum FROM tableexample WHERE user = '$user' AND status = '1'

You might want to check the SUM(count) = 0 where you can do it in your while loop

while ($row = mysqli_fetch_array($request)) {
    if (!empty($row[0])) {
        echo $row[0]; // sum not 0
    } else {
        echo '0';
    }
}

Answer 3

Your are using mysqli_fetch_array which returns array with both numeric and associative indexes instead of just associative array. You should use mysqli_fetch_assoc or mysqli_fetch_array with 2nd parameter resulttype as mentioned in docs.

resulttype This optional parameter is a constant indicating what type of array should be produced from the current row data. The possible values for this parameter are the constants MYSQLI_ASSOC, MYSQLI_NUM, or MYSQLI_BOTH.

By using the MYSQLI_ASSOC constant this function will behave identically to the mysqli_fetch_assoc(), while MYSQLI_NUM will behave identically to the mysqli_fetch_row() function. The final option MYSQLI_BOTH will create a single array with the attributes of both.

https://www.php.net/manual/en/mysqli-result.fetch-array.php

https://www.php.net/manual/en/mysqli-result.fetch-assoc.php

As per OP's request. Here is your code with some changes.

<?php
$user = $_SESSION["user"];
$systems = mysqli_connect("localhost", "root", "", "testdatabase");
$query = mysqli_real_escape_string($systems, "SELECT SUM(count) AS value_sum FROM tableexample WHERE user = '$user' AND status = '1'");
$request = mysqli_query($systems, $query) or die(mysqli_error($systems)); // Added die for dev env. You can choose how you want to deal with db query error.
if (mysqli_num_rows($request) > 0) {
    // Just replaced mysqli_fetch_array with mysqli_fetch_assoc
    while ($row = mysqli_fetch_assoc($request)) {
        echo '' . $row["value_sum"] . '';
    }
} else {
    echo '0';
}

Also, as you are getting 0 it seems that your if (mysqli_num_rows($request) > 0) is not returning true. Might be due to some error in db query, you may want to check that again.

Edit 04-04-2020:

1. Updated info about indexes of returned array by mysqli_fetch_array.
2. Added mysqli_real_escape_string for $query.