$wpdb->insert - inserting multiple rows

Question

How do I use $wpdb->insert to insert multiple rows. Here is my code.

for($i=0; $i<=$urlCount; $i++) {
   $stat = $wpdb->insert(
        'WP_URLS',
        array(
            'POSTID' => $post->ID,
            'URL' =>   $_POST['url'.$i]
        )
    );
}

This code works only for the first insert (ie, when $i = 0 , when value is url_0 ). Sometimes the URL count will be more than 100 and sometimes it will be zero. So instead of writing the code for 100 times, I just want to have a simple loop that works for any number of records. Thats the reason I went for loop.

Thank you for the help.

The table structure is

CREATE TABLE WP_URLS (
   POSTID BIGINT NOT NULL,
   URL VARCHAR(254)
);

What is the structure of your table? I see no problem in your code. — Ján Bočínec
– Ján Bočínec, Commented Sep 2, 2011 at 6:52
You might want to use $wpdb->prepare() for security reasons. See Codex. — kaiser
– kaiser, Commented Sep 2, 2011 at 13:26
CREATE TABLE WP_URLS ( POSTID BIGINT NOT NULL, URL VARCHAR(254) ); @johnnyPea — Albin Joseph
– Albin Joseph, Commented Sep 3, 2011 at 4:27
@kaiser the $wpdb->insert() escapes the data as per the documentation. Still do we need to use $wpdb->prepare() ? — Albin Joseph
– Albin Joseph, Commented Sep 3, 2011 at 4:34
@Albin Joseph You're right. I just took a look at the core and both insert & replace use the prepare fn. — kaiser
– kaiser, Commented Sep 3, 2011 at 21:52

kaiser · Accepted Answer · 2014-04-21 18:00:29Z

2

I guess the problem is a pretty simple one. You didn't show the whole code, but I guess the problem is that you're saving the insertion for some unknown reason as string into a var named $stat. Everytime you fill the var with a new query you're overwriting your var. You should use $stat .= $wpdb->insert( ...etc... ) instead. to append to your string. Don't forget to set the var to an empty string before the loop, so you can append: $stat = ''.

Another - imo better way - would be to use an array. Reason: You can easier debug the code. And pls read the comment i/t code.

$stats = array();
for( $i = 0; $i <= $urlCount; $i++ ) 
{
    $stats[] = array(
        'POSTID' => $post->ID,
        // are you really retrieving **ALL** your urls as "url1", "url2", etc. 
        // via some $_POST-ed form?
        'URL' =>   filter_var(
            $_POST["url{$i}"], 
            FILTER_VALIDATE_URL, 
            FILTER_FLAG_SCHEME_REQUIRED
        )
    );
}

foreach ( $stats as $stat )
    $wpdb->insert( 'WP_URLS', $stat );

edited Apr 21, 2014 at 18:00

answered Sep 3, 2011 at 21:55

kaiser

51k27 gold badges152 silver badges246 bronze badges

I just wanted to see if the insertion was successful or not and thats the reason I stored the $stat. Just to print the value.

Albin Joseph
– Albin Joseph

2011-09-05 06:44:24 +00:00
Commented Sep 5, 2011 at 6:44
1

Thank you for the better way. I printed $_POST and it has values for ur1, url2 etc.

Albin Joseph
– Albin Joseph

2011-09-05 06:47:03 +00:00
Commented Sep 5, 2011 at 6:47

Add a comment |

Shukri Yusof · Accepted Answer · 2021-12-17 22:46:18Z

Even though there is an accepted answer, it works with seperate queries for each insert.

This could be a better solution for inserting data in just one query:

<?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
        $place_holders = [];
        $row = implode(', ', array_keys($row_arrays));
        $values = array_values($row_arrays);

        foreach (array_values($row_arrays) as $key => $param) {
        
            if(is_numeric($param)) {
                if($key == 0){
                    array_push($place_holders, " '%d'");
                } else {
                    array_push($place_holders, ", '%d'");
                }
                
            } else {
                if($key == 0){
                    array_push($place_holders, " '%s'");
                } else {
                    array_push($place_holders, ", '%s'");
                }
            }
        }

        $params = implode('', $place_holders);
    
        $query = "INSERT INTO {$wp_table_name} (" . $row . ") VALUES (" . $params . ")";

        if($wpdb->query($wpdb->prepare($query, $values))){
            return true;
        } else {
            return false;
        }
    }

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

I'd suggest to switch the arguments order and set the implicitly required argument first: $table_name. Also, you can directly use $wpdb->table_name in most cases. Further more, from a readability point of view, you might want to set INSERT INTO {$wp_table_name} ( after the foreach loop so it's clear what happens in which order (loop, query rows, build VALUES array, then assign and insert. About your prinft arguments: There's more than string/int. Also there might be problems with non-default rows and strict SQL mode. — kaiser
– kaiser, Commented Jan 15, 2016 at 9:37
I am very sure that your string concat in the foreach loop with ,/comma separation will not work. It will produce double commas at the start of the string. Still +1 for the link. — kaiser
– kaiser, Commented Jan 15, 2016 at 9:40

Stack Exchange Network

$wpdb->insert - inserting multiple rows

2 Answers 2

Your Answer

Linked

Hot Network Questions

$wpdb->insert - inserting multiple rows

2 Answers 2

Your Answer

Sign up or log in

Post as a guest

Linked

Related

Hot Network Questions