I Have the following PHP script:
$i = 0;
while ($i < 30){
$insertquery = "INSERT INTO {$savename} VALUES (";
foreach ($valuearray as $column){
$insertquery .= $column[$i] . ",";
}
$insertquery .= ");";
echo $insertquery;
$queryResult = mysqli_query($con, $insertquery);
if ($queryResult === TRUE){
}else{
print "<br /><br />No Row created. Check " . mysqli_error($con);
}
$i++;
}
The $valuearray is a two dimensional array with everything that needs to be inserted into the table. The issue is that the above code creates a MySQL statement in the form
INSERT INTO rates_test VALUES (1000,0.1,2.3,100,2,3,);
Which gives a Syntax error as the last value has a , after it.
How could I go about changing the script so that the last value does not have a , after it, or prehaps delete it after the foreach loop?
Before $insertquery .= ");"; you could use $insertquery = rtrim($insertquery, ',');
My preferred method for building IN lists though is to use implode:
"VALUES (" . implode(',', $valuearray) . ")";
It's also worth noting that you should preferably be using parameterized queries. I'm not sure whether you can bind a variable number of arguments using mysqli, but in PDO the idea would be something like:
"VALUES (" . implode(',', array_fill(0, count($valuearray), '?')) . ")";
This emits "VALUES (?,?,?)". Then you could just pass $valuearray to execute.
I prefer to not put it there in the first place if possible, than add something you don't want then remove it later (each to their own I guess):
foreach ($valuearray as $column)
{
$insertquery .= $column[$i];
if ($i < 29)
{
$insertquery .= ",";
}
}
How about using implode function so you don't have to worry about the last comma
$insertquery .= implode(",", $valuearray);
PS: Seems the code is prone to SQL injection if the array comes from user input