I am getting an error when inserting with execute($array)

Question

I have a table

percentile      int(3)  No           
FoSW            int(3)  Yes     NULL     
dfLR            int(3)  Yes     NULL     
FoA             int(3)  Yes     NULL     
SoG             int(3)  Yes     NULL     
RST             int(3)  Yes     NULL     
SSW             int(3)  Yes     NULL    
total           int(3)  No

and an array:

Array
(
    [percentile] => 99
    [FoSW] => 125
    [DfLR] => 110
    [FoA] => 60
    [SoG] => 120
    [RST] => 40
    [SSW] => 45
    [total] => 500
)

And this code that does not work for some reason... Catch does not throw an error. Just my if statement which echos error...

if ($_POST['percent']=='add'){
    try{
        $post = $_POST;
        unset($post['percent']);

        $sth = $dbh->prepare("INSERT INTO percentiles (percentile, FoSW, dfLR, FoA, SoG, RST, SSW, total) VALUES (?,?,?,?,?,?,?,?)");

        if ($sth->execute($post)){
            echo 'done<br/>';
        }

        else echo 'error';
    }

    catch(PDOException $e){
        echo 'error'.$e;
    }
}

You're effectively assigning $_POST to your placeholders - are there 8 fields in $_POST? Are they in the eight order? — andrewsi, Jul 17 '13 at 19:49
yes, as you can see in the array i showed there are 8 items in the array and in the right order. I unset the one item that should not be there. — Smith Smithy, Jul 17 '13 at 19:51
The [documentation](http://php.net/manual/en/pdostatement.execute.php) on PDOStatement->Execute doesn't say that it throws an exception. Are you certain that it **should** be? — Mr. Llama, Jul 17 '13 at 19:51
no i am not certain that it should throw an exception, but it is not inserting and I can not figure out why. — Smith Smithy, Jul 17 '13 at 19:52
@SmithSmithy - I can see an associative array, that has 8 key-value pairs. That's 16 elements. You might be wanting to get just the values of $_POST; but it'll be a lot easier to maintain if you explicitly assign values. If you change your form in the future, this code will break - even if you move a couple of fields around. — andrewsi, Jul 17 '13 at 19:53
try `$sth->errorcode()` and `$sth->errorInfo()`. They will contain error information when execute returns false. Execute doesn't throw an exception. — GolezTrol, Jul 17 '13 at 19:54
see also http://stackoverflow.com/a/15990858/689579 about getting PDO error messages. — Sean, Jul 17 '13 at 19:55
@andrewsi can you show me what to do with the $post array to correct. I understand what you are saying about maintaining the code, but I still want to see this script work. — Smith Smithy, Jul 17 '13 at 19:58
@SmithSmithy - you can get away with `$sth->execute(array($_POST['percentile], $_POST['FoSW'].....`, explicitly naming each $_POST field in the execute. — andrewsi, Jul 17 '13 at 20:01
If you have php>=5.2 No - from http://php.net/manual/en/pdostatement.execute.php `Changelog :: The keys from input_parameters must match the ones declared in the SQL. Before PHP 5.2.0 this was silently ignored.` — Sean, Jul 17 '13 at 20:17
@sean so i need to convert it to an indexed array and throw away the keys correct? — Smith Smithy, Jul 17 '13 at 20:20
either an un-indexed array, or need to use `$sth->bindParam()` if wanting to index. see example #3 & #4 - http://php.net/manual/en/pdostatement.execute.php — Sean, Jul 17 '13 at 20:22

score 1 · Accepted Answer · answered Jul 17 '13 at 21:00

Your array $post matches the eight values required for INSERT operation, but the array should be indexed by integers and not associative array/dictionary.

Array
(
    [percentile] => 99
    [FoSW] => 125
    [DfLR] => 110
    [FoA] => 60
    [SoG] => 120
    [RST] => 40
    [SSW] => 45
    [total] => 500
)

The above array will work if you change your prepare() call as follows:

$sth = $dbh->prepare("INSERT INTO percentiles 
        (percentile, FoSW, 
        dfLR, FoA, 
        SoG, RST, 
        SSW, total) 
    VALUES
        (:percentile, :FoSW, 
        :DfLR, :FoA, 
        :SoG, :RST, 
        :SSW, :total)");

I am getting an error when inserting with execute($array)

1 Answers1