PDO Query Not Inserting - HY093 error message but correct number of bound variables

Question

Code

$dbh->setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_WARNING );
$stmt = $dbh->prepare($query);
print_r($fullStmt);

if(!$stmt->execute($fullStmt))
{
    print_r($stmt->errorInfo());
    $full_query = "INSERT INTO `fixtures` (competition_code,competition_id,competition_name,season_id,season_name,
    timestamp,uid,last_modified,matchday,period,matchwinner,date,team1,team1_halfscore,team1_score,team1_goals,
    team1_side,team2,team2_halfscore,team2_score,team2_goals,team2_side) VALUES (";
    foreach($fullStmt as $val){ $full_query.= "'$val', "; }
    $full_query = trim($full_query, ", ");
    $full_query.= ");";
    exit($full_query);
}

Output

Array
(
    [competition_code] => EN_PR
    [competition_id] => 8
    [competition_name] => English Barclays Premier League
    [season_id] => 2013
    [season_name] => Season 2013/2014
    [timestamp] => 2013-10-30 09-03-49
    [uid] => g695281
    [last_modified] => 2013-10-15T12:35:58+00:00
    [matchday] => 1
    [period] => FullTime
    [matchwinner] => t7
    [date] => 2013-08-17 15:00:00 BST
    [team1] => t3
    [team1_halfscore] => 1
    [team1_score] => 1
    [team1_goals] => p44346/#/Goal
    [team1_side] => Home
    [team2] => t7
    [team2_halfscore] => 1
    [team2_score] => 3
    [team2_goals] => p54861/#/Goal//p83564/#/Goal//p54861/#/Penalty
    [team2_side] => Away
)
Array
(
    [0] => HY093
    [1] => 
    [2] => 
)
INSERT INTO `fixtures` (competition_code,competition_id,competition_name,season_id,season_name,
            timestamp,uid,last_modified,matchday,period,matchwinner,date,team1,team1_halfscore,team1_score,team1_goals,
            team1_side,team2,team2_halfscore,team2_score,team2_goals,team2_side) VALUES ('EN_PR', '8', 'English Barclays Premier League', '2013', 'Season 2013/2014', '2013-10-30 09-03-49', 'g695281', '2013-10-15T12:35:58+00:00', '1', 'FullTime', 't7', '2013-08-17 15:00:00 BST', 't3', '1', '1', 'p44346/#/Goal', 'Home', 't7', '1', '3', 'p54861/#/Goal//p83564/#/Goal//p54861/#/Penalty', 'Away');

Overview

$fullStmt is an array of values, and I have a query as follows:

$query = "INSERT INTO `fixtures` (
                competition_code,
                competition_id,
                competition_name,
                season_id,
                season_name,
                timestamp,
                uid,
                last_modified,
                matchday,
                period,
                matchwinner,
                date,
                team1,
                team1_halfscore,
                team1_score,
                team1_goals,
                team1_side,
                team2,
                team2_halfscore,
                team2_score,
                team2_goals,
                team2_side
                ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";

However, when trying to execute, it returns FALSE. I output the resulting query and when directly inserting it into phpMyAdmin it inserts successfully.

Why does it insert without issue when I run the code in the SQL field in phpMyAdmin but not when executing in PHP?

You're set for warnings - do you have `error_reporting(E_ALL);` on right now with `display_errors`? I'm curious if `errorInfo()` is actually populated _before_ `execute()`, after attempting `prepare()` to know if the statement was successfully prepared in the first place. Inspect `errorInfo()` after `prepare()` (or turn on `ERRMODE_EXCEPTION` temporarily, so the code never advances past `prepare()` if it fails) — Michael Berkowski, Oct 07 '15 at 13:19
@MichaelBerkowski Turning on PHP errors provided: `PDOStatement::execute(): SQLSTATE[HY093]: Invalid parameter number: parameter was not defined` pointing to the `execute()` function. It's still unclear what parameter is not being bound, as it works fine in phpMyAdmin and I can't find any unbound parameters? — Ben, Oct 07 '15 at 13:21
If I recall, when using `?` placeholders instead of named ones, PDO would discard the array keys in `execute()` on its own, but if that isn't the case, you should also try `execute(array_values($fullStmt))` to get rid of the associative keys before binding. — Michael Berkowski, Oct 07 '15 at 13:21
Yes, I just tested it too to find that's PDO's behavior. Answer coming up. — Michael Berkowski, Oct 07 '15 at 13:24
Wow there's a bit of unchecked aggression there @RiggsFolly, especially considering I put it in the post - directly under "Overview"... — Ben, Oct 07 '15 at 13:27

Michael Berkowski · Accepted Answer · 2015-10-07T13:36:45.637

4

I wasn't certain of this behavior in PDO until testing for myself, but since your array of values in $fullStmt is an associative array, PDO is in fact making an attempt to bind named parameters based on its array keys. Your originally prepared statement uses positional ? placeholders, so the named parameters are not present (and they cannot be mixed with ?).

So you need to eliminate the array keys for PDO to correctly bind the array values with their positional placeholders. That's most easily done by calling array_values() on the array as it is passed to execute().

// Strip off the associative array keys...
if(!$stmt->execute(array_values($fullStmt))) {
   // etc
}

Note that PDO's correct interpretation of the array's order depends on its values being in exactly the correct order to begin with. Your $fullStmt array does happen to be in the correct order by whatever means you've produced it. If that process changes however, stripping off the array keys may result in your INSERT statement placing values into the wrong columns. It may be worth the effort to refactor your statement generation to use named parameters like :competition_code in the VALUES () list and continue using the associative array to protect against this potential tripping point.

edited Oct 07 '15 at 13:36

answered Oct 07 '15 at 13:29

Michael Berkowski

267,341
46
444
390

Actually if the order of this array changes i.e. you move a fields about in your HTML you will likely be trying to put data in the wrong column. I would suggest building the column list dynamically as well as the data list so this does not catch you out later. – RiggsFolly Oct 07 '15 at 13:32
@RiggsFolly I agree with that assessment. I will add a note to that effect. – Michael Berkowski Oct 07 '15 at 13:33
@RiggsFolly In this situation, the data is created dynamically from several other arrays - they will always be in this order, whether the values are filled, empty or NULL, but you raise a good point. – Ben Oct 07 '15 at 13:35
Maybe named parameters and a `bindValue` methodology would make the code more `change safe`. – RiggsFolly Oct 07 '15 at 13:37
@MichaelBerkowski As for *the latter* part; that question fell under 3 different dupes. Both of which have been posted under it. I couldn't pick all 3; robots are only as good as the *person* who hardcoded their program ;-) Edit: Ah, you deleted that comment about the other question. – Funk Forty Niner Oct 07 '15 at 13:37
1

@BenPearlKahan _they will always be in this order_ Famous last words of the drowning man. **The titanic is unsinkable** What is someone esle gets the job of amending the code?? – RiggsFolly Oct 07 '15 at 13:39
@RiggsFolly Someone's aggressive today! It's my own personal project, I'm the one maintaining and amending, and all the imported data is also managed, organised and sorted by me. That's why I'm confident that the array will be in this order. – Ben Oct 07 '15 at 13:53
1

@BenPearlKahan Not aggressive, just trying to pass on years of experience of being tripped up by assumptions.made days/months/years before that change over time i.e. bin there, made incorrect assumptions, paid the price. Both feet have been shot, more than once. – RiggsFolly Oct 07 '15 at 14:29

PDO Query Not Inserting - HY093 error message but correct number of bound variables

Code

Output

Overview

1 Answers1