COUNT query giving wrong results

Question

I have following table attendance

+--------------------------------+
| rollno | sub | date | presenty |
+--------------------------------+

It currently do not have any row. I want to add unique row with same value of rollno, sub, date and presenty. If any new entry comes with the same credentials, then I want to show user an alert message. Else, new entry should be created. I have written the following code:

$rollno = $_POST['rollno'];
$subject = $_POST['subject'];
$date = $_POST['date'];
$presenty = $_POST['presenty'];
$date_tmp = date_create($date);
$date_new = date_format($date_tmp, "d-m-Y");

$q = $conn->prepare("SELECT COUNT(`presenty`) FROM `attendance` WHERE `rollno` = ? AND `sub`=? AND `date`=?");
$q->bind_param("sss",$rollno,$subject,$date);
$q->execute();
$q->bind_result($pres);
$q->fetch();
$rows= $q->num_rows;
$q->close();
if($rows == 0){
    $insrtqry = $conn->prepare("INSERT INTO attendance(rollno, sub, date, presenty) VALUES(?,?,?,?)");
    $insrtqry->bind_param("ssss",$rollno, $subject, $date_new, $presenty);
    if($insrtqry->execute()){ ?>
        echo "Record Inserted Successfully";
    } else {
        echo "Record not added";
    }
} else {
    echo "Record already exists";
}

But, even if the table has no entry in it, when I try to insert a new record, the count query returns 1 and the else part executes. What is the problem? please help.

Because you do a `COUNT()` query, it always returns 1 row with the `COUNT` result. — jrswgtr, Oct 31 '19 at 12:15
@jrswgtr but, when I try to execute it in sql editor, it returns 0 for empty records — Ashish Joshi, Oct 31 '19 at 12:17
Your'e not even checking the return of `COUNT` you're checking the return of `execute()` which is only `true` or `false`... — AbraCadaver, Oct 31 '19 at 12:18
You have NOT done a FETCH so you are not looking at the result of the COUNT, you are purely looking at the result of an `->execute()` which is TRUE or FALSE — RiggsFolly, Oct 31 '19 at 12:18
@AmanjotKaur it will return 1 row, with 0 as the count result. — jrswgtr, Oct 31 '19 at 12:19

Amanjot Kaur · Accepted Answer · 2019-10-31T13:31:14.823

The issue is that you can't use num_rows here because count always returns something. So, you will always get num_rows as >0, so for the count, you should use the bind_result method.

mysqli_stmt::bind_result -- mysqli_stmt_bind_result — Binds variables to a prepared statement for result storage . Binds columns in the result set to variables. When fetch() is called to fetch data, the MySQL client/server protocol places the data for the bound columns into the specified variables var1, ....

      $rollno = $_POST['rollno'];
$subject = $_POST['subject'];
$date = $_POST['date'];
$date = date('Y-m-d',strtotime($date));
$presenty = $_POST['presenty'];

$q = $conn->prepare("SELECT COUNT(`presenty`) FROM `attendance` WHERE `rollno` = ? AND `sub`=? AND `date`=?");
$q->bind_param("sss",$rollno,$subject,$date);
$q->execute();
$q->bind_result($cnt);
$q->fetch();
$q->close();
if($cnt == 0){
    $insrtqry = $conn->prepare("INSERT INTO attendance(rollno, sub, date, presenty) VALUES(?,?,?,?)");
    $insrtqry->bind_param("ssss",$rollno, $subject, $date, $presenty);
    if($insrtqry->execute()){
        echo "Record Inserted Successfully";
    } else {
        echo "Record not added";
    }
} else {
    echo "Record already exists";
}

For more details you can check, Example of how to use bind_result vs get_result They have brilliant detailed info on using get_result

Comments are not for extended discussion; this conversation has been [moved to chat](https://chat.stackoverflow.com/rooms/201686/discussion-on-answer-by-amanjot-kaur-count-query-giving-wrong-results). — Samuel Liew, Oct 31 '19 at 21:18

COUNT query giving wrong results

1 Answers1