php - 带有like子句的mysql if语句返回null

Question

我正在尝试使用 php pdo 从数据库中搜索，但是我在 mysql 中的 if 语句中搜索的所有值都返回 null。如果有人帮助我，我将不胜感激。这是我的代码。在 echo 中，我使用 var_dump 只是因为看到来自数据库的内容，并且我发现所有 if 语句都返回 null，无论它有什么值，它都应该返回值，因为在第二个条件下我正在调用列名称。有人可以纠正我吗

    <?php
if(isset($_POST['food'])){
    $text = $_POST['food'];
    // connection to the Ddatabase
    $servername = "******";
    $username = "*******";
    try {
    $db = new PDO("mysql:host=$servername;dbname=*********", $username, "********");
    $db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    // let's filter the data that comes in
    $text = trim($text);
    $text = stripslashes($text);
    $text = htmlspecialchars($text);
    $text = strtolower($text);

    // prepare the mysql query to select
    $get_name = $db->prepare("SELECT dou.businessName , m.id, m.ownerid, IF (m.firstFood LIKE :word, m.firstFood, 'no')
, IF (m.secondFood LIKE :word, m.secondFood, 'no')
, IF (m.thirdFood LIKE :word, m.thirdFood, 'no')
, IF (m.fourthFood LIKE :word, m.fourthFood, 'no')
, IF (m.fifthFood LIKE :word, m.fifthFood, 'no')
, IF (m.sixthFood LIKE :word, m.sixthFood, 'no')
, IF (m.seventhFood LIKE :word, m.seventhFood, 'no')
, IF (m.eightFood LIKE :word, m.eightFood, 'no')
, IF (m.ninthFood LIKE :word, m.ninthFood, 'no')
, IF (m.tenthFood LIKE :word, m.tenthFood, 'no')
FROM dineOwnerUser AS dou INNER JOIN menu AS m ON m.ownerid = dou.id WHERE
(m.firstFood LIKE :word
OR m.secondFood LIKE :word
OR m.thirdFood LIKE :word
OR m.fourthFood LIKE :word
OR m.fifthFood LIKE :word
OR m.sixthFood LIKE :word
OR m.seventhFood LIKE :word
OR m.eightFood LIKE :word
OR m.ninthFood LIKE :word
OR m.tenthFood LIKE :word)");
    // bind value
    $get_name->bindValue(':word','%'.$text.'%');
    // execute the query
    $get_name -> execute();
    // show the users on the page
    while($texts = $get_name->fetch(PDO::FETCH_ASSOC)){
        // show each user as a link
        echo '<a href=""><li>'.var_dump($texts['businessName']).'</li></a>';
        echo '<a href=""><li>'.var_dump($texts['firstFood']).'</li></a>';
        echo '<a href=""><li>'.var_dump($texts['secondFood']).'</li></a>';
        echo '<a href=""><li>'.var_dump($texts['thirdFood']).'</li></a>';
        echo '<a href=""><li>'.var_dump($texts['fourthFood']).'</li></a>';
        echo '<a href=""><li>'.var_dump($texts['fifthFood']).'</li></a>';
        echo '<a href=""><li>'.var_dump($texts['sixthFood']).'</li></a>';
        echo '<a href=""><li>'.var_dump($texts['seventhFood']).'</li></a>';
        echo '<a href=""><li>'.var_dump($texts['eightFood']).'</li></a>';
        echo '<a href=""><li>'.var_dump($texts['ninthFood']).'</li></a>';
        echo '<a href=""><li>'.var_dump($texts['tenthFood']).'</li></a>';
        echo '<a href=""><li>'.var_dump($texts['id']).'</li></a>';
    }
}
    catch(PDOException $e)
    {
        echo "Connection failed: " . $e->getMessage();
    }
$db = null;
exit;
}
?>

Answer 1

你忘记了mysql中的AS。

    <?php
    if(isset($_POST['food'])){
        $text = $_POST['food'];
        // connection to the Ddatabase
        $servername = "******";
        $username = "*******";
        try {
        $db = new PDO("mysql:host=$servername;dbname=*********", $username, "********");
        $db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
        // let's filter the data that comes in
        $text = trim($text);
        $text = stripslashes($text);
        $text = htmlspecialchars($text);
        $text = strtolower($text);

        // prepare the mysql query to select the users 
    $get_name = $db->prepare("SELECT dou.businessName , m.id, m.ownerid, IF (m.firstFood LIKE :word, m.firstFood, 'no') AS firstFood
, IF (m.secondFood LIKE :word, m.secondFood, 'no') AS secondFood
, IF (m.thirdFood LIKE :word, m.thirdFood, 'no') AS thirdFood
, IF (m.fourthFood LIKE :word, m.fourthFood, 'no') AS fourthFood
, IF (m.fifthFood LIKE :word, m.fifthFood, 'no') AS fifthFood
, IF (m.sixthFood LIKE :word, m.sixthFood, 'no') AS sixthFood
, IF (m.seventhFood LIKE :word, m.seventhFood, 'no') AS seventhFood
, IF (m.eightFood LIKE :word, m.eightFood, 'no') AS eightFood
, IF (m.ninthFood LIKE :word, m.ninthFood, 'no') AS ninthFood
, IF (m.tenthFood LIKE :word, m.tenthFood, 'no') tenthFood
FROM dineOwnerUser AS dou INNER JOIN menu AS m ON m.ownerid = dou.id WHERE
(m.firstFood LIKE :word
OR m.secondFood LIKE :word
OR m.thirdFood LIKE :word
OR m.fourthFood LIKE :word
OR m.fifthFood LIKE :word
OR m.sixthFood LIKE :word
OR m.seventhFood LIKE :word
OR m.eightFood LIKE :word
OR m.ninthFood LIKE :word
OR m.tenthFood LIKE :word)");
    // bind value
    $get_name->bindValue(':word','%'.$text.'%');
    // execute the query
    $get_name -> execute();
    // show the users on the page
    while($texts = $get_name->fetch(PDO::FETCH_ASSOC)){
        // show each user as a link
        echo '<a href=""><li>'.$texts['businessName'].'</li></a>';
        echo '<a href=""><li>'.$texts['firstFood'].'</li></a>';
        echo '<a href=""><li>'.$texts['secondFood'].'</li></a>';
        echo '<a href=""><li>'.$texts['thirdFood'].'</li></a>';
        echo '<a href=""><li>'.$texts['fourthFood'].'</li></a>';
        echo '<a href=""><li>'.$texts['fifthFood'].'</li></a>';
        echo '<a href=""><li>'.$texts['sixthFood'].'</li></a>';
        echo '<a href=""><li>'.$texts['seventhFood'].'</li></a>';
        echo '<a href=""><li>'.$texts['eightFood'].'</li></a>';
        echo '<a href=""><li>'.$texts['ninthFood'].'</li></a>';
        echo '<a href=""><li>'.$texts['tenthFood'].'</li></a>';
        echo '<a href=""><li>'.$texts['id'].'</li></a>';
    }
}
    catch(PDOException $e)
    {
        echo "Connection failed: " . $e->getMessage();
    }
$db = null;
exit;
}
?>