Support » Developing with WordPress » How to Update multiple rows using $wpdb->update

  • Hi,
    I have created dynamic add/remove fields in frontend post submission page. Using $wpdb->insert the system is working perfectly.

    I have also created a draft edit page on frontend. On this page I want these dynamic fields to get updated. I tried $wpdb->update but it only updates the last field. In short I want to update multiple rows on single click.

    my code:

    $project_id = $_SESSION['project_id']; global $wpdb; $reward_ids = $wpdb->get_results("SELECT * FROM wpxa_rewards WHERE project_id = $project_id"); foreach($reward_ids as $reward_id); $r_id = $reward_id->ID;
    
    $count = count( $project_reward_title );
    for ( $i = 0; $i < $count; $i++ ) {
    
    global $wpdb;
    
    $wpdb->update( 'wpxa_rewards',
    
      array(
    
        'reward_title'       => "$project_reward_title[$i]",
        'reward_description' => "$project_reward_description[$i]",
        'reward_amount'      => "$project_reward_amount[$i]",
        'reward_shipping'    => "$project_reward_shipping[$i]",
        'est_date'           => "$project_est_date[$i]"
    
      ),
    
      array( 'ID' => $r_id ),
    
      array(
    
        '%s',
        '%s',
        '%d',
        '%s',
        '%s'
    
      ),
    
      array( '%d' )
    
    );
    
    }

    Plz help.. Thanks

    • This topic was modified 6 years, 2 months ago by Mineshrai.
Viewing 5 replies - 1 through 5 (of 5 total)
  • For a start there is a very important aspect of your code.
    It is fundamental that you MUST never EVER in your whole life EVER, pass user input into SQL without sanitizing it.
    Your code starts:

    $project_id = $_SESSION['project_id'];
     global $wpdb;
     $reward_ids = $wpdb->get_results("SELECT * FROM wpxa_rewards WHERE project_id = $project_id");

    Now what happens if a hostile person contrives in their web browser to set the session variable “project_id” to say:
    $_SESSION['project_id'] = "0; drop table;"
    You will submit these queries (and it could be MUCH worse than this):

    SELECT * FROM wpxa_rewards WHERE project_id = 0;
    drop table;

    You need to use “prepare” to protect your query.

    Your next phrase is:
    foreach($reward_ids as $reward_id); $r_id = $reward_id->ID;
    I think you want this loop to extend over the rest of your code, as it is only the last value of $r_id will be used.

    • This reply was modified 6 years, 2 months ago by RossMitchell.
    • This reply was modified 6 years, 2 months ago by RossMitchell.
    Thread Starter Mineshrai

    (@mineshrai)

    @rossmitchell Thanks.

    I will update the code with “prepare”. But my problem still exists.. Plz help..

    @mineshrai Ross gave you the solution. Look at the last paragraph. In your code $r_id is the same for all calls to $wpdb->update() so the same row is just getting updated over and over again. You need to update the ID for each item in the for loop.

    • This reply was modified 6 years, 2 months ago by Jacob Peattie.
    Thread Starter Mineshrai

    (@mineshrai)

    Hi @jakept Thanks,
    But some how I m not getting exactly what to do. Can u plz elaborate…

    Dion

    (@diondesigns)

    You didn’t tell us where this data is being stored. If it is in one of the WordPress “meta” tables (usermeta, postmeta, etc), is it not possible to run a single UPDATE query to update multiple “meta keys” for the same ID.

    If it is a table you created, and you copied the schema of one of the WordPress “meta” tables, give some thought to changing the table’s schema because the schema of the “meta” tables is extremely inefficient. A far more efficient schema would have each “meta key” as a column in the table. Not only would that allow updates of all “meta keys” at once, it would also allow a JOIN to a main table using the same ID.

    EDIT: I did some digging into wp-db.php. You said that using $wpdb->insert creates what you want using the code you provided. Try using $wpdb->replace instead of $wpdb->update and see if it solves your problem when updating data.

Viewing 5 replies - 1 through 5 (of 5 total)
  • The topic ‘How to Update multiple rows using $wpdb->update’ is closed to new replies.