php - 由于错误的 1 和 0，SQL 得到相反的结果，反之亦然

Question

在这段代码中，我试图测试这种检查数据的 boolean 方法。这个项目的目的是为我们的校园制作一个食品预算系统。在这个初级阶段，我设置了一个表，其中有一个 boolean 值来检测不同的选项。

<?php

$db_host = "db4free.net";
$db_name = "testable";
$db_username = "muymuy1";
$db_password = "Muymuy2002";

$db = new PDO("mysql:host=$db_host; dbname=$db_name; charset=utf8", "$db_username", "$db_password") or die("error");

$sql = "INSERT INTO `food` (name, id, price, isForSingle, isForPair, isForSharing, isFullMeal) VALUES ('Sanwhich 234', NULL, 110, 1, 0, 0, 0)";
$db->query($sql) or die("Something happened");

$sql = "DELETE FROM `food` WHERE name LIKE '%sanwhich%'";
$stmt = $db->query($sql) or die("error 4");

$sql = "SELECT * FROM `food` WHERE isForSingle=0";
$stmt = $db->query($sql) or die("error 5");

$data = array();
$count = 0;

if($stmt !== false)
{
     while($row = $stmt->fetch(PDO::FETCH_ASSOC))
        { 
            $data[$count] = array(
                "id" => $row['id'],
                "name" => $row['name'],
                "price" => $row['price'],
                "isFullMeal" => $row['isFullMeal'],
                "isForSharing" => $row['isForSharing'],
                "isForPair" => $row['isForPair'],
                "isForSingle" => $row['isForSingle'],
             );
         $count += 1;
      }

 }

echo '<pre>'; print_r($data); echo '</pre>';


?>

表数据:

但是，我的问题是这不起作用。 0与1混淆，1与0混淆，因为当我用WHERE isForPair = 0搜索时，出现的是有1的数据行，而当我搜索WHERE isForPair = 1，出现的是有0的数据行。

1) ... WHERE isForPair = 0 (Output: Results that isForPair = 1)

2) ... WHERE isForPair = 1(输出:无，尽管表中的所有内容都是 isForPair = 1)

Answer 1

您的表 (CREATE TABLE) 语句的 DDL 语句必须与查询匹配。似乎您想将表中的数据与 boolean 值匹配。在这种情况下，您可以将字段 isForSingle 设置为数据库中的 TINYINT 数据类型。

注意 BOOLEAN 和TINYINT 是一样的 in MySQL RDBMS.

这是我试过的DDL语句。

CREATE TABLE food (id INTEGER, name VARCHAR(100), isForSingle TINYINT);

然后，我插入 2 条记录。

INSERT INTO food VALUES(1,'Hamburger',1);
INSERT INTO food VALUES(2,'Pizza',0);

然后，我尝试使用您的 PHP 代码查询数据。

<?php

$db_host = "YOURDBHOST";
$db_name = "YOURDATABASE";
$db_username = "YOURUSERNAME";
$db_password = "YOURPASSWORD";

$db = new PDO("mysql:host=$db_host; dbname=$db_name; charset=utf8", "$db_username", "$db_password") or die("error");

// ------------------------------------------------------------------
$sql = "SELECT * FROM `food` WHERE isForSingle=1";
$stmt = $db->query($sql) or die("error");

$data = array();
$count = 0;

if($stmt !== false)
{
     while($row = $stmt->fetch(PDO::FETCH_ASSOC))
        { 
            $data[$count] = array(
                "id" => $row['id'],
                "name" => $row['name'],
                "isForSingle" => $row['isForSingle'],
             );
         $count += 1;
      }

 }

var_dump($data);
// -----------------------------------------------------------------



// ------------------------------------------------------------------
$sql = "SELECT * FROM `food` WHERE isForSingle=0";
$stmt = $db->query($sql) or die("error");

$data = array();
$count = 0;

if($stmt !== false)
{
     while($row = $stmt->fetch(PDO::FETCH_ASSOC))
        { 
            $data[$count] = array(
                "id" => $row['id'],
                "name" => $row['name'],
                "isForSingle" => $row['isForSingle'],
             );
         $count += 1;
      }

 }

var_dump($data);
// ------------------------------------------------------------------

结果如下。

array(1) {
  [0] =>
  array(3) {
    'id' =>
    string(1) "1"
    'name' =>
    string(9) "Hamburger"
    'isForSingle' =>
    string(1) "1"
  }
}
array(1) {
  [0] =>
  array(3) {
    'id' =>
    string(1) "2"
    'name' =>
    string(5) "Pizza"
    'isForSingle' =>
    string(1) "0"
  }
}

希望对您有所帮助。