Prepared statement...inserting many rows

PHP
1

I am trying to insert many rows(6) in a table with a prepared statement.

here is the syntax(only 2 rows insertions…for clarity)

 if($stmt=$connection->prepare('INSERT INTO store_open (b_user_ID,open_time,close_time,day) 
   VALUES(?,?,?,?),(?,?,?,?)'))

Despite the INSERT syntax for inserting many rows in a table is the above…I do not think this will with a prepared statement…cause when I am going to use bind_param…which variable is going to be associated with what question mark symbol…I hope you understand what I mean.

What might be the solution to it…a for loop?
Since I am going to be inserting 6 rows at a time…that means making 6 INSERTs…something not so efficient I think…

What do you propose?

2

Are you using the mysqli_* extension or PDO?

3

I am using mysqli

4

So what’s the use case? Why would someone be entering six rows into this table at a time?

5

Same for PDO, just loop through the bindParam & excecute, it’s the prepare statement you wan’t to avoid.
This guy gives a good explanation if you want to take it a step further :
http://stackoverflow.com/questions/19512498/mysqli-multiple-row-insert-simple-multi-insert-query

6

I made a calendar with the ability to make appointments on any given day and developed this as a way of entering 7-8 rows of time. It’s a crazy way of doing it, but it does use prepared statements:

  public function create(array $records=NULL, $created_by = NULL) {
    $this->records = $records;
    if (is_array($this->records)) {
      $db = Database::getInstance();
      $pdo = $db->getConnection();
      
      $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] = $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);
      }            
      
    }
  } // End of create method/function:

It uses PDO and is written in OOP fashion, but I don’t think it would be to hard to convert to either procedural or mysqli? I know I converted this from procedural to OOP.

7

the only problem in my case is that the data I want to insert to the database are contained in a multidimensional array and it is a little hard looping through it.

9

Doing one insert or six, you still have to loop through the array data, don’t you? So that shouldn’t make any difference?

10

I will try to be more specific about the array…
It is actually the weekdays(7 array members)…and its day holds two other array members…these are 2 24H times…it could be 14:00:00 and 17:00:00 for example.

In essence the array holds info about the schedule(open/close time) of a store…

Here is the code so far:

if($stmt=$connection->prepare('INSERT INTO store_open (b_user_ID,open_time,close_time,day)VALUES(?,?,?,?)'))
   foreach ($content as $key => $value) {
     $stmt->bind_param('isss',$b_user,$key);  
     $stmt->execute();
}

b_user_ID is not contained in the array…there is no problem with it…
As you can understand from the above the array that holds the weekdays is $content

11

Here is an example of a bulk-insert usisng prepared statements. It uses PDO but it shouldn’t be very hard to convert to mysqli: (yes I know I need to add more validation to my own code)

try {
                $sql="            
                    INSERT INTO
                        ue_conversation_participant
                        (
                              conversation
                            , user
                            , deleted
                            , unread
                        )
                    VALUES
                ";
                $insertQuery    = array();
                $insertData        = array();
                foreach ( $new_recipient_ids AS $new_recipient_id ) {
                    $insertQuery[] = '(?,?,?,?)';
                    $insertData[] = $_POST['pm_id'];
                    $insertData[] =  $new_recipient_id['id'];
                    $insertData[] = 0;
                    $insertData[] = 1;
                }
                
                if (!empty($insertQuery)) {
                    $sql .= implode(', ', $insertQuery);
                    $stmt = $this->db->prepare($sql);
                    $stmt->execute($insertData);
                }
                
            }
            catch (PDOException $e) {
            $this->db->rollBack();
                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;
            }
        }

The above example is used to insert as many participants as needed to a conversation in a PM system. The rollback is there because it’s forming part of a transaction

1 Like
12

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