WordPress.org

Ideas

Allow $wpdb->insert() to add multiple rows in one query

  1. David Gard
    Member

    I recently came across a scenario where I had to place upwards of 300 rows at a time into a database table. Initially, I was using $wpdb->insert(), this was pretty slow as it ran a separate query to insert each row.

    It would be good to be able to pass an array of associative arrays to $wpdb->insert() (as they'd all have the required $field names required by the helper function), and let the function insert all of your rows in one go.

    Posted: 3 years ago #
  2. Arevico
    Member

    12345

    Good iea, actually SQL supports this too so i think it is a good plan to add support for it :
    http://stackoverflow.com/questions/2624713/how-do-i-insert-multiple-rows-without-repeating-the-insert-into-dbo-blah-part

    Posted: 3 years ago #
  3. mirzazeyrek
    Member

    try this:

    <?php
    
    	/**
         * A method for inserting multiple rows into the specified table
         *
         *  Usage Example:
    	 *
         *  $insert_arrays = array();
         *  foreach($assets as $asset) {
         *
         *  $insert_arrays[] = array(
         *  'type' => "multiple_row_insert",
         *  'status' => 1,
         *  'name'=>$asset,
         *  'added_date' => current_time( 'mysql' ),
         *  'last_update' => current_time( 'mysql' ));
         *
         *  }
         *
         *  wp_insert_rows($insert_arrays);
    	 *
         *
         * @param array $row_arrays
    	 * @param string $wp_table_name
         * @return false|int
         *
         * @author	Ugur Mirza ZEYREK
         * @source http://stackoverflow.com/a/12374838/1194797
         */
    
        function wp_insert_rows($row_arrays = array(), $wp_table_name) {
            global $wpdb;
            $wp_table_name = esc_sql($wp_table_name);
            // Setup arrays for Actual Values, and Placeholders
            $values = array();
            $place_holders = array();
            $query = "";
            $query_columns = "";
    
            $query .= "INSERT INTO {$wp_table_name} (";
    
                    foreach($row_arrays as $count => $row_array)
                    {
    
                        foreach($row_array as $key => $value) {
    
                            if($count == 0) {
                                if($query_columns) {
                                $query_columns .= ",".$key."";
                                } else {
                                $query_columns .= "".$key."";
                                }
                            }
    
                            $values[] =  $value;
    
                            if(is_numeric($value)) {
                                if(isset($place_holders[$count])) {
                                $place_holders[$count] .= ", '%d'";
                                } else {
                                $place_holders[$count] .= "( '%d'";
                                }
                            } else {
                                if(isset($place_holders[$count])) {
                                $place_holders[$count] .= ", '%s'";
                                } else {
                                $place_holders[$count] .= "( '%s'";
                                }
                            }
                        }
                                // mind closing the GAP
                                $place_holders[$count] .= ")";
                    }
    
            $query .= " $query_columns ) VALUES ";
    
            $query .= implode(', ', $place_holders);
    
            if($wpdb->query($wpdb->prepare($query, $values))){
                return true;
            } else {
                return false;
            }
    
        }

    source:

    https://github.com/mirzazeyrek/wp-multiple-insert

    Posted: 9 months ago #

RSS feed for this topic

Reply

You must log in to post.

  • Rating

    12345
    4 Votes
  • Status

    This idea is under consideration