I have a form that includes a multiple select box. As a result, I am inserting variables into the database (e.g. the contents of input fields) as well as an array - the multiple select box values.
I tried to do this as two separate queries, but I'm getting an error after my first query:
Column count doesn't match value count at row 1
Here's my code snippet (the first query) with by debugging commented out:
$instruments = mysqli_real_escape_string($con, $_POST['instruments']);
//var_dump($_POST['instruments']);
$columns = implode(", ",array_keys($instruments));
//var_dump($columns);
$escaped_values = array_map('mysql_real_escape_string', array_values($instruments));
//var_dump($escaped_values);
$values = implode(", ", $escaped_values);
//var_dump($values);
$sql = "INSERT INTO `depfinder_sandbox`(instrument1, instrument2, instrument3, instrument4, instrument5) VALUES ($values)";
$result1 = mysqli_query($con, $sql) or die(mysqli_error($con));
While debugging, I've found the following:
-
var_dump($instruments)
returns NULL despite the variable being set. - However,
var_dump($_POST['instruments'])
returns as it should. - If I use
$_POST['instruments']
,var_dump($escaped_values)
returnsstring(4) "0, 1"
, which is incorrect. -
var_dump($values)
returns NULL whatever I do.
Even if the variables were returning as expected, would I still have a column count problem given that $instruments[]
can contain anything from 1 to 5 values, yet the query stipulates 5 (instrument1, instrument2, instrument3, instrument4, instrument5)?
UPDATE
Here's what I have now:
foreach ($_POST['instruments'] as $key => $value)
{
$instruments[$key] = mysqli_real_escape_string($con, $value);
}
var_dump($instruments); // returns perfectly
$columns = implode(", ",array_keys($instruments));
var_dump($columns); // returns: string(4) "0, 1"
$values = implode(", ", $columns);
$sql = "INSERT INTO `depfinder_sandbox`(instrument1, instrument2, instrument3, instrument4, instrument5) VALUES ($values)";
$result1 = mysqli_query($con, $sql) or die(mysqli_error($con)); // returns: Column count doesn't match value count at row 1