PHP bindValue for SQL statement doesnt work inside function

Question

I often create functions when code is duplicated several times. In this instance, I'm building SQL queries and binding the parameters afterwards. The query works if I dont try to bind inside a function, but the code is so much cleaner looking with the function.

Here's the code without the function (working):

if(!empty($param_steamid) && ($param_steamid != ""))
{
    $stmt->bindValue(":param_steamid", '%'.$param_steamid.'%', PDO::PARAM_STR);
}
if(!empty($param_name) && ($param_name != ""))
{
    $stmt->bindValue(":param_name", '%'.$param_name.'%', PDO::PARAM_STR);
}
if(!empty($param_lastclientip) && ($param_lastclientip != ""))
{
    $stmt->bindValue(":param_lastclientip", '%'.$param_lastclientip.'%', PDO::PARAM_STR);
}
if(!empty($param_match) && ($param_match != ""))
{
    $stmt->bindValue(":param_match", $param_match, PDO::PARAM_INT);
}
$stmt->bindValue(":startpgnum", $start_from, PDO::PARAM_INT);

Here's the code using the function and the function:

SQLBindParam($stmt, $param_steamid, "param_steamid", true);
SQLBindParam($stmt, $param_name, "param_name", true);
SQLBindParam($stmt, $param_lastclientip, "param_lastclientip", true);
SQLBindParam($stmt, $param_match, "param_match");
SQLBindParam($stmt, $start_from, "startpgnum");

function SQLBindParam(&$stmt, &$variable, $paramname, $bStringInput = false)
{
    if(empty($variable) || ($variable == ""))
    {
        return;
    }

    if($bStringInput == true)
    {
        $stmt->bindValue(':'.$paramname, '%'.$variable.'%', PDO::PARAM_STR);
    }
    else
    {
        $stmt->bindValue(':'.$paramname, $variable, PDO::PARAM_INT);
    }
    return;
}

When using the function, I get the following error:

Error: SQLSTATE[HY093]: Invalid parameter number: no parameters were bound

Notes: The where statement has similar checks for if the variables are empty/nullstring, so the # params will not be mismatched due to that check.

I passed $stmt and $variable by reference (& in front of parameter).

Any idea why the function isnt binding the parameters?

Answer 1

"0" (as a string) is evaluated as empty but != "". As an integer, both evaluate the same.

Solution: One of the parameters was not being bound because it was 0 (non-string) and wasnt passing the checks. It had nothing to do with it being in a function, as Ghost pointed out. Additionally, instead of empty(), I should have been using isset().

Here was my testing:

$test = empty($testbuffer);
console_log($test, "Test result 1a: ");
$test = ($testbuffer == "");
console_log($test, "Test result 1b: ");
$test = ($testbuffer == 0);
console_log($test, "Test result 1c: ");
$test = ($testbuffer === 0);
console_log($test, "Test result 1d: ");
$test = ($testbuffer == "0");
console_log($test, "Test result 1e: ");
$test = ($testbuffer === "0");
console_log($test, "Test result 1f: ");
$testbuffer = "";
$test = empty($testbuffer);
console_log($test, "Test result 2a: ");
$test = ($testbuffer == "");
console_log($test, "Test result 2b: ");
$test = ($testbuffer == 0);
console_log($test, "Test result 2c: ");
$test = ($testbuffer === 0);
console_log($test, "Test result 2d: ");
$test = ($testbuffer == "0");
console_log($test, "Test result 2e: ");
$test = ($testbuffer === "0");
console_log($test, "Test result 2f: ");
$testbuffer = "0";
$test = empty($testbuffer);
console_log($test, "Test result 3a: ");
$test = ($testbuffer == "");
console_log($test, "Test result 3b: ");
$test = ($testbuffer == 0);
console_log($test, "Test result 3c: ");
$test = ($testbuffer === 0);
console_log($test, "Test result 3d: ");
$test = ($testbuffer == "0");
console_log($test, "Test result 3e: ");
$test = ($testbuffer === "0");
console_log($test, "Test result 3f: ");
$testbuffer = "1";
$test = empty($testbuffer);
console_log($test, "Test result 4a: ");
$test = ($testbuffer == "");
console_log($test, "Test result 4b: ");
$test = ($testbuffer == 0);
console_log($test, "Test result 4c: ");
$test = ($testbuffer === 0);
console_log($test, "Test result 4d: ");
$test = ($testbuffer == "0");
console_log($test, "Test result 4e: ");
$test = ($testbuffer === "0");
console_log($test, "Test result 4f: ");
$testbuffer = 0;
$test = empty($testbuffer);
console_log($test, "Test result 5a: ");
$test = ($testbuffer == "");
console_log($test, "Test result 5b: ");
$test = ($testbuffer == 0);
console_log($test, "Test result 5c: ");
$test = ($testbuffer === 0);
console_log($test, "Test result 5d: ");
$test = ($testbuffer == "0");
console_log($test, "Test result 5e: ");
$test = ($testbuffer === "0");
console_log($test, "Test result 5f: ");
$testbuffer = 1;
$test = empty($testbuffer);
console_log($test, "Test result 6a: ");
$test = ($testbuffer == "");
console_log($test, "Test result 6b: ");
$test = ($testbuffer == 0);
console_log($test, "Test result 6c: ");
$test = ($testbuffer === 0);
console_log($test, "Test result 6d: ");
$test = ($testbuffer == "0");
console_log($test, "Test result 6e: ");
$test = ($testbuffer === "0");
console_log($test, "Test result 6f: ");

Output:

//Test 1: (variable not set)
Test result 1a: 1   //empty()
Test result 1b: 1   //== ""
Test result 1c: 1   //== 0
Test result 1d:     //=== 0
Test result 1e:     //== "0"
Test result 1f:     //=== "0"
//Test 2: ""
Test result 2a: 1
Test result 2b: 1
Test result 2c: 1
Test result 2d: 
Test result 2e: 
Test result 2f: 
//Test 3: "0"
Test result 3a: 1
Test result 3b: 
Test result 3c: 1
Test result 3d: 
Test result 3e: 1
Test result 3f: 1
//Test 4: "1"
Test result 4a: 
Test result 4b: 
Test result 4c: 
Test result 4d: 
Test result 4e: 
Test result 4f: 
//Test 5: 0
Test result 5a: 1
Test result 5b: 1
Test result 5c: 1
Test result 5d: 1
Test result 5e: 1
Test result 5f: 
//Test 6: 1
Test result 6a: 
Test result 6b: 
Test result 6c: 
Test result 6d: 
Test result 6e: 
Test result 6f: 

I changed the function to the following:

function SQLBindParam(&$stmt, &$variable, $paramname, $bStringInput = false, $bSkipCheck = false)
{
    if(!isset($variable))
    {
        return;
    }
    else if(!$bSkipCheck)
    {
        if($variable == "")
        {
            return;
        }
    }

    if($bStringInput == true)
    {
        $stmt->bindValue(':'.$paramname, '%'.$variable.'%', PDO::PARAM_STR);
    }
    else
    {
        $stmt->bindValue(':'.$paramname, $variable, PDO::PARAM_INT);
    }
    return;
}

And edited the call for it on the last one to be:

SQLBindParam($stmt, $start_from, "startpgnum", false, true);