SitePoint Sponsor

User Tag List

Results 1 to 7 of 7
  1. #1
    SitePoint Guru D-flyer's Avatar
    Join Date
    Jan 2001
    Location
    Near a computer
    Posts
    782
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Manyy-to-many and an INSERT-query, do i need a temp?

    Hi there,

    Gonna try to explain my problem. In my db model i got a many-to-many relation. So i have 3 tbl's where one joins the other 2.

    Now the flow of the front-end:
    page1 a <form> wich collect the data for tbl1 --->>> and with hidden fields the data goes to Page2
    page2 a <form> wich collect the data for tbl2 --->>> and with hidden fields the data(from this page and page1) goes to a preview page
    preview page --->shows all the data and the user can go back or submit the data.

    Now what is was wondering:
    To join the data in tbl3 i need the tbl1.id before i submit all the data, right???How do i accomplish this??

    Tips, suggestions

  2. #2
    killall -9 lusers
    Join Date
    Oct 2002
    Location
    Cincinnati, Ohio, USA
    Posts
    390
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Use several queries.

    1. Insert data into table 1.
    2. Get last ID and keep in a variable..
    3. Insert data into table 2.
    4. Get last ID and keep in a differetn variable.
    5. Insert IDs from the variables into table 3.

  3. #3
    SitePoint Guru D-flyer's Avatar
    Join Date
    Jan 2001
    Location
    Near a computer
    Posts
    782
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    thnks,

    So when a user decide to NOT post his data, say after page1, i need to somehow flush the data out of the db?? I don't like the idea of stuffing my db with incomplete

    I was thinking "temp". Get lastest ID and keep it in a temp variable.. or maybe the use of a sessions???

  4. #4
    SitePoint Wizard silver trophy Karl's Avatar
    Join Date
    Jul 1999
    Location
    Derbyshire, UK
    Posts
    4,411
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    If you've got a many-to-many relationship then you need to re-think your database design so that you eliminate many-to-many relationships between tables. The generally accepted way it to create a table that ties the other two together e.g.

    Table1 has many to many with Table2

    So you create Table3, which is just composed of the primary key of Table1 and the primary key of Table2, so you create a bridge between the tables storing the relationship and thus avoid a direct many-to-many relationship.
    Karl Austin :: Profile :: KDA Web Services Ltd.
    Business Web Hosting :: Managed Dedicated Hosting
    Call 0800 542 9764 today and ask how we can help your business grow.

  5. #5
    SitePoint Guru D-flyer's Avatar
    Join Date
    Jan 2001
    Location
    Near a computer
    Posts
    782
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Yep,
    I understand the principle how to convert many-to-many relations into a db-struture

    But the problem i described is one i can't get done. For example, in tble3 i want to correspond tbl1.id with tbl2.id[2] and tbl2.id[3]. And then with the flow i described earlier.

    Any more suggestions???

  6. #6
    SitePoint Wizard silver trophy Karl's Avatar
    Join Date
    Jul 1999
    Location
    Derbyshire, UK
    Posts
    4,411
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Sorry, my apologies, only skim read your post, looked more at the tables and flow, oops.

    One option is to run a cron job every hour that removes 1st stage data that is older than 1 hour and hasn't had 2nd stage data added.
    Karl Austin :: Profile :: KDA Web Services Ltd.
    Business Web Hosting :: Managed Dedicated Hosting
    Call 0800 542 9764 today and ask how we can help your business grow.

  7. #7
    SitePoint Author silver trophybronze trophy
    wwb_99's Avatar
    Join Date
    May 2003
    Location
    Washington, DC
    Posts
    10,653
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)
    What you really need here is a transaction. Just have it rollback on cancel.

    WWB


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
  •