我正在编写一个脚本,基本上是将数据从API加载到本地MySQL数据库.根据API返回的内容,这些值是可变的.
到目前为止,一切都很正常,直到我try 将这些行插入MySQL数据库.具体来说,我知道我应该使用prepared语句,但当我试图将变量绑定到prepared语句时,我遇到了麻烦.当我try 运行以下代码时,我得到:
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
下面是有问题的代码:
$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
我坦率地承认,我在这方面有点新手,所以我愿意接受任何关于如何做得更好的建议.值得一提的是,从来没有任何直接的用户输入,所以我相对不关心安全问题,如果这对如何最好地实现这一点有影响的话.
提前谢谢!