Skip to:
Content
Pages
Categories
Search
Top
Bottom

sql query, program or plugin to insert a value in a field for all members


  • HDcms
    Participant

    @hdcms

    Hello
    I added an xprofile numeric field “photo_note”

    I did it manually for a member
    SELECT * FROM xxx_bp_xprofile_data WHEREuser_id = 3315

    id field_id value
    2488 794 3315 3

    I want to give a value “3” to several thousand members
    Can you tell me, a sql query, a program or a plugin that could allow me to do it?

    Regards

Viewing 13 replies - 1 through 13 (of 13 total)

  • HDcms
    Participant

    @hdcms

    Hello
    please
    regards


    Venutius
    Moderator

    @venutius

    I’m not big on SQL but could you not put all of your user id’d in and array and run a foreach loop on that array with the command you used above?

    Something like:

    $users = array( 12,23,45,56); //array of user id's
    global $wpdb;
    
    foreach ( $users as $user ) {
        $query="INSERT INTO XXXXXX_bp_xprofile_data (field_id, user_id, value, last_updated) VALUES ( fid, $user, 3, date )";
    $results = $wpdb->get_results($query); 
    }

    Where fid = the field number that you want to add and date is the timestamp for when the ddata was added.


    HDcms
    Participant

    @hdcms

    Hello
    Thank you for this idea
    So I have to go through php.
    If this code works, can I extend it to more than a thousand members?
    Regards


    Venutius
    Moderator

    @venutius

    I’d be tempted to do it in batches but yes it should work, probably not the most efficient way of doing it


    Venutius
    Moderator

    @venutius

    Note that this assumes the field does not already exist for the affected users, which should be correct.

    Also clear WordPress object cache after manipulating the DB directly.


    HDcms
    Participant

    @hdcms

    Hello

    @venutius

    What I meant was that I can not create a big array!
    Is not it rather necessary to create a loop from the 1st to the last member?


    @djpaul

    why? by program

    Thanks


    Venutius
    Moderator

    @venutius

    Then what you’d need to do is user SQL to pull off all the user_ids and put them into the $users variable and quirt that into the foreach loop.

    But you’d also have to add an if statement around the db INSERT in order to make sure each user only had the field once.


    Venutius
    Moderator

    @venutius

    So I think your function would look something like this, I’ve not tested this and like I say I’m no expert on $wpdb queries:

    global $wpdb;
    $users = $wpdb->get_results("SELECT ID from $wpdb->users");
    foreach ( $users as $user ) {
        $existing_field=$wpdb->get_results("SELECT fid FROM $wpdb->bp_xprofile_data for user_id = $user");
        if ( !$existing_field ) {
            $query="INSERT INTO $wpdb->bp_xprofile_data (field_id, user_id, value, last_updated) VALUES ( fid, $user, 3, date )";
    $results = $wpdb->get_results($query);
        }

    }`


    HDcms
    Participant

    @hdcms

    Hello @venutius

    I tested the first version that works well. Thank you

    I am now going to try to test the other. I did not understand to empty the cache, whether it was with BO programming or a cache plugin

    $users = array(3809,2749,2430,3849); //array of user id's
    global $wpdb;
    $note_champ="794";
    $note_valeur="3";
    foreach ( $users as $user ) {
        $query="INSERT INTO wpamo_bp_xprofile_data (field_id, user_id, value, last_updated) VALUES ($note_champ, $user, $note_valeur, NOW() )";
    $results = $wpdb->get_results($query); 
    }

    regards

    Because the object cache caches query results from the database and if you modify the database – either directly or using WP’s DB APIs – you to clear it. Otherwise you’ll end up with inconsistent results.

    Using WP-CLI is probably easiest for this: https://developer.wordpress.org/cli/commands/cache/flush/

    If you don’t know enough to write these PHP commands yourself, using some shell scripting and the BuddyPress WP-CLI commands will probably be easier: https://github.com/buddypress/wp-cli-buddypress/


    HDcms
    Participant

    @hdcms

    Hi @djpaul
    In my case, it’s a new field with a new value.
    nothing was cached before !!

    But, I ask a new question for new members who will register after the execution of the program. I do not see a way to put a default value in a numeric field. Did I look bad?

    Regards


    HDcms
    Participant

    @hdcms

    Hello,
    On another forum, I had this suggestion with WP function 🙂
    But I am embarrassed if I can not put a default value to my numeric field!

    $users = get_users( [ 'fields' => 'ids' ] );
    global $wpdb;
    /** @var \WP_User $user */
    foreach ( $users as $user_id ) {        
        $results = $wpdb->insert(
            'bp_xprofile_data',
            [
                'field_id'     => 794,
                'user_id'      => $user_id,
                'value'        => 4,
                'last_updated' => new \DateTime()
            ],
            [ '%d', '%d', '%d', '%s' ]
        );
    }

    regards

Viewing 13 replies - 1 through 13 (of 13 total)
  • You must be logged in to reply to this topic.
Skip to toolbar