updating user in custom wordpress table

Question

I have created a custom table in my wordpress project and I'm using wordpress sql queries to insert/update a user. When an email already exists, it should update the user with the same email, inserting a new user works, but updating one doesn't do anything. No errors either. Can anyone see what I'm doing wrong with my query? Here's my code:

global $wpdb;

        $fname = sanitize_text_field(esc_sql($_POST['fname']));
        $email = sanitize_email(esc_sql($_POST['email']));

        $uniqId = hexdec(uniqid());

        $prepare = $wpdb->prepare("SELECT * FROM `wp_registered_customers` WHERE email = %s", [$email]);
        $users = $wpdb->get_row($prepare);
        if(count($users) > 0){
            // wp_send_json_error(ErrorCodes::EMAIL_ALREADY_IN_USE, 400);
            $sql = $wpdb->prepare("UPDATE `wp_registered_customers` SET fnmme=%s, email=%s, step1=1, step2=0, step3=0 WHERE email=%s", 
                [
                    $fname,
                    $email,
                    $email,
                ]
            );
            $result = $wpdb->get_results($sql);
        } else {
            $wpdb->insert('wp_registered_customers', array(
                'customer_id' => $uniqId,
                'fname' => $fname,
                'email' => $email,
                'step1' => 1,
            ));
        }

Answer 1

There are 2 main issues in your code:

1. Your $wpdb->get_row($prepare) will return either an object or a null, so you can't do if(count($users) > 0) because $users is not an array.

   To fix that, just use if ( $users ).

2. There's a typo here: SET fnmme=%s — the field name should be fname.

So you should just need to correct those issues and your code would work as expected.

However, you should not use $wpdb->get_results() to run an UPDATE query, and instead,

• You can use $wpdb->query().

• Or in your case, you should use $wpdb->update() like so: (just replace the $sql = $wpdb->prepare(...); $result = $wpdb->get_results($sql); in your code with the code below)

  $wpdb->update(
      'wp_registered_customers',
      array(
          'fname' => $fname,
          'email' => $email,
          'step1' => 1,
          'step2' => 0,
          'step3' => 0,
      ),
      array( 'email' => $email )
  );
  

  But, are you sure you want to set email to the value of $email? 🤔

Additionally, I don't think it's necessary to call esc_sql() here:

$fname = sanitize_text_field(esc_sql($_POST['fname']));
$email = sanitize_email(esc_sql($_POST['email']));