Advanced ON DUPLICATE KEY UPDATE mysql query WordPress

weaver comments:


UPDATE wp_rg_lead_detail
SET value = CASE ROUND( field_number*10 )
WHEN 383 THEN 'johnsmith1'
WHEN 386 THEN '[email protected]'
WHEN 388 THEN 'California'
END
WHERE form_id = 3 AND lead_id = 1 AND ROUND( field_number*10 ) IN ( 383, 386, 388)


[Note: field_number is a LONG, thus the use of ROUND.]

weaver comments:

That's not an option. Thanks.

Francisco Javier Carazo Gil comments:

Ok, I have seen your query, why do you fill it into MySQL instead of making it sense from PHP?

Are you using $wpdb?

weaver comments:

I don't really understand the question. It seems irrelevant (unless you know some obscure Gravity Forms API call.)

And yes, I'm using wpdb->query.

Francisco Javier Carazo Gil comments:

No, I told you about you SQL query, you are working directly using this query with CASE, I always recommend to do it this logical at application level.

I have worked with Gravity Forms and I thing would be easier if you give us some code to make easier our answers to help you.

weaver comments:

Why do you recommend this at the application level? This would create multiple queries. In my above example I am able to update three rows at once. (The only unfortunate part here is if the row doesn't exist, it is not updated.)

I have no code to offer, but this is how it works: when a user submits Form A, each field that was answered creates a single row in the above table (for our purposes, the lead_id is the particular user.) This form is only filled out once per user.

I then have another form that updates those rows. However it does not create a new row if none exisited. That's what I need.

Before I attempt my query, I have "form_id", "lead_id", "field_id", and "field_value".

"form_id", "lead_id" and "field_id" are never duplicate so they can be replied upon as unique.

Francisco Javier Carazo Gil comments:

If you do it at application level you can do the next, in the function who manage the form submission you can do something like this:

$check = $wpdb->get_row("SELECT * FROM XX WHERE id");
if($re_check == 0 || $re_check == NULL) { // if the row NO exists
$wpdb->insert( );
} else {
$wpdb->update();
}


weaver comments:

Right, and for a 30 element form, that would create 60 queries (looking up the ID for each.) As my orginal question said, "I'm hoping a solution exists where I can run a single update query."

Thanks.

Francisco Javier Carazo Gil comments:

Ok, so in this case you should use a store procedure, but also with this logic. Not in PHP, into MySQL, it would be much faster.

You could call directly from PHP the procedure.

Hardeep Singh comments:

Hello,

Any updates!!

Thanks!