PDO insert query not working PHP

Question

I'm using the following code to insert a bunch of records into a sqlite database:

try {
    $dir = 'sqlite:file_name';
    $dbh  = new PDO($dir) or die("cannot open the database");
    $dbh->setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_WARNING );

    $query_str = 'DELETE FROM mytable; INSERT INTO mytable (field1, field2, field3, field4, field5, field6, field7, field8, field9) VALUES ';
    $query = $dbh->prepare( $query_str . $valuesPlaceholders);
    $sqlResponse = $query->execute($valuesArr);
}
catch (PDOException $e) {
    if ($e->getCode() == 1062) {
        echo 'here';
    } else {
        throw $e;
}

This is the $valuesPlaceholders:

(?, ?, ?, ?, ?, ?, ?, ?, ?),(?, ?, ?, ?, ?, ?, ?, ?, ?),(?, ?, ?, ?, ?, ?, ?, ?, ?)

This is my $valuesArr:

Array
(
    [0] => Array
        (
            [0] => temperature
            [1] => M1
            [2] => 40110
            [3] => 100
            [4] => 500
            [5] => 200
            [6] => 300
            [7] => 1
            [8] => C
        )

    [1] => Array
        (
            [0] => humidity
            [1] => M1
            [2] => 40114
            [3] => 100
            [4] => 500 
            [5] => 200
            [6] => 300
            [7] => 1
            [8] => %
        )

    [2] => Array
        (
            [0] => param111
            [1] => M2
            [2] => 40115
            [3] => 100.5
            [4] => 500
            [5] => 200
            [6] => 300
            [7] => 0.1
            [8] => uni
        )

)

This gives me the following errors:

Array to string conversion

PDOStatement::execute(): SQLSTATE[HY000]: General error: 25 column index out of range

The table structure consists of 10 columns, including 1 id column, which is auto increment.

What am I doing wrong ?

@RiggsFolly I checked this out: https://stackoverflow.com/questions/1176352/pdo-prepared-inserts-multiple-rows-in-single-query — mrid, Jun 19 '18 at 12:48
You are attempting to run **TWO** queries 1) The **DELETE** and 2) the **INSERT** — RiggsFolly, Jun 19 '18 at 12:49
I have even tied using a 1D array, even that gives index out of range error — mrid, Jun 19 '18 at 12:50
`Array to string conversion` definitely indicates that `$valuesPlaceholders` is array. — u_mulder, Jun 19 '18 at 12:51
@u_mulder this error is occurring on `execute()`, not on `prepare()`. $valuesPlaceholders is a string only — mrid, Jun 19 '18 at 12:53
@u_mulder , is it correct that the execute function requires a single dimensional array with strings or numbers in them, but he is inputting array with arrays in them, so it tries to convert each inner array to string. Because this is what it wants: single dimensional array with either numbers or strings, not arrays? — Sugumar Venkatesan, Jun 19 '18 at 13:35

Sugumar Venkatesan · Answer 1 · 2018-06-19T13:37:24.957

****your function requires single dimensional array with strings or numbers in them, because you have multidimensional array, it tries to convert your inner arrays to strings so you get the error array to string conversion****

   Convert the values to single array,

    $values_str =''; 
    $i = 0;
    foreach($valuesArr as $ar){
      if($i > 0)$values_str.=',';
      $values_str .= implode(',',$ar);
      $i++;
    }
    $values_str = explode(',', $values_str);
    //I dont know whether you would need this array_map or not
    array_map('strin_function', $values_str);
    function strin_function($val){
      return "'".$val."'";
    }
    $valuesArr = $values_str;
    var_dump($valuesArr);

score 0 · Answer 2 · answered Jun 20 '18 at 10:28

You've linked to another question in your comment, but you seem to have missed why the accepted answer works and your code doesn't.

Specifically, the answer shows the creation of a new 1D array that is passed to the execute method:

...
foreach($data as $d){
    $question_marks[] = '('  . placeholders('?', sizeof($d)) . ')';
    $insert_values = array_merge($insert_values, array_values($d));
}
...

You haven't done this in your code and that's why it fails.

Unless you have a demonstrable need to use the above multi-insert technique I would recommend sticking to the standard methods:

$dbh->beginTransaction();
$dbh->exec('DELETE FROM mytable');
$query = $dbh->prepare(
    'INSERT INTO mytable (field1, field2, field3, field4, field5, field6, field7, field8, field9) VALUES (?,?,?,?,?,?,?,?,?)'
);
foreach ($valuesArr as $arr) {
    $query->execute($arr);
}
$dbh->commit();

You should also change $dbh->setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_WARNING ); to $dbh->setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION ); or your try/catch block is useless as PDO won't throw exceptions.

score -2 · Answer 3 · answered Jun 19 '18 at 12:46

Try this syntax, It will work 100%.

<?php
    $servername = "localhost";
    $username = "username";
    $password = "password";
    $dbname = "myDBPDO";

    try {
        $conn = new PDO("mysql:host=$servername;dbname=$dbname", $username, $password);
        // set the PDO error mode to exception
        $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
        $sql = "INSERT INTO MyGuests (firstname, lastname, email)
        VALUES ('John', 'Doe', 'john@example.com')";
        // use exec() because no results are returned
        $conn->exec($sql);
        echo "New record created successfully";
        }
    catch(PDOException $e)
        {
        echo $sql . "<br>" . $e->getMessage();
        }

    $conn = null;
    ?>

How exactly is this syntax going to help with this question? — Dimitris Filippou, Jun 19 '18 at 12:47
So you think the OP should just forget about doing the `DELETE FROM mytable;` — RiggsFolly, Jun 19 '18 at 12:48

PDO insert query not working PHP

3 Answers3