Welcome!

By registering with us, you'll be able to discuss, share and private message with other members of our community.

SignUp Now!
  • Guest, before posting your code please take these rules into consideration:
    • It is required to use our BBCode feature to display your code. While within the editor click < / > or >_ and place your code within the BB Code prompt. This helps others with finding a solution by making it easier to read and easier to copy.
    • You can also use markdown to share your code. When using markdown your code will be automatically converted to BBCode. For help with markdown check out the markdown guide.
    • Don't share a wall of code. All we want is the problem area, the code related to your issue.


    To learn more about how to use our BBCode feature, please click here.

    Thank you, Code Forum.

SQL & PHP: Inserting or duplicating data the correct way?

I have three connected tables; quotes, quote_details and qd_items which are linked via keyed relationships shown below.

Code:
CREATE TABLE quotes (
    quote_id int, //PRIMARY KEY
    quote_name varchar
);

CREATE TABLE quote_details (
    quote_details_id int, //PRIMARY KEY
    quote_id int,          //FOREIGN KEY
    quote_details_description varchar
);

CREATE TABLE qd_items (
    qd_items int,            //PRIMARY KEY
    quote_details_id int,    //FOREIGN KEY
    qd_items_desription varchar
);

I'm looking for a way of correctly duplicating rows across multiple tables. For instance, if I click a button on the quotes table to duplicate a particular row of data.
Then I would like to create a procedure which also duplicates the data linked to the id of the row in the two other tables. The newly duplicated data would be assigned new keys, and would be seperate from the original record.

I've tried the below which works for the quotes table, and the quote details table:

PHP:
        //QUOTES TABLE
        $query = "
        
        INSERT INTO quotes (quote_name)
        SELECT quote_name
        FROM quotes
        WHERE quote_id = '".$version."' //VERSION CARRIES THE ID OF THE CLICKED ROW
        
        ";
        
        $statement = $connect->prepare($query);
        $statement->execute(
            array(
                $version    =>    $_POST["version_quote"] //THIS CARRIES THE ID OF THE CLICKED ROW TO DUPLICATE
            )
        );
        $result = $statement->fetchAll();
        
        
        //QUOTE_DETAILS TABLE
        $query = "

            INSERT INTO quote_details (quote_id, quote_details_description)
            SELECT LAST_INSERT_ID(), quote_details_description
            FROM quote_details
            
            WHERE quote_id = '".$version."' //REFERENCING CLICKED ROW ID AGAIN
            
            ";
            
            

        $statement = $connect->prepare($query);
        $statement->execute(
            array(
                '$version'    =>    $_POST["version_quote"] //REFERENCING CLICKED ROW ID AGAIN
            )
        );
        $result = $statement->fetchAll();


These two queries work: the quotes table is duplicated, and all records linked by the quote_id key are duplicated in the quote_details table too.
These are assigned new keys and are now seperate from the original records.

I'm struggling as I'm not sure how is the best way to create the duplicates in the qd_items table.
I think this may require some sort of transaction or loop to run through each key of the imported rows in the quote_details table, and duplicate for each key.

In practice this is become a bit of a challenge for me.
Perhaps someone would shed some light on the situation?
 

New Threads

Latest posts

Buy us a coffee!

Back
Top Bottom