1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

Find users without custom field set

Discussion in 'General PHP and MySQL Discussions' started by nrep, Jun 1, 2015.

  1. nrep

    nrep Well-Known Member

    Hello,

    I've got a custom field called "nrep_status", which is set for 90% of our users (to either 'Valid' or 'Invalid'). However, I'd like to change this so that the 10% of non-assigned users have this custom field value set and updated to 'Invalid'.

    Initially, I bulk assigned all of our users via the following query, but due to a problem with the script that assigns the value during registration, some members have no custom field set:

    INSERT INTO xf_user_field_value (user_id, field_id, field_value)
    SELECT xf_user.user_id, 'nrep_status', 'Valid'
    FROM xf_user
    LEFT JOIN xf_user_option ON (xf_user.user_id = xf_user_option.user_id)
    WHERE user_state = 'valid' AND xf_user.is_banned = 0
    ON DUPLICATE KEY UPDATE
    field_value = VALUES(field_value);

    Is there a way to add a field for the unassigned users that meet the where clause criteria, without touching the status of those that have already been set? I just can't get my head around the MySQL for this.

    Basically, I want to run the above query, but only where there is no existing "field_value" set for the user.

    Cheers!
     

Share This Page