Dynamically generate type definition strings for prepared statements

Question

I'm writing a script that essentially loads data from an API into a local MySQL database. These values ​​change based on what the API returns.

So far everything is working fine until I try to actually insert rows into the MySQL database. Specifically, I know I'm supposed to use prepared statements, but I'm having trouble when I try to bind variables to prepared statements. When I try to run the following 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

This is 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 suggestions on how to do it better. For what it's worth, there's never any direct user input, so I'm relatively unconcerned about security issues, if that has any bearing on how best to solve this problem.

Thanks in advance!

Answer 1

bind_param() actually accepts variable parameters, not array parameters. But modern PHP has a syntax for converting an array into multiple scalar parameters:

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

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

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

But it's embarrassing if you don't know how many elements there are in the array.

---

FYI, I prefer using PDO instead of mysqli. You don't have to bind anything, just pass the array of values ​​as a parameter to execute():

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

$stmt->execute( $readingValues );

I find PDO easier. The reason to use mysqli is if you have a lot of legacy code from the mid-2000s that needs tweaking. If you're just starting out, there's no legacy code. So you might as well adopt PDO first.

There is a good PDO tutorial: https://phpdelusions.net/pdo/