将PHP变量和数组插入MySQL

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) returns string(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