PHP MySQLI 选择多列返回空值

标签: MySQL PHP
发布时间: 2017/4/9 23:03:30
注意事项: 本文中文内容可能为机器翻译,如要查看英文原文请点击上面连接.

我试图从我与以下预准备语句的数据库中检索 2 列︰

$statement = $conn->prepare("SELECT script, work_instruction FROM ".$table." WHERE category='".$category."' AND sub_category='".$sub_category."' AND issue='".$issue."'");

每当我使用此查询,返回 PHP null 到我的网页,即使数据是目前的 DB 中。然而,以下的语句工作 (相同为 work_instruction)︰

$statement = $conn->prepare("SELECT script FROM ".$table." WHERE category='".$category."' AND sub_category='".$sub_category."' AND issue='".$issue."'");

一旦我准备的语句执行,并依次通过我的结果,然后返回它们。我认为问题可能躺在这里,但不是敢肯定。

$statement->execute();
$statement->bind_result($results);

while($statement->fetch()){
    array_push($myArr, $results);
}

$statement->free_result();
echo json_encode($myArr);


这里是我的桌子看上去像示例︰enter image description here


这里是整个函数,以防万一你需要看到更多︰

// Responsible for generating queries to the database
// and returning the data collected
function query($conn, $column) {    
    $myArr = array();
    $table = $_POST['table'];
    $category = $_POST['category'];
    $sub_category = $_POST['sub_category'];
    $issue = $_POST['issue'];

    switch ($column) {
        case 'category':
            $statement = $conn->prepare("SELECT DISTINCT category FROM ".$table);
            break;

        case 'sub_category':
            $statement = $conn->prepare("SELECT DISTINCT sub_category FROM ".$table." WHERE category='".$category."'");
            break;

        case 'issue':
            $statement = $conn->prepare("SELECT DISTINCT issue FROM ".$table." WHERE category='".$category."' AND sub_category='".$sub_category."'");
            break;

        //This does not work
        case 'script':
        $statement = $conn->prepare("SELECT script, work_instruction FROM ".$table." WHERE category='".$category."' AND sub_category='".$sub_category."' AND issue='".$issue."'");
        break;

        //This does work
        case 'work_instruction':
        $statement = $conn->prepare("SELECT work_instruction FROM ".$table." WHERE category='".$category."' AND sub_category='".$sub_category."' AND issue='".$issue."'");
        break;

        case 'doc_link':
            //$statement = $conn->prepare($query);
            break;

        default:
            break;
    }
    $statement->execute();
    $statement->bind_result($results);

    while($statement->fetch()){
        array_push($myArr, $results);
    }

    $statement->free_result();
    echo json_encode($myArr);

}

解决方法 1:

为了简化您的查询功能和添加在错误报告中。

function query($conn, $column) {    
    $myArr = array();
    $table = '`' . trim($_POST['table']) . '`';
    $category = $_POST['category'];
    $sub_category = $_POST['sub_category'];
    $issue = $_POST['issue'];
    $selects = array(
       'category' => 'SELECT DISTINCT `category` FROM ' . $table,
       'sub_category' => 'SELECT DISTINCT `sub_category` FROM ' . $table . ' WHERE category = ?',
       'issue' => 'SELECT DISTINCT `issue` FROM ' . $table . ' WHERE category = ? AND sub_category = ?',
       'script' => 'SELECT `script`, `work_instruction` FROM ' . $table . ' WHERE category = ? AND sub_category = ? AND issue = ?',
       'work_instruction' => 'SELECT `work_instruction` FROM ' . $table . ' WHERE category = ? AND sub_category = ? AND issue = ?'
    );
    if (true === isset($selects[$column])) {
       $statement = $conn->prepare($selects[$column]);
       switch ($column) {
          case 'sub_category':
             $statement->bind_param('s', $category);
             break;
          case 'issue':
             $statement->bind_param('ss', $category, $sub_category);
             break;
          case 'work_instruction':
          case 'script':
             $statement->bind_param('sss', $category, $sub_category, $issue);
             break;
       }
       if (!$statement->execute()) {
           trigger_error('Error executing MySQL query: ' . $statement->error);
       }
       $results = $statement->get_result();
       while ($row = $results->fetch_array(MYSQLI_ASSOC)) {
           $myArr[] = $row;
       }
       $results->free();
       $statement->close();
   } else {
       trigger_error('Unknown column specified. Expected one of: "' . implode('", "', array_keys($selects)) . '" received "' . $column . '"');
   }
   echo json_encode($myArr);
}

在您使用 bind_result 测试查询我返回数据很好,所以如果你改变这之后仍然存在问题,我相信它会有事做的变量值。http://sqlfiddle.com/# ! 9/0bfb79/2

要确定的问题我们需要示例表结构 (列和值),因为接近尽可能的实际值,像在 SQL 小提琴我联系在一起。

同时被送到查询填写表、 类别、 sub_category 和问题的变量值。再一次像 SQL 小提琴我联系在一起。

除非您使用 $statement->store_result() 您不需要调用 $statement->free_result() http://php.net/manual/en/mysqli-stmt.free-result.php而不是释放所分配的内存 $statement->get_result http://php.net/manual/en/mysqli-result.free.php

官方微信
官方QQ群
31647020