Stanj
Posts: 166
|
| Posted: 06/25/2009, 4:05 AM |
|
Hi CCS'ers
I have 3 related tables and need to update a field after inserting g into another table.
Table: ORDERS with fields order_id, pc_id
ORDERS_PRODUCTS with fields order_id, port_id, pc_detail_id
PC_DETAIL with fields pc_id, port_id, pc_detail_id
Field to be updated: pc_detail_id
After a order is created, the customer selects additional details, in this case a date in the pc_detail table, the index for that row is pc_detail_id. That index number needs to be inserted into the orders_products table into the empty field of the same name.
The orders_products table will return 1 or more rows that need to be updated for each orders_id. The pc_detail table needs two values to return the unique row: pc_id and port_id
I am using MySQL 5.1 and have not figured out a way to do this yet as an after_insert event with a form that enters pc_id data into ordes table. My attempts at writing an Update query to accomplish this has been unsuccessful so far.
Any suggestions for a non-programmer?.
Thank you
Stan
CCS4.2xx/PHP5.1/MySQL5.1/Vista
_________________
Stan
St Petersburg Russia |
 |
 |
jacem
Posts: 87
|
| Posted: 06/28/2009, 5:28 PM |
|
Hi,
You can execute an sql statement within a event trigger. So the trick is to ensure you know what you want to do SQL wise.
$db =new clsDBMyWebDatabaseDB(); //replace with your database connection name
//example sql statement being saved as a text variable $SQL, getting values from a form/grid, the current date/time coming fromthe sql server itself just to show you can mix thing up a bit
$SQL="UPDATE yourtable SET `datefieldone`=now(), `textfieldone`=".$Container->fieldone->value." WHERE `anotherfield`='".$Container->anotherfield->Value."'";
//I personally echo the $SQL in runtime to see that I am creating an SQL statement that will work while developing before the up-comming code is executed:
//now actually execute the sql statement
$db->query($SQL);
//close the database
$db->close();
Hope this points you into the right direction
|
|
|
|
Stanj
Posts: 166
|
| Posted: 07/02/2009, 11:55 PM |
|
Thank you Jacem
I thought I replied to your helpful answer right after you made it but now see that it did not register. I was having intermittent DSL problems last week so maybe that is the cause.
Thank you for taking the time to answer my question. I had come to the conclusion my problem was simply debugging the complex SQL query that used 6 joins and many variables just before you answered so choose to break it down to smaller chunks. I ended up creating an array of the products in the order and stepped through them with a simpler query that triggered a stored procedure on the server at each step. That worked fine and is easy to modify runtime.
This is for a shopping cart that is a old much modified oScommerce code base that is a bit of a kludge and frankly scary looking. Its db structure is completely wrong for the functions we need. I built an entirely new cart designed around the specific needs that works fine last year using CCS but the office staff voted not to have to learn a new system of order processing fearing that it would mean less work so therefore fewer people. My main concern was increased capacity and was not even thinking of staff reductions. One small division in the office specializes in one product(a type of cruise shore excursion) that is running the new cart and post-order processing system and the 2 new hires handle more bookings with a relaxed pace than the rest of the 15 person office does with this old processing system that has the oSC core. In fact almost twice as many. We have to turn away customers of the other form of shore excursion because of not being able to keep up with the order processing before the client leaves home on their cruise.
It is amazing how functional a complex system can be build by a non-programmer using CCS4.
_________________
Stan
St Petersburg Russia |
|
|
|
|
