Dynamically generate type definition string for prepared statement

I’m working on a script that is essentially loading data from an API into a local MySQL database. The values are variable depending on what is returned by the API.

So far everything is working just fine up until I try to actually insert the rows into the MySQL db. Specifically, I know I should be using prepared statements, but I’m having trouble when I try to bind the variables to the prepared statement. When I try to run the below code, I get:

PHP Warning:  mysqli_stmt::bind_param(): Number of elements in type definition string doesn't match number of bind variables in /opt/awn2sql/functions.php on line 212

Here’s the code in question:

$readingValues = array_values($read); //array of just the values in the original array
array_push($readingValues, $devicemac); //add the MAC address of the device that recorded the reading to the array
    
$columns = implode(", ",$readingTypes); //create a string of column names to build the SQL query
    
$valuesCount = count($readingValues); //get a count of the values to fill an array with placeholders
$stmt_placeholders = implode(',',array_fill(0,$valuesCount,'?')); //fill an array with placeholders (i.e. ?,?,?) - see above
$stmt_param = null; //$stmt_param will hold the type definitions string for binding the 
    
foreach ($readingValues as $param) { //iterate through each value in the $readingValues array, get the type, and add it to the type definitions string
    if (gettype($param) == 'integer')
    {
        $stmt_param = $stmt_param.'i';
    }
    else if (gettype($param) == 'double')
    {
        $stmt_param = $stmt_param.'d';
    }               
    else if (gettype($param) == 'string')
    {
    $stmt_param = $stmt_param.'s';
    }
    else if (gettype($param) == 'blob')
    {
        $stmt_param = $stmt_param.'b';
    }
    else
    {
        echo "Invalid data type!";
    }
}

$val_insert_query = "INSERT INTO ".$config['mysql_db'].".readings (".$columns.") VALUES (".$stmt_placeholders.");"; //Template for the query
    
$stmt=$mysqli->prepare($val_insert_query); //Prepares the template for the query for binding, prepared statement becomes $stmt

echo ($stmt_param." (".strlen($stmt_param).")n"); //for debugging, echo the type definiton string and get its length (which should match the number of values)

echo (count($readingValues)); //count the number of values, which should match the number of elements in the type defintion string
    
$stmt->bind_param($stmt_param, $readingValues); //Binding
    
$stmt->execute(); //execute the statement

I freely admit that I’m a bit of a newbie at this, so I’m open to any and all suggestions on how to do this better. For what it’s worth, there’s never any direct user input, so I’m relatively unconcerned about security concerns if that makes a difference in how best to approach this.

Thanks in advance!

Answer

bind_param() actually takes variable arguments, not an array argument. But modern PHP has syntax for turning an array into multiple scalar arguments:

$stmt->bind_param($stmt_param, ...$readingValues); //Binding

This is equivalent to passing the array elements as individual arguments:

$stmt->bind_param($stmt_param, $readingValues[0], $readingValues[1],
    $readingValues[2], etc.);

But that’s awkward if you don’t know how many elements are in the array.

---

FYI, I like to use PDO instead of mysqli. You don’t have to bind anything, just pass the array of values as the argument to execute():

$stmt=$pdo->prepare($val_insert_query); 

$stmt->execute( $readingValues );

I find PDO to be a lot easier. The reason to use mysqli is if you have a lot of legacy code from the mid-2000’s that you need to adapt. If you’re just starting out, you have no old code. So you might as well adopt PDO to start with.

There’s a good tutorial for PDO: https://phpdelusions.net/pdo/