php - INSERT array - PDO

Question

I've got a portion of code that is supposed to take the data entered in a form, store it in an array and then enter it into the database. I have used var_dump on $fields and $data and they are both returning the information entered in the field (in the add_habbo function). So the problem I've got is that the MYSQL/PDO code isn't inserting this data into the database.

This is the code that I am using to insert them into the database:

    $fields = '`' . implode('`, `', array_keys($habbo_data)) . '`';
    $data   = ''' . implode('', '', $habbo_data) . ''';

    var_dump($fields);
    var_dump($data);

    global $con;

    $query = "INSERT INTO `personnel` (:fields) VALUES (:data)";
    $result = $con->prepare($query);
    $result->bindParam(':fields', $fields, PDO::PARAM_STR);
    $result->bindParam(':data', $data, PDO::PARAM_STR);
    $result->execute();

I get the impression it has something to with the bindParam sections, possibly PDO::PARAM_STR? Thanks for your assistance!

Update:

$fields = '`' . implode('`, `', array_keys($habbo_data)) . '`';
$fields_data   = ':' . implode(', :', array_keys($habbo_data));

var_dump($fields);
var_dump($fields_data);

global $con;

$query = "INSERT INTO `personnel` (`rank`, `habbo_name`, `rating`, `asts`, `promotion_date`, `transfer_rank_received`, `cnl_trainings`, `rdc_grade`,
    `medals`, `branch`) VALUES ({$fields_data})";
$result = $con->prepare($query);
$result->execute($habbo_data);

$arr = $result->errorInfo();
print_r($arr);

Error:

Array ( [0] => 21S01 [1] => 1136 [2] => Column count doesn't match value count at row 1 )

Answer 1

Prepared statements are not the same as copy and paste!

INSERT INTO `personnel` (:fields) VALUES (:data)

You're telling PDO/MySQL here that you want to insert exactly one piece of data (:data) into one field (:field). The value is one string containing commas, not several values separated by commas.

Furthermore you can only bind data, not structural information like field names. You will have to create a query like so:

INSERT INTO `personnel` (foo, bar, baz) VALUES (?, ?, ?)

and then bind data to the three placeholders separately.