CODEWITHSUNDEEP
phppdo
user2547576
user2547576

Reputation: 117

INSERT array - PDO

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 )

Upvotes: 0

Views: 1842

Answers (2)

jeroen
jeroen

Reputation: 91734

You cannot do that:

  • You need to add each variable / field-name and value individually;
  • You can only bind values and not table- or field-names.

Table- and field-names you will have to inject directly into your sql so to prevent sql injection problems, you need to check them against a white-list before doing that.

So in your case that would be something like (rough draft):

// assuming all fields have been checked against a whitelist
// also assuming that the array keys of `$habbo_data` do not contain funny stuff like spaces, etc.
$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` ({$fields}) VALUES ({$fields_data})";
$result = $con->prepare($query);
$result->execute($habbo_data);

Note that I am not manually binding the variables any more but sending the associative $habbo_data array directly as a parameter to the execute method, see example #2.

Upvotes: 0

deceze
deceze

Reputation: 522016

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.

Upvotes: 1

Related Questions