I am developing a site using php, where I use functions to store data into database, but I had failed to use bindParam, can anyone help me out f this?

Question

I am developing a site using php, where I use a function to store data into database, but I failed to use bindParam function within my insert data function.

function insertData($table,$field,$data,$connection){

$c=8;
$param = "?";

$field_values= implode(',',$field);
$data_values=implode(',',$data);

$bind = $param.$data_values;

$sql='INSERT INTO '.$table.'('.$field_values.') VALUES('.$param.')';
$stm=$connection->prepare($sql);

  for ($i = 1; $i <= $c; $i++) {
    $stm->bindParam($i, $data_values);
  }

  if ($stm->execute()) {

  //return true;
  $last_id = $connection->lastInsertId();

  return array (true,$last_id);

  }
}

I have updated my code. I have tried to bind using for loop but have got an warning "PDOStatement::execute(): SQLSTATE[HY093]: Invalid parameter number: number of bound variables does not match number of tokens" — Sohon Shome, Sep 27 '19 at 16:49
https://www.w3schools.com/php/php_mysql_prepared_statements.asp "sss" may be confusing ;) — zod, Sep 27 '19 at 16:51

Nigel Ren · Answer 1 · 2019-09-27T17:45:09.300

1

One problem is that you are adding in $param for the bound values, but that is a single ?. You need 1 question mark for each bound value. So your statement looks like...

INSERT INTO tableName(id,name,desc) VALUES(?)

when it should look like

INSERT INTO tableName(id,name,desc) VALUES(?,?,?)

Also - with PDO, you can pass an array of the data values to the execute() method rather than binding each one. This code uses this method as I think it is simpler (more comments in code)...

function insertData($table,$field,$data,$connection){
    // Concatenate fields, add backticks to ensure names are used properly
    $field_values= implode('`,`',$field);
    // Create a list of params, 1 ? for each data field (remove last comma)
    $param=rtrim(str_repeat("?,", count($data)),",");

    $sql='INSERT INTO '.$table.'(`'.$field_values.'`) VALUES('.$param.')';
    $stm=$connection->prepare($sql);

    // Rather than bindParam - pass original data array to execute.
    if ($stm->execute($data)) {
        $last_id = $connection->lastInsertId();
        return array (true,$last_id);
    }
    // Return error
    return array(false, $connection->errorInfo());
}

edited Sep 27 '19 at 17:45

answered Sep 27 '19 at 16:57

Nigel Ren

56,122
11
43
55

It's not working too, its inserting all the value in every column. – Sohon Shome Sep 27 '19 at 17:12
Can you check what `$sql` contains and what `$data` contains. – Nigel Ren Sep 27 '19 at 17:13
$sql is fine and $data contain all the field values – Sohon Shome Sep 27 '19 at 17:19
Can you show the data in the table after the insert? – Nigel Ren Sep 27 '19 at 17:20
table after data insert, this a link of table data image view https://ibb.co/BZH5DgK – Sohon Shome Sep 27 '19 at 17:25
Not sure what is happening as I would assume that `$data` is an array containing something like `['small alu', 'brown', 'sweet potatoe'...]` - so 1 row of data and 1 value per element. – Nigel Ren Sep 27 '19 at 17:29

I am developing a site using php, where I use functions to store data into database, but I had failed to use bindParam, can anyone help me out f this?

1 Answers1