PDO Bulk Insert (Prepared Statements)

PHP
1

I’ve got a problem with a bulk insert with PDO using a prepared statement:

    public function insert_order_items($order_items) {
        try {
            $sql="
                INSERT INTO
                    ue_game_continent_order
                    (
                          user_id
                        , con_id
                        , type
                        , item
                        , number
                        , tick_start
                        , ticks_remaining
                        , status     
                    )
                VALUES 
            ";
            
            $insertQuery    = array();
            $insertData        = array();
            foreach ($order_items AS $row ) {
                $insertQuery[] = '(?,?,?,?,?,?,?,?)';
                $insertData[] = $row['user_id'];
                $insertData[] = $row['con_id'];
                $insertData[] = $row['type'];
                $insertData[] = $row['item'];
                $insertData[] = $row['number'];
                $insertData[] = $row['tick_start'];
                $insertData[] = $row['ticks_remaining'];
                $insertData[] = $row['status'];
            }
            
            if (!empty($insertQuery)) {
                $sql .= implode(', ', $insertQuery);
                $stmt = $this->db->prepare($sql);
                $stmt->execute($insertData);
            }
        }
        catch (PDOException $e) {
            error_log('Error reading the session data table in the session reading method.');
            error_log(' Query with error: '.$sql);
            error_log(' Reason given:'.$e->getMessage()."\n");
            return false;
        }
        die;
    }

It worked in PHP 5.5 but in PHP 7 it gives:

Warning: PDOStatement::execute(): SQLSTATE[HY000]: General error: 2031

I can’t think of what might have changed with PDO between version 5.5 of PHP and version 7 of PHP. My guess is that there is something about it that 5.5 lets you get away with but PHP 7 doesn’t

2

The following might help you?

public function create(array $records = NULL, $created_by = NULL, $urlDate, $page) {
    $this->records = $records;
    if (is_array($this->records)) {
        
        $pdo = $this->myPDO();

        $this->sql = 'INSERT INTO calendar (created_by, security_level, date, time, name, info) VALUES ';

        foreach ($this->records as $this->record) {
            $this->query[] = '(:created_by' . $this->n . ', :security_level' . $this->n . ', :date' . $this->n . ', :time' . $this->n . ', :name' . $this->n . ', :info' . $this->n . ')';

            $this->record = (object) $this->record;
            $this->iData['created_by' . $this->n] = $created_by;
            $this->iData['security_level' . $this->n] = 'sysop';
            $this->iData['date' . $this->n] = $this->record->date;
            $this->iData['time' . $this->n] = $this->record->time;
            $this->iData['name' . $this->n] = $this->record->name;
            $this->iData['info' . $this->n] = trim($this->record->info);

            $this->n += 1;
        }

        if (!empty($this->query)) {
            $this->sql .= implode(', ', $this->query);
            $this->stmt = $pdo->prepare($this->sql);
            $this->result = $this->stmt->execute($this->iData);
        }
    }
    header("Location: " . $this->myPage . "?urlDate={$urlDate}&page={$page}");
    exit();
}
3

I can’t seem to replicate this issue. Could you perhaps provide more information on this, such as the attribute types of the ue_game_continent_order relation and what data you’re attempting to insert?

4

This is a var_dump of $order_items

array (size=2)
      0 => 
        array (size=8)
          'user_id' => int 1
          'con_id' => int 1
          'type' => string 's' (length=1)
          'item' => int 21
          'number' => int 1
          'tick_start' => int 31
          'ticks_remaining' => int 4
          'status' => string 'b' (length=1)
      1 => 
        array (size=8)
          'user_id' => int 1
          'con_id' => int 1
          'type' => string 's' (length=1)
          'item' => int 22
          'number' => int 1
          'tick_start' => int 31
          'ticks_remaining' => int 4
          'status' => string 'b' (length=1)

A show create table for the table:

CREATE TABLE `ue_game_continent_order` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL,
  `con_id` int(11) NOT NULL,
  `type` varchar(255) NOT NULL,
  `item` int(11) NOT NULL,
  `number` int(11) NOT NULL,
  `att_boost` int(11) NOT NULL,
  `def_boost` int(11) NOT NULL,
  `tick_start` int(11) NOT NULL,
  `ticks_remaining` int(11) NOT NULL,
  `status` varchar(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
5

I still cannot replicate your issue. When running your script on the above table definition and data, I simply get a SQLSTATE[HY000]: General error: 1364 error because both the att_boost and def_boost attributes cannot be null (since you’re not specifying them in your insert query). Making them optionally null works fine.

Does that fix your issue? If not, then what database are you using? I’m testing on MySQL (v5.7).

6

Maybe DEFAULT 0 would work?

In any case it looks like you will need to change the query code or run an ALTER

My guess would be NULL to empty coercion

7

@tpunt

I’m using MySQL version 5.7.9, PHP version 7.0 (it’s installed via WAMP3). Are you using individual installs? I’m wondering if it could be a bug/glitch specific to WAMP3

I’ve set the default for the att_boost and def_boost fields to NULL as there won’t always be a value for them

8

Yes. I prefer to avoid being tied to pre-bundled stacks because I like the flexibility of being able to choose what software versions I run on.

How did you get them to default to NULL? Your above table definition says they cannot be NULL.

It could be an issue related to WAMP or it could be an issue related to Windows (since I’m running on Mac OS X and can’t seem to replicate it), so I can’t really help anymore. Sorry!

9

I set the default via PHPMyAdmin. I’m going to hopefully try WAMP3 on a windows 7 computer over the Easter weekend, to see if that makes any difference

10

Starting with a pre-bundled stack doesn’t prevent you upgrading the individual components afterwards - I actually find that’s the easiest alternative for both install (stack) and upgrade (individual).

11

Got WAMP3 installed on the Windows 7 machine, now just need to get PHPMyAdmin to stay logged infor long enough for me to be able to do anything

12

Tried it on the Windows 7 computer, I get the same error for running both PHP 5.6 and PHP 7 so I’m starting to suspect it might be a bug in WAMP 3

13

Got it working! It looks like it was down to how I had the PDO Statement class extended (done so so that I can keep an eye on the number of queries run), i had:

    public function execute($bound_input_params = NULL) {
        $GLOBALS['query_count']++;

        ++$this->query_count;
        
        return parent::execute($bound_input_params = NULL);
    }

I created a new function in the PDO Statment extended class:

    public function bulk_insert($data) {
        $GLOBALS['query_count']++;

        ++$this->query_count;
        
        return parent::execute($data);
    }

I also typecast each variable (something that I really need to finish off doing everywhere). It seems like PDO is stricter possibly after version 5.5 of PHP, when it comes to extending the Statement object

14

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.