Warning: sqlsrv_fetch_array() expects parameter 1 to be resource when using explode() as variable in multiple SQL query | PHP, SQL

Question

Why am I receiving this error?

Warning: sqlsrv_fetch_array() expects parameter 1 to be resource, boolean given.?

I tested it manually when I tried INSERT INTO employeemasterfile (employeeidno) VALUES ('$deptcode'+'$empno'); to the database, with the following values: $deptcode: D001 and $empno: 0000017, and it works perfectly.

This is the SQL code.

<?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 jobposition
FROM departmentmasterfile, hrdjobpositionentry,employeemasterfile 
WHERE hrdjobpositionentry.department=departmentmasterfile.departmentname AND
LEFT (employeemasterfile.employeeidno,4) = LEFT (departmentmasterfile.departmentcode,4) AND
LEFT (departmentcode,4) = '$deptcode' AND hrdjobpositionentry.jobposition='$jobposition'
GROUP BY LEFT (departmentcode,4), section, departmentname, paymentmode, hrdjobpositionentry.jobposition
ORDER 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)){

The while() clause shouldn't be a problem, correct? Since I'm only inserting on the second SQL query.

This SQL query only works when I remove INSERT INTO employeemasterfile (employeeidno) VALUES ('$deptcode'+'$empno');

EDIT: This is the $conn, this is loaded via include() in the main page.

<?php
$serverName = "XX\XXXX";
$connectionInfo = array( "Database"=>"XXXX", "UID"=>"XX", "PWD"=>"XXXX",
'ReturnDatesAsStrings'=>true,"CharacterSet" => "UTF-8");
$conn = sqlsrv_connect( $serverName, $connectionInfo);

if( $conn ) {

}else{
     echo "Connection could not be established.<br />";
     die( print_r( sqlsrv_errors(), true));
}
?>

Answer 1

Explanations:

The reason for your error is that sqlsrv_query() returns false, which means that your statement is not executed correctly:

Return value (sqlsrv_query):

A statement resource. If the statement cannot be created and/or executed, false is returned.

You need to check the result of sqlsrv_query() and get the errors with sqlsrv_errors(), if the statement is not executed correctly.

<?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;
}

...
?>

Additional notes:

Try to use parameterized queries. Function sqlsrv_query() supports using parameteres:

The sqlsrv_query function is well-suited for one-time queries and should be the default choice to execute queries unless special circumstances apply. This function provides a streamlined method to execute a query with a minimum amount of code. The sqlsrv_query function does both statement preparation and statement execution, and can be used to execute parameterized queries.

Example (based on your code):

<?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);

?>