How to Conditionally Update Multiple Database Rows

I anyone has time, I need a little education on handling a database issue as I'm new to this.

I have a database of customers used by a plugin and in that table is a field "wp_user_id" which is NULL for some entries. I need to update all rows with NULL to be replaced with the real user id. All of those entries already have an email address and that email address field corresponds to their WP user. All the data in the table has already been escaped.

I am trying to create an include to update that database every time that plugin's module is opened. So I need to fetch all rows with wp_user_id = NULL, then fetch the correct user id using get_user_by and then update that row.

I have read many examples of routines for different kinds of updating, but I am not sure how to proceed because I am new. Should I be trying to fetch many rows into an array, or should I be running some kind of loop that runs through every row in the database?

  • Jason Overhulser

    Just to show you what I was experimenting with...

    // these data are already escaped
    $blog_id = get_current_blog_id();
    $target_table_name = "wp_".$blog_id."_customers";
    echo "I'm alive and the table name is ".$target_table_name."
    ";
    global $wpdb;
    //$myrows = $wpdb->get_results( "SELECT id, name FROM mytable" );
    echo "global was declared
    ";
    $results = $wpdb->get_results(
    $wpdb->prepare("SELECT count(ID) as total FROM {$wpdb->$target_table_name} WHERE wp_user_id IS NULL", $some_parameter)
    );
    //$result =$wpdb->get_results("SELECT * FROM $wpdb->$target_table_name where wp_user_id IS NULL");

    echo "results were obtained
    ";
    //print_r($result);
    echo var_dump ($results);
    sleep (20);

  • Mahlamusa

    Hi Jason Overhulser

    I hope you are doing great today. Thank you very much for contacting us and for giving us the sample code of what you are trying to implement. I have looked into the code, and if I understood very well the following code should be very useful to you. I have tested the code locally and it seemed to work. Please refer to the comments in the code to understand what each statement.

    Copy and paste the following code into your theme's 'functions.php' file:

    add_action('init', 'update_user_ids');
    function update_user_ids(){
    	global $wpdb;
    	$blog_id = get_current_blog_id();
    
    	switch_to_blog( $blog_id ); //switch to the current blog
    	$target_table_name = $wpdb->prefix.'customers'; //use wpdb->prefix instead of 'wp_'.$blog_id'_'
    
    	//get all the user email addresses from the customers table
    	$results = $wpdb->get_results("SELECT user_email FROM {$target_table_name} WHERE wp_user_id=NULL");
    	foreach ( $results as $update_user) {
    		//create a new use object given the user's email address
    		$user = get_user_by( 'email', $update_user->user_email );
    
    		//update table where set wp_user_id = $user->ID, where user_email = $user->user_email
    		$updated =  $wpdb->update(
    			$target_table_name,
    			array('wp_user_id' => $user->ID),
    			array('user_email' => $user->user_email),
    			array('%d'), array('%s')
    		);
    
    		//if $updated you can log the details
    		if ( $updated ) {
    			error_log( "CUSTOMER UPDATED: User ID: ". $user->ID . ", email: " .$user->user_email );
    		}
    	}
    	//restore current blog
    	restore_current_blog();
    }

    Or create a new file like 'update-user-ids.php' paste the code above and add the PHP opening tag at the top of the file like so:

    <?php
    //paste the code above here

    Then save and upload that file to your website in the folder '/wp-content/mu-plugins', you should just create the 'mu-plugins' folder if it does not exist. This is now a 'Must Use' or MU Plugin that will automatically be loaded and executed by WordPress every time it runs.

    The code should work as is but with the assumption that the email column is called 'user_email', if that is not the case then you should replace that with the actual column name containing the user email in the table, replace 'user_email' with the actual column name used in the 'customers' table you are trying to update.

    I hope that helps you very much. Please do not hesitate to ask should you need any further assistance. Please enjoy the rest of your day.

    Cheers,
    Mahlamusa

  • Mahlamusa

    Hi Jason Overhulser,

    Sorry I noticed that the code in the post is not properly formatted and may be difficult to follow, as result I have attached a zip file which contains the properly formatted code. You can simply unzip and upload the contained file into your testing website in the folder '/wp-content/mu-plugins' and run your test. So you must have the file in the locations:

    /wp-content/mu-plugins/update-user-id.php

    Enjoy the rest of your day.

    Cheers,
    Mahlamusa