Ask your WordPress questions! Pay money and get answers fast! Comodo Trusted Site Seal
Official PayPal Seal

INSERT and UPDATE in 1 function??? Need to record success.. WordPress

  • SOLVED

This statement executes correctly on the database:

INSERT INTO
wp_transactions
(
employee_id,
quantity,
quantity_depleted,
date,
trans_type,
batch_desc,
location_id,
item_id
)
SELECT
S.employee_id,
S.quantity,
S.quantity_depleted,
S.date,
S.trans_type,
S.batch_desc,
S.1_id,
S.2_id
FROM
wp_staging S
WHERE
S.trans_type='Inventory'


I would also like to update each wp_staging line effected with a record of 'success' by adding something like this to the function:
UPDATE
wp_staging S
SET
S.process_code = 'success'


Any ideas? Clearly I'm doing it wrong ;)

Answers (3)

2011-08-23

Erez S answers:

It's actually impossible. You can only update and insert in one query when there is duclipated values, and also on the current table and not in another.
See these:
http://www.digimantra.com/tutorials/insert-update-single-mysql-query-duplicate-key/
http://www.daniweb.com/web-development/databases/mysql/threads/107837


Patricia Moff comments:

That's what I figured, but I was hoping there was a way to do it with PHP. Any ideas?


Patricia Moff comments:

Perhaps I should re-phrase my question....
How does someone normally record a line submitted was successfully?


Erez S comments:

There is a function called mysql_insert_id
http://php.net/manual/en/function.mysql-insert-id.php
This function return the last value you inserted, so simply use the update query above using simple WHERE statement with the returned value


Erez S comments:

On wordpress queries you can recive the last ID using this syntax:
$wpdb->insert_id;


Patricia Moff comments:

Erez,
I'm not sure I understand. Can you cut and paste my code, adding the insert_id, or whatever, so I can see it in action?
I need to add it to the S.process_code (wp_staging table), not the wp_transactions table.


Erez S comments:

After the UPDATE query do this:

("UPDATE

wp_staging S

SET

S.process_code = 'success'

WHERE

S.employee_id = '".mysql_insert_id()."');

Or like this:

("UPDATE

wp_staging S

SET

S.process_code = 'success'

WHERE

S.employee_id = '".$wpdb->insert_id."');


Patricia Moff comments:

I tacked it on the end of my original execute SQL, and it complained. But I think you might be onto the right answer. I think it will work in a PHP function.

This question was sort of 'part one' of a much large question I need to post, so I think I will declare you the winner and move to 'part two'

Thanks so much for your help!

2011-08-23

Daniele Raimondi answers:

I should separate the operations, using some php code to retrieve all needed record into a PHP array and then, for each record make an insert in wp_transactions and, on success, an update to the relative wp_staging record in the database


Patricia Moff comments:

Can you write that in WordPress code for me? I don't know what that means ;( sorry.

2011-08-23

Peter Michael answers:

Might want to use [[LINK href="http://dev.mysql.com/doc/refman/5.1/en/stored-routines.html"]]Stored Routines[[/LINK]] using [[LINK href="http://dev.mysql.com/doc/refman/5.1/en/triggers.html"]]Triggers[[/LINK]]