CODEWITHSUNDEEP
phpooppdo
user9741470
user9741470

Reputation:

PHP PDO Dynamic query builder class

I'm writing a class to generate dynamic SQL insert query. I've created a test table that has seven columns if I consider also the id that is an auto increment value otherwise the values are only six. I've tried to test if the insert function I've write works, but the console log me an error that is regarding the numbers of bounded variables: PHP Warning: PDOStatement::execute(): SQLSTATE[HY093]: Invalid parameter number: number of bound variables does not match number of tokens. Usually I use to write my query and this is the first time that I get this error. Is it possible that I need to include also the id column field in the query? Here is the code:

Test page

<?php
$dataManager = new DataManager($db);

$table = 'hats_info';
$data = array('test0','test1','test2','test3','test4','test5');
$columns = array('materialA','materialB','materialC','materialD','accessories','hat_image');
$holders = array('?','?','?','?','?','?');

echo $dataManager->insert($data, $table, $columns, $holders);

?>

Class method code 

<?php 

public function insert($table, array $data, array $columns , array $holders){

    $columns = implode(', ', array_values($columns));
    $holders = implode(', ', array_values($holders));
    $values = implode(', ', array_values($data));

    $stmt = $this->db->prepare("INSERT INTO $table ($columns) VALUES ($holders)");
    return $stmt->execute(array($values));
}

?>

Upvotes: 1

Views: 1498

Answers (1)

Bill Karwin
Bill Karwin

Reputation: 562310

I've done functions like this in the following way:

$table = 'hats_info';

$data = [
  'materialA' => 'test0',
  'materialB' => 'test1',
  'materialC' => 'test2',
  'materialD' => 'test3',
  'accessories' => 'test4',
  'hat_image' => 'test5'
];

echo $dataManager->insert($table, $data);

public function insert($table, array $data) {
    $columns_delimited = implode(', ' array_map(
        function ($column) { return "`$column`"; },
        array_keys($data)
    ));
    $placeholders = implode(', ', array_fill(1, count($data), '?'));
    $sql = "INSERT INTO `$table` ($columns_delimited) VALUES ($placeholders)"
    $stmt = $this->db->prepare($sql);
    return $stmt->execute(array_values($data));
}

Upvotes: 1

Related Questions