警告：sqlsrv_fetch_array() 在多个 SQL 查询中使用explode

Question

为什么我会收到此错误？警告：sqlsrv_fetch_array() 期望参数 1 是资源，给定的布尔值。？我在尝试INSERT INTO employeemasterfile (employeeidno) VALUES ('$deptcode'+'$empno');数据库时手动测试了它，使用以下值$deptcode：：D001 和$empno：0000017，它完美地工作。这是 SQL 代码。<?php if(isset($_POST['referenceno']))$data=$_POST['referenceno'];$explode= explode(":", $data);$user = $user['fingerscanno'];$deptcode=$explode[0];$emp=$explode[1];$empno = str_pad(++$emp,7,"0",STR_PAD_LEFT);$jobposition=$explode[2];$departmentname=$explode[3];$sql = "SELECT DISTINCT LEFT (departmentcode,4) as deptcode,MAX(RIGHT(employeeidno, 7)) as empno,section,  departmentname, paymentmode,hrdjobpositionentry.jobposition as jobpositionFROM departmentmasterfile, hrdjobpositionentry,employeemasterfile WHERE hrdjobpositionentry.department=departmentmasterfile.departmentname ANDLEFT (employeemasterfile.employeeidno,4) = LEFT (departmentmasterfile.departmentcode,4) ANDLEFT (departmentcode,4) = '$deptcode' AND hrdjobpositionentry.jobposition='$jobposition'GROUP BY LEFT (departmentcode,4), section, departmentname, paymentmode, hrdjobpositionentry.jobpositionORDER BY LEFT (departmentcode,4);INSERT INTO employeemasterfile (employeeidno) VALUES ('$deptcode'+'$empno');";    $query = sqlsrv_query($conn, $sql, array(), array("Scrollable" => SQLSRV_CURSOR_KEYSET));    while($row = sqlsrv_fetch_array($query, SQLSRV_FETCH_ASSOC)){该while()条款应该没有问题，对吗？因为我只是在第二个 SQL 查询中插入。此 SQL 查询仅在我删除时有效 INSERT INTO employeemasterfile (employeeidno) VALUES ('$deptcode'+'$empno');

Answer 1

说明：

你的错误的原因是sqlsrv_query() return false，这意味着你的语句没有正确执行：

返回值（sqlsrv_query）：

语句资源。如果无法创建和/或执行该语句，则返回 false。

如果语句未正确执行，您需要检查 的结果sqlsrv_query()并获取错误sqlsrv_errors()。

<?php

...

$query = sqlsrv_query($conn, $sql, array(), array("Scrollable" => SQLSRV_CURSOR_KEYSET));

if ($query === false) {

    echo "Error (sqlsrv_query): ".print_r(sqlsrv_errors(), true);

    exit;

}

...

?>

补充说明：

尝试使用参数化查询。函数sqlsrv_query()支持使用参数：

sqlsrv_query 函数非常适合一次性查询，除非特殊情况适用，否则应该是执行查询的默认选择。此函数提供了一种以最少代码执行查询的简化方法。sqlsrv_query 函数同时进行语句准备和语句执行，并可用于执行参数化查询。

示例（基于您的代码）：

<?php 

if (!isset($_POST['referenceno'])) {

    echo "Error."

    exit;

}   

$data = $_POST['referenceno'];

//$user = $user['fingerscanno'];

$explode        = explode(":", $data);

$deptcode       = $explode[0];

$emp            = $explode[1];

$empno          = str_pad(++$emp, 7, "0", STR_PAD_LEFT);

$jobposition    = $explode[2];

$departmentname = $explode[3];

$sql = "

    SELECT 

        DISTINCT LEFT (departmentcode,4) as deptcode,

        MAX(RIGHT(employeeidno, 7)) as empno,

        section,  

        departmentname, 

        paymentmode,

        hrdjobpositionentry.jobposition as jobposition

    FROM departmentmasterfile, hrdjobpositionentry,employeemasterfile 

    WHERE 

        hrdjobpositionentry.department = departmentmasterfile.departmentname AND

        LEFT (employeemasterfile.employeeidno,4) = LEFT (departmentmasterfile.departmentcode,4) AND

        LEFT (departmentcode,4) = ? AND hrdjobpositionentry.jobposition = ?

    GROUP BY LEFT (departmentcode,4), section, departmentname, paymentmode, hrdjobpositionentry.jobposition

    ORDER BY LEFT (departmentcode,4);

    INSERT INTO employeemasterfile (employeeidno) VALUES (? + ?);

";

$params = array(

    $deptcode,

    $jobposition,

    $deptcode,

    $empno

);

$query = sqlsrv_query($conn, $sql, $params, array("Scrollable" => SQLSRV_CURSOR_KEYSET));

if ($query === false) {

    echo "Error (sqlsrv_query): ".print_r(sqlsrv_errors(), true);

    exit;

}

while($row = sqlsrv_fetch_array($query, SQLSRV_FETCH_ASSOC)) {

    var_dump($row);

    echo "<br>";

}

sqlsrv_free_stmt($query);

?>