php - COUNT query giving wrong results
问题描述
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.
解决方案
问题是你不能在这里使用 num_rows 因为 count 总是返回一些东西。因此,您将始终将 num_rows 设为 >0,因此对于计数,您应该使用 bind_result 方法。
mysqli_stmt::bind_result - mysqli_stmt_bind_result - 将变量绑定到准备好的语句以存储结果。将结果集中的列绑定到变量。当调用 fetch() 来获取数据时,MySQL 客户端/服务器协议将绑定列的数据放入指定的变量 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";
}
有关更多详细信息,您可以查看如何使用 bind_result 与 get_result 的示例他们有关于使用 get_result 的精彩详细信息
推荐阅读
- javascript - Chart.js 如何制作垂直虚线
- python - Bool 没有属性 fetchone,即使它是一个游标
- azure - Kubernetes Dashboard 右上角没有用户图标
- apache-flink - flink 中的自定义指标组
- javascript - React Firebase 应用程序身份验证服务在生产中不起作用
- python - 使用 CSV 文件和 python 的多图像下载器
- java - 上下文类加载器和在依赖项之一中加载资源的问题
- python - 如何在 Python 中对 GENIA 语料库进行 XML 解析
- php - WordPress 使用工作缩略图重命名附件文件
- graphql - 主键与 Amplify Schema 文件的关系