PDO prepared statement with dynamic variables

Question

I want to create a function that can insert data into database using PDO prepared statement

public function test() {
  $this->insert([
    'first_name' => $_POST['first_name'],
    'last_name' => $_POST['last_name'],
    'email' => $_POST['email']
  ]);
}


public function insert(array $data) {

  $fields = '';
  $bindValues = '';
  $values = [];

  foreach($data as $k => $v) {
    $fields .= $k . ', ';
    $bindValues .= ':' . $k . ', ';
    $values[$k] = $v; 
  }

  $fields = rtrim($fields, ', ');
  $bindValues = rtrim($bindValues, ', ');

  $insert = $this->db->prepare("
    INSERT INTO
      :table
    (:fields)
    VALUES 
      (:values)
  ");

  $insert->execute([
    'table' => $this->table,
    $values
  ]);  
}

dumped variables: 
1. $fields
2. $bindValues 
3. $values


'first_name, last_name, email' (length=28)
':first_name, :last_name, :email' (length=31)
array (size=3)
  'first_name' => string 'Nikola' (length=6)
  'last_name' => string 'Misic' (length=5)
  'email' => string 'mail@mail.com' (length=13)

And I'm getting an error

Call to a member function execute() on boolean

There's something wrong with the SQL, and I don't know what or how to debug it.

You can't bind tables or columns. A binding should have 1 value also. — chris85, Jun 11 '17 at 16:05

score 0 · Accepted Answer · answered Jun 11 '17 at 16:07

0

You need to build the SQL using something like...

$insert = $this->db->prepare("
    INSERT INTO
      {$this->table}
    ($fields)
    VALUES 
      ($bindValues)
  ");

answered Jun 11 '17 at 16:07

Nigel Ren

56,122
11
43
55

Yeah ok, I did it already but I thought I could do it using prepare function. I didn't know that you can't bind tables or columns. Thank you. – mishke Jun 11 '17 at 16:09
You should be really careful about letting user input into the query - the whole point of using prepared statements is gone then – Qirel Jun 11 '17 at 16:35

PDO prepared statement with dynamic variables

1 Answers1