manuel
Posts: 48
|
Posted: 04/22/2013, 5:00 PM |
|
This is my table(documents): id, code, amount, doc_type
I need to update the "doc_type" based on the first 4 digits of "code", I'm using php & mysql, but I prefer to do this directly in mysql.
This must occur when a new record is created or updated. I cant modify the DDBB ( Stored Proc. other, Triggers, etc.) So I must do it in the page.
Tried to find the insert & update secuence in CCS php, but the function UpdateRow() and function InsertRow() seems really "cryptic" to me.
May be something like this:
INSERT INTO Documents (id, code, amount, doc_type) values (1, 1020456789, 23.90, CASE LEFT(code,4) when 1010 then "Personal"
when "1020" then "Offical"
when "1030" then "Driver"
END)
Any clue?
|
|
|
Lucius
Posts: 220
|
Posted: 04/23/2013, 10:12 AM |
|
Have you tried CCS help file? Check the topic named: "Execute Custom SQL" It's under Examples & Techniques -> Programming -> Working with databases
If you need to connect this to Insert and Update in the Database use appropriate events for the Record that enables your Insert/Update: After Execute Insert and After Execute Update events.
|
|
|
pjcltd
Posts: 34
|
Posted: 04/23/2013, 2:30 PM |
|
Quote manuel:
This is my table(documents): id, code, amount, doc_type
I need to update the "doc_type" based on the first 4 digits of "code", I'm using php & mysql, but I prefer to do this directly in mysql.
This must occur when a new record is created or updated. I cant modify the DDBB ( Stored Proc. other, Triggers, etc.) So I must do it in the page.
Tried to find the insert & update secuence in CCS php, but the function UpdateRow() and function InsertRow() seems really "cryptic" to me.
May be something like this:
INSERT INTO Documents (id, code, amount, doc_type) values (1, 1020456789, 23.90, CASE LEFT(code,4) when 1010 then "Personal"
when "1020" then "Offical"
when "1030" then "Driver"
END)
Any clue?
Hi
this can be done in code on the page
you need to get the value into a var then you can make the sql code up from that
once you have done it once then you will see how easy it is to do
i do it all the time on php and in .net VB
if you need help let me know drop me a pm with your email address
thanks
Paul
|
|
|
|