Duplicate Rows on Multiple Tables w/ Linked Keys - SQL / PHP

[jamie640]

Hello All,

Hoping someone with a little more experience than myself maybe able to help.
I've been stuck on this issue with my code for months now, and it's started hurting my head thinking about it.

Can anybody offer any guidance / advise / anything at all to help me on my way?

-----

I'm writing a web application, which will be eventually used for a construction company.
The application successfully allows users to produce quotes for costing purposes.

I'm stuck, because I need to be able to duplicate a quotation, and all sub-tables with linked keys.
This will create a new version of the quote which we can edit separately.

I've managed to duplicate the first table, and the subsequent table with the linked keys.
However, I'm struggling to duplicate the rows on the third table, and have them linked to the second.

I may be doing this all wrong, but can anyone advise?

        //FIRST TABLE INSERT:

        //KEY FOR TABLE ONE: quote_id > LINKED TO TABLE TWO

        $query = "
        
        INSERT INTO quotes (customer, quote_contact, enquirey, quote_name, quote_date, quote_status)
        SELECT customer, quote_contact, enquirey, quote_name, quote_date, quote_status
        FROM quotes
        WHERE quote_id = :version_quote
        
        ";

        //:version_quote is a btn

        $statement = $connect->prepare($query);
        $statement->execute(
            array(
                ':version_quote'    =>    $_POST["version_quote"]
            )
        );
        $result = $statement->fetchAll();



        //SECOND TABLE INSERT:

        //KEY FOR TABLE TWO: quote_details_id > LINKED TO TABLE THREE
        //FOREIGN KEY FOR TABLE TWO: quote_id > LINKED TO TABLE ONE

        $query = "

            INSERT INTO quote_details (quote_id, quote_details_desc, quote_details_quantity)
            SELECT LAST_INSERT_ID(), quote_details_desc, quote_details_quantity
            FROM quote_details
            
            WHERE quote_id = :version_quote
            ";
            

            $statement = $connect->prepare($query);
            $statement->execute(
                array(
                    ':version_quote'    =>    $_POST["version_quote"]
                )
            );
            $result = $statement->fetchAll();

            // THIRD TABLE INSERT:

            //PRIMARY KEY FOR TABLE THREE: qd_item_id
            //FOREIGN KEY FOR TABLE THREE: qd_quote_details_id > LINKED TO TABLE TWO

            $query = "

            INSERT INTO qd_items (...)

 

[Deleted member 2829]

Not sure I understand what the problem is here, you don't tell what exactly is the problem with table 3, except that you're struggling.
But the first statement really puzzles me already:

INSERT INTO quotes (customer, quote_contact, enquirey, quote_name, quote_date, quote_status)
        SELECT customer, quote_contact, enquirey, quote_name, quote_date, quote_status
FROM quotes
WHERE quote_id = :version_quote

You are selecting a row from table quotes by its key (which I guess is a unique primary key ?) and then insert that same row back into the table. How is that supposed to work and what do you want to achieve with it ? What am I missing here ?

BTW - Have you tested these same SQL commands directly in your database's native SQL interpreter, and were there any messages ?

 

[jamie640]

@cbreemer, firstly thank you for taking the time to reply to me, and appologies if i'm not very clear.

Essentially, what i'm trying to do is make a duplicate of a row on table #1 upon clicking a button, ( :version_quote is the button that references the row id ).
Once the button is pressed, the the query selects all of the data from the row, and duplicates it. So far so good.

In the same query, i then duplicate all rows from table #2 where the quote_id matches (foreign key).
These new rows are associated with the first tables duplicate row via the LAST_INSERT_ID

So far, it works great... I've created a duplicate row from table one, and managed to assign copies of the second tables rows to the first table.

I hope you're still with me?.... 🙄

What i'm struggling with, is i need to duplicate rows from table #3 and assign them to each of the newly copied rows from table #2.
This means i need to insert rows into table #3, by the id of each row in table #2 that i recently duplicated..

I don't know if I need to create some sort of loop, or perhaps i'm going about this completely the wrong way... I'm a complete novice when it comes to coding, and how i made it this far is nothing short of a miracle. If i can get this last part working, then I can potentially start using the system in the workplace.

The reasoning the above is so important, is that we reguarly deal with projects costing anywhere between £100k and 500k. Our clients request that we manufacture things out of for example, glass and metal, but may also want comparitive costs for producing the same thing in a different material such as timber or acrylics. We would have dozens if not 100s of rows on a quote, and the time it takes to manually produce another quote is extremely time consuming. Overwriting an existing quote isn't an option either, as we may need to go back to it at a later date.

It's very difficult to find information on duplicating tables online, as it looks as though there isn't much cause for this sort of thing...

If you somehow mananged to understand the above, please do feel free to ask any further questions!
As i said, i've been working on this project for an extremely long amount of time, and i've tried many things to get this to work.

If you have any advice, i'm all ears!

Thanks,
Jamie

 

[jamie640]

Also to assist, see attached some photo's of my datatables. I hope this visually gives a better understanding as to what I am trying to acheive.

 

[menator01]

It may just be me but, seems you should look into relational databases. What it sounds like your trying to do is create a relation between tables in the same database by creating new tables and filling with data from the other tables. Just my opinion.

 

[jamie640]

I think i have an ok understanding of relational databases, and how they work, and I think my tables are strucurally sound.

---


I have a relational database.

Enquiries Table
Quote Project Table
Quote Line Item Table
Quote List Items Table

- An Enquiry has many Projects
- A Project has many Line Items
- A Line Item has many List Items

How do i duplicate or copy a 'Project' and all of it's associated keys from the Line Item Table, and List Item Table.
And Assign them all new keys with SQL / PHP.

I think that's as concise as i can be...?

 

[Deleted member 2829]

I think that's as concise as i can be...?

Oh you can be a lot more concise than this ! First of all, post the complete DDL for these three tables, i.e. the SQL used to create them. This will at least make clear what all the fields and keys are, maybe address some of the so far unanswered questions, and hopefully enable someone to help you out with the final INSERT statement. In most databases (not sure what yours is ?) you get a table's DDL as the output of a DESCRIBE TABLE command. Please do not post screenshots. It would also help if you refer to the tables by their actual name, rather than table #1 etc. And take care of your terminology... You confusingly use the terms link, associate, assign for what I guess is the same thing, which is referencing a foreign key.

 

[jamie640]

@cbreemer, understood, and thanks for the feedback. I can understand your confusion.
Would the below help, or am I further confusing things?

------

I'm using datatables to make use of the realtime CRUD operations.
As a minimum, I have x3 files for each table. e.g. quotes.php | quotes_action.php | quotes_fetch.php

Below I have shown the workings of the quotes table, (excluding the majority of information to make it easier to digest).
Does this provide a better insight as how my tables work? I'm not sure that you would need to see information for every table, as it generally repeats.

$(document).ready(function(){

    //CREATE CSS GROUPS FOR EACH COLUMN STATUS
    var enquireydatatable = $('#quote_data').DataTable({
        "createdRow": function( row, data, dataIndex){
                if( data[7] ==  `complete`){
                    $(row).addClass('complete');
                }
                if( data[7] ==  `lost`){
                    $(row).addClass('lost');
                }
                if( data[7] ==  `active`){
                    $(row).addClass('active_row');
                }
                if( data[7] ==  `sent`){
                    $(row).addClass('sent');
                }
        },
        
        
        
        
        
        "processing":true,
        "serverSide":true,
        "orderCellsTop": true,
        "order":[],
        "ajax":{
            url:"../../../pages/tables/quotes/quotes_fetch.php",
            method:"POST"
               },
                "columnDefs":[
                    { "width": "5%", "targets": 0 },
                    { "width": "10%", "targets": 1 },
                    { "width": "10%", "targets": 2 },
                    { "width": "15%", "targets": 3 },
                    { "width": "15%", "targets": 4 },
                    { "width": "10%", "targets": 5, render: $.fn.dataTable.render.number( ',', '.', 2, '£' ) },
                    { "width": "10%", "targets": 6 },
                    { "width": "5%", "targets": 7 },
                    { "width": "5%", "targets": 8 },
                    { "width": "5%", "targets": 9 },
                    { "width": "5%", "targets": 10 },
                    {
                        "targets":[0, 1, 2, 3, 4, 5, 6],
                        "orderable":true,
                        "searchable":true,
                    },
                ],
                "lengthMenu": [[-1, 25, 50, 100], ["All", 25, 50, 100]],

                initComplete: function() {
                    
                    
                    
                    //SELECT BOX INSTEAD OF SEARCHBOX
                    this.api().columns([1,7]).every(function() {
                    var column = this;
                    var select = $('<select><option value=""></option></select>')
                      .appendTo($(column.footer()).empty())
                      .on('change', function() {
                        var val = $.fn.dataTable.util.escapeRegex(
                          $(this).val()
                        );

                        column
                          .search(this.value)
                          .draw();
                      });

                    column.data().unique().sort().each(function(d, j) {
                      select.append('<option value="' + d + '">' + d + '</option>');
                      
                      
                    });
                    });
                    
                    
                    
                    $('#quote_data tfoot tr').appendTo('#quote_data thead');
                                                            
                        $('.search-input-text').on( 'keyup click', function () {   // for text boxes
                        var i =$(this).attr('data-column');  // getting column index
                        var v =$(this).val();  // getting search input value
                        enquireydatatable.columns(i).search(v).draw();

                        } );
                        
                    
                        


                        }
                        
                                        
        
        
        
        
    });
    
        

    // --- V E R S I O N   Q U O T E ---
    
    $(document).on('click', '.version_quote', function(){
        
        //alert('Quote Versioned');

        var btn_action = 'version_quote';
        var version_quote = $(this).attr("id");
        
        if(confirm("Are you sure you want to version this enquirey?"))
        {
        $.ajax({
            url:"../../../pages/tables/quotes/quotes_action.php",
            method:"POST",
            data:{version_quote:version_quote, btn_action:btn_action},
            success:function(data)
            {
                $('#alert_action').fadeIn().html('<div class="alert alert-success">'+data+'</div>');
                $('#action').attr('disabled', false);
                enquireydatatable.ajax.reload();
            }
        })
        }
        else
        {
            return false;
        }
        
        
        
    });
    
    // --- V E R S I O N   E N D ---

The data is retreived through a seperate file: quotes_fetch.php

$output = array();

$query .= "SELECT quotes.quote_id, customers.customer_name, quotes.quote_contact, enquireys.enquirey_name, quotes.quote_name, quotes.quote_date, quotes.quote_status,
           SUM(quote_details.quote_details_quantity * qd_items.qd_item_total) AS quote_value
           FROM quotes
          
           INNER JOIN customers ON customers.customer_id = quotes.customer
          
           LEFT JOIN enquireys ON enquireys.enquirey_id = quotes.enquirey
           LEFT JOIN quote_details ON quote_details.quote_id = quotes.quote_id
           LEFT JOIN qd_items ON qd_items.qd_quote_details_id = quote_details.quote_details_id

          

           ";

Any actions are also made through a seperate file: quotes_actions.php
Actions would include adding, deleting, updating, and in this case, versioning (duplicating) a quote.

if(isset($_POST['btn_action']))
{
    
    
    
        
    
    if($_POST['btn_action'] == 'version_quote')
    {
        
        
        // F I R S T ---------
        $query = "
        
        INSERT INTO quotes (customer, quote_contact, enquirey, quote_name, quote_date, quote_status)
        SELECT customer, quote_contact, enquirey, quote_name, quote_date, quote_status
        FROM quotes
        WHERE quote_id = :version_quote
        
        ";
        
        $statement = $connect->prepare($query);
        $statement->execute(
            array(
                ':version_quote'    =>    $_POST["version_quote"]
            )
        );
        $result = $statement->fetchAll();
        
        
        
        
        
        // S E C O N D ---------
        $query = "

            INSERT INTO quote_details (quote_id, quote_details_desc, quote_details_quantity)
            SELECT LAST_INSERT_ID(), quote_details_desc, quote_details_quantity
            FROM quote_details
            
            WHERE quote_id = :version_quote
            
            ";
                    
        
        //SELECT * FROM quote_details WHERE quote_id =
        
        $statement = $connect->prepare($query);
        $last_id = $connect->lastInsertId();
        $statement->execute(
            array(
                ':version_quote'    =>    $_POST["version_quote"]
            )
        );
        $result = $statement->fetchAll();
        $filtered_rows = $statement->rowCount();
    
    

        // T H I R D
        
        





    
    }

 

[Deleted member 2829]

Indeed, this obfuscates things even more. All I asked for was the DDL for the 3 tables, so as to try and help you with the insert of the 3rd table. But you keep sidestepping my questions 😕