Ask your WordPress questions! Pay money and get answers fast! (more info)

Advanced ON DUPLICATE KEY UPDATE mysql query WordPress

I have a database table that needs to be updated. This is a MySQL question. (it's a gravity forms table actually, but that's kind of irrelevant to the question.)

The table contains five columns: Id, form_id, lead_id, field_id, and field_value.

When a user initally fills out a form, for every answer answered, a row is added to this table. (If they do not answer a question, there is no row added.)

I have another form that is used to update this table. This is easy to accomplish IF the field was PREVIOUSLY answered (a row exsists and I can use form_id, lead_id, field_id in the WHERE clause to udpate my table.)

However, I have run into trouble that sometimes, the answer was never answered, and so the row does not exist. I need to add a new row if the fields "form_id", "lead_id", "field_id" do not exist together. (I don't think I can use Id, as I'd have to query the database to get that. I'd think ON DUPLICATE KEY UPDATE would work, but again, I don't know how to tell the query that "form_id", "lead_id", "field_id" are the Uniques.)

The second part of my question I'm hoping a solution exists where I can run a single update query. (The form has 30+ fields.) My initially query was using CASE clause to accomplish this.

Answers (3)

2013-02-18

Arnav Joy answers:

can you show your query how you are running it currently?


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.]

2013-02-18

Francisco Javier Carazo Gil answers:

weaver,

First one, I would set this table to InnoDB and I would introduce some constraints, like indexes and maybe more tables to be able to get Foreign Keys to lead, field, etc.


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.

2013-02-19

Hardeep Singh answers:

Hello,

First thing, this is doable. I have used it in my custom CMS implementation.

1. Unique index exists for the combination of three fields. Please confirm. This is a must for this clause to work
2. Can you share the table structure + indexes? I can do some testing on my own.
3. Sample SQL that is being used.

- Hardeep


Hardeep Singh comments:

Hello,

Any updates!!

Thanks!