Results 1 to 5 of 5
  1. #1
    Join Date
    Nov 2012
    Posts
    3

    Unanswered: Hy all ! and I need help with Stored Procs and Triggers

    First of all, hello everybody! I'm new in php, newer in MYSQL and for now, the reason I joined this forum is to get help and support. I hope that won't be long until I will add my own useful contribution to this community.

    Now, my problem:

    Because I want to encapsulate multiple queries (especially INSERT) in one transaction, to be sure that all tables involved either are complete, or the query is not executed at all, I chosen to use mysqli_multi_query, but I have some problems returning last insert ID.

    The actual problem is that I have to pass clientID and dealerID (both of them are auto increment) to TABLE_orders in their appropriate fields.

    The only way I can think of it is to use stored procedures and triggers, but I have no clue how exactly to use them.
    The perfect way would be a procedure that handles all situations, but I cannot think of any solution. So, obviously would be a custom procedure/trigger for each table sequence that I may have, passed as argument in save().

    What I am askig you, sirs, if you are willing to, please show me how to create such a procedure/trigger that takes inserted id from some tables (in my case TABLE_clients.FIELD_clientName and TABLE_dealers.FIELD_dealerName) and attach that id on TABLE_orders, each one in it's correspondent field.
    Also, if you are thinking to a better solution, but to keep mysqli_multi_query functionality, I would be glad to adopt it.

    I just hope that what I wrote here makes any sense.

    Maybe you are asking why I am complicating myself, well, what you see here is compiled from several classes, in fact the all saving methods are part of an abstract class and only $table_seq and the custom procedure for each structure will be in its own object class.


    Thanks you very much !

    The basics of my code is as follows:
    (i left only what I thought is necessary for my example)


    PHP Code:
    <?php
    Class MyClass
    {
       
    /*
        *Declared all stuff here, including constructor and whatelse
        */

        
    protected function save(){
            
    $this->execute('sql_save');
        }
        
        private function 
    execute($sql_statement){
            
    // reset containers
            
    $this->field $this->data $this->table NULL;

            
    $sql "BEGIN;";
            foreach (
    $this->table_seq as $this->table => $fields_seq){    
                
    /*
                * I get data from array and implode it to make a comma-separated string
                * ready for using as Table (fields) and VALUES(data) in SQL INSERT
                */
                
    $this->field implode(", "array_keys($fields_seq));
                
    $this->data  implode(', 'array_map(array($this'add_quotes'), $fields_seq));

                
    $sql .= $this->$sql_statement(); //append the prepared SQL
            
    }
            
    $sql .= "COMMIT;";

       
    /*
        *it triggers mysqli_multi_query,
        *which runs all queries inside, so I cannot interfere from PHP
        */
         
    $result $this->db->query($sql);
        }
        
        private function 
    sql_save(){
            
    $sql "INSERT INTO ".$this->table." (".$this->field.") VALUES (".$this->data.");";
        return 
    $sql;
        }
        
        private function 
    add_quotes($str) {
        
    $quoted "'".$str."'";
            return 
    $quoted;
        }
        
       
    /*
        * Set a table - fields assignment to know which properties in which table go
        */
        
    protected function set_table_seq(){
            
    /*1*/
            
    $this->table_seqTABLE_clients] = [FIELD_clientName => $this->clientName];
            
    /*2*/
            
    $this->table_seq[TABLE_dealers] = [FIELD_dealerName => $this->dealerName];
            
    /*3*/
            
    $this->table_seq[TABLE_orders]  = [FIELD_clientID => $this->clientID,
                                               
    FIELD_dealerID => $this->dealerID
                                             
    /*[... and some more fields here...]*/ ];
            }
        }    
    }    
    <?
    Last edited by derei; 11-05-12 at 12:40.

  2. #2
    Join Date
    Nov 2012
    Posts
    3
    I tried something like this, but my trigger...doesn't trigger. or LAST_INSERT_ID() not working ?

    My logic was that the trigger will be called (of course) after the insert in Table1 has been performed and it will collect the id from last INSERT, then pass it to session variable @last, which should be visible in the second INSERT

    But I can't say what is going wrong here.

    Code:
    DROP TABLE IF EXISTS Table1;
    CREATE TABLE Table1(clientID INT NOT NULL AUTO_INCREMENT,clientName VARCHAR (256) NOT NULL,PRIMARY KEY (clientID));
    
    DROP TABLE IF EXISTS Table2;
    CREATE TABLE Table2(orderID INT NOT NULL AUTO_INCREMENT,clientID INT NOT NULL,orderStuff VARCHAR(256),PRIMARY KEY (orderID));
    
    BEGIN;
    
    DELIMITER //
    CREATE TRIGGER keepid AFTER INSERT ON Table1
    SET @last = (SELECT LAST_INSERT_ID());
    DELIMITER ;
    
    INSERT INTO Table1 (clientName) VALUES ('Client Name');
    INSERT INTO Table2 (clientID, orderStuff) VALUES (@last, 'some stuff here');
    
    COMMIT;

  3. #3
    Join Date
    Sep 2009
    Location
    San Sebastian, Spain
    Posts
    880
    You do not need mysqli_multi_query to get consistency. You need to created transactions. Transactions will mean that all your queries will work as a single query which either succeeds or fails. What this means instead of building the set of statements to be executed within a START TRANSACTION and COMMIT as you are doing in your code you simply execute a single query at a time starting with START TRANSACTION and ending with COMMIT.

    Alternatively using a stored procedure would be far better as this would avoid sending an SQL statement, parsing the statement, executing the statement and returning the results for each step. This would be all done within the database.

    You can have a START TRANSACTION in your code, all of your code and finally a COMMIT or ROLLBACK transaction statement as you have put in your code. But you do not need to execute them all as a single unit. So you could send a START TRANSACTION statement, process results

    Just read your new posting. Triggers will not work accessing the autoincrement values. There is another way of doing this by accessing the table information_schema.tables and the auto_increment field. However, you would need to lock this table or the specific row prior to inserting to guarantee the value to be correct.
    Last edited by it-iss.com; 11-06-12 at 13:05.
    Ronan Cashell
    Certified Oracle DBA/Certified MySQL Expert (DBA & Cluster DBA)
    http://www.it-iss.com
    Follow me on Twitter

  4. #4
    Join Date
    Nov 2012
    Posts
    3
    I don't know how to use transactions in PHP, so please if you would like to post an example.
    Also, for the second solution with "table locking", I have no idea what is that, but I think I could use an example too.

    In the meantime, I studied further more the triggers matter and I came out with a partial solution.
    The problem is that LAST_INSERT_ID() retrieves only the generated ID for the first statement where more inserts are done. MySQL :: MySQL 5.0 Reference Manual :: 12.13 Information Functions

    And, if you will run the code, you will see also that the current statement doesn't affect the value of LAST_INSERT_ID() (also mentioned on dev.mysql).

    So, the question is: how this code can be changed to provide the actual LAST INSERT ID for each statement?

    Code:
    /*
    DROP TABLE Table1;
    DROP TABLE Table2;
    DROP TABLE Table3;
    */
    CREATE TABLE IF NOT EXISTS `Table1`(`t1ID` INT NOT NULL AUTO_INCREMENT, `t1Data` VARCHAR(256) NOT NULL, PRIMARY KEY (`t1ID`));
    CREATE TABLE IF NOT EXISTS `Table2`(`t2ID` INT NOT NULL AUTO_INCREMENT, `t2Data` VARCHAR(256) NOT NULL, PRIMARY KEY (`t2ID`));
    CREATE TABLE IF NOT EXISTS `Table3`(`ID` INT NOT NULL AUTO_INCREMENT, `t1_ID` INT, `t2_ID` INT, PRIMARY KEY (`ID`));
    
    
    INSERT INTO `Table1`(`t1data`) VALUES('some data');
    INSERT INTO `Table2`(`t2data`) VALUES('other data');
    
    INSERT INTO `Table3`(`t1_ID`, `t2_ID`) VALUES(@id1, @id2);
    
    DELIMITER $$
    DROP TRIGGER IF EXISTS `call_on_t1`$$
    CREATE TRIGGER `call_on_t1` AFTER INSERT ON `Table1`
    FOR EACH ROW
    SET @id1 = LAST_INSERT_ID()$$
    
    DELIMITER //
    DROP TRIGGER IF EXISTS `call_on_t2`//
    CREATE TRIGGER `call_on_t2` AFTER INSERT ON `Table2`
    FOR EACH ROW
    SET @id2 = LAST_INSERT_ID()//

  5. #5
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    I'd rather be riding on the Tiger 800 or the Norton

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •