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