0

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,
            ));
        }

1 Answer 1

0

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']));
6
  • Thanks it works now, I used the ->update method. Will that automatically preven sql injection, or does that need additional code?
    – Jan
    Commented Jul 14, 2023 at 9:01
  • Yes, the update method will use the prepare method to prepare the query for a safe execution. The documentation also stated, "Both $data columns and $data values should be "raw" (neither should be SQL escaped)".
    – Sally CJ
    Commented Jul 14, 2023 at 9:11
  • Ah okay, sometimes an email is used multiple times, how do I make sure only the first found row with a specific email gets updated?
    – Jan
    Commented Jul 14, 2023 at 9:17
  • I have already answered your question, so additional questions should be asked via separate posts. But what you asked is a generic PHP/programming question and should instead be asked on Stack Overflow.
    – Sally CJ
    Commented Jul 14, 2023 at 13:29
  • Nonetheless, you'd want to use get_results() then, i.e. $users = $wpdb->get_results($prepare);, and use if($users && count( $users ) > 0). Then you'd want use the first item in the result, and then update by a unique field in your table, e.g. the customer_id field. So for example, pass array( 'customer_id' => $users[0]->customer_id ) as the 3rd parameter for that function. (If you need further assistance, you should post a new question)
    – Sally CJ
    Commented Jul 14, 2023 at 13:33

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.