SitePoint Sponsor

User Tag List

Results 1 to 8 of 8
  1. #1
    SitePoint Wizard rctneil's Avatar
    Join Date
    Jun 2005
    Posts
    1,898
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question Duplicatingrow and related data

    Hello,
    If I have a db set up as below:



    So the middle table there creates a link between the top and bottom tables

    If I wanted to create a duplicate of a row in the top table but do the following things:

    add the word "-copy" to the name field, give it a unique area_id but also create duplicates of all the related rows in the related tables but under the new area_id for the copy!

    How could this be achieved?

    Thanks

    Regards,
    Neil

  2. #2
    SitePoint Wizard rctneil's Avatar
    Join Date
    Jun 2005
    Posts
    1,898
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Any ideas guys?

  3. #3
    reads the ********* Crier silver trophybronze trophy longneck's Avatar
    Join Date
    Feb 2004
    Location
    Tampa, FL (US)
    Posts
    9,854
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    i didn't follow what you want to do. can you also create a sample result set?
    Check out our new Industry News forum!
    Keep up-to-date with the latest SP news in the Community Crier

    I edit the SitePoint Podcast

  4. #4
    SitePoint Wizard rctneil's Avatar
    Join Date
    Jun 2005
    Posts
    1,898
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    For example, How can I duplicate the Little Leigh row in the areas table but alter the area name to be something like this: "Little Leigh - Copy". But I also need any rows of the stuff table copying which are related to that area row. So in this case it would be the first and last row of the middle table. But the main thing is that the new row copy obvisously can't have the same id so a new one will need to be used and that altered accordingly in the stuff table.

    I hope you will be able to help me with this now!

  5. #5
    SitePoint Wizard bronze trophy
    Join Date
    Jul 2008
    Posts
    5,757
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Code:
    INSERT INTO tbl_areas (name, years)
    SELECT CONCAT(name, ' - Copy')
         , years
      FROM tbl_areas
     WHERE area_id = 1
    Code:
    INSERT INTO tbl_stuff (area_id, shop_id)
    SELECT 4
         , shop_id
      FROM tbl_stuff
     WHERE area_id = 1
    the value 4 would come from mysql_insert_id()

  6. #6
    SitePoint Wizard rctneil's Avatar
    Join Date
    Jun 2005
    Posts
    1,898
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks, can you just explain how each one works as in write out what it is saying in a sentence.

  7. #7
    SitePoint Wizard bronze trophy
    Join Date
    Jul 2008
    Posts
    5,757
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Normally you insert by hardcoding the values
    Code:
    INSERT INTO tbl_areas (name, years) VALUES ('foo', 11)
    INSERT ... SELECT syntax tells the database that you aren't giving it the values, but instead you're telling it how to find them. The select query is executed, and the result is used as the values. Each row the select statment produces will execute the insert statement. The number of columns should match.

    CONCAT() just adds strings together.

    Equivalent functionality using php to help clarify
    PHP Code:
    $sql "
    SELECT name
         , years
      FROM tbl_areas
     WHERE area_id = 1
    "
    ;
    $res mysql_query($sql);
    while (
    $row mysql_fetch_row($res)) {
        
    $row[0] = $row[0] . ' - Copy';
        
    $sql "INSERT INTO tbl_areas (name, years) VALUES ('$row[0]', $row[1])";
        
    mysql_query($sql);


  8. #8
    SitePoint Wizard rctneil's Avatar
    Join Date
    Jun 2005
    Posts
    1,898
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thankyou very much. This will come in very handy for a variety of tasks in my admin system

    Thanks

    Regards,
    Neil


Bookmarks

Posting Permissions

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