PDO bindParam issue

Question

Possible Duplicate:
Can PHP PDO Statements accept the table name as parameter?

I have a function in my class which is doing some trouble. Here the function

function insert($table,$column = array(),$value = array())
{
    $array1 = implode(",", $column);
    $array2 = implode(",", $value);

    try 
    { 
        $sql = $this->connect->prepare("INSERT INTO :table (:date1) VALUES (:date2)");  
        $sql->bindParam(':table',$table, PDO::PARAM_STR);
        $sql->bindParam(':data1',$array1, PDO::PARAM_STR);
        $sql->bindParam(':data2',$array2, PDO::PARAM_STR);

        $sql->execute();

    }  
    catch(PDOException $e) 
    {  
        echo $e->getMessage();  
    }  
}

I call the function with:

-> insert('coupons',array('categorie','name','link','code','id'),array('test11','test','test','test','NULL'));

The error I get is :

Warning: PDOStatement::execute() [pdostatement.execute]: SQLSTATE[HY093]: Invalid parameter number: parameter was not defined in C:\xampp\htdocs\MYFRAMEWORK\lib\database.class.php on line 46

Line 46 is :

$sql->execute();

So now I don't really see where the issue is. Any pointers?

Answer 1

PDOs bind value data, not table and column names.

You are misunderstanding the use of bindings. You cannot bind table and column names with PDO. You bind data to insert INTO those columns. You need to construct the SQL to include the table names and columns using string operations.

Format the data

I've renamed your $column and $value to $column_array, $value_array to make it clear what they are, and assumed that each is a simple array: $column_array = array('column1', 'column2', ...) etc.

$placeholders = array_map(function($col) { return ":$col"; }, $column_array);

$bindvalues = array_combine($placeholders , $value_array);

$placeholders now looks like this:

$placeholders = array(
        ':column1',
        ':column2',
         ...
    );

$bindvalues now looks like this:

$bindvalues = array(
        ':column1'=>'value1',
        ':column2'=>'value2',
         ...
    );

Build, prepare, execute

$sql = $this->connect->prepare("INSERT INTO $table (" .implode(",", $column_array) .") VALUES (". implode(",", $placeholders) . ")";

This will give you a prepared statement of the form:

$sql = INSERT INTO table_name (column1, column2, ...) VALUES (:column1, :column2, ...)

You can then execute the prepared statement and pass the $values as an argument.

$sql->execute($bindValues);

Note:

• One caveat that must be mentioned. Make sure that your original data has been sanitized against SQL Injection. PDO's take care of that for the bound values, but if you are constructing the columns from, say, $_POST data this is vulnerable and needs to be sanitized.

Answer 2

The query is not constructed properly, the values are missing surrounding ' quotes.

When you do an implode, the array2 looks like,

 test1,test,test... //and so on.

It needs to be 'test1','test','test'... in-order to be properly binded inside the Insert query.

$sql = $this->connect->prepare("INSERT INTO :table (:date1) VALUES (:date2)"); 

Also, you have typos in the parameter names, date1 instead of data1 and date2 instead of data2.