WordPress Development Stack Exchange is a question and answer site for WordPress developers and administrators. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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)
);
share|improve this question
1  
What is the structure of your table? I see no problem in your code. – Ján Bočínec Sep 2 '11 at 6:52
    
You might want to use $wpdb->prepare() for security reasons. See Codex. – kaiser Sep 2 '11 at 13:26
    
CREATE TABLE WP_URLS ( POSTID BIGINT NOT NULL, URL VARCHAR(254) ); @johnnyPea – Albin Joseph Sep 3 '11 at 4:27
1  
@kaiser the $wpdb->insert() escapes the data as per the documentation. Still do we need to use $wpdb->prepare() ? – Albin Joseph Sep 3 '11 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 Sep 3 '11 at 21:52
up vote 2 down vote accepted

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 );
share|improve this answer
    
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 Sep 5 '11 at 6:44
1  
Thank you for the better way. I printed $_POST and it has values for ur1, url2 etc. – Albin Joseph Sep 5 '11 at 6:47

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:

/**
 * 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

share|improve this answer
    
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 Jan 15 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 Jan 15 at 9:40

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.