SitePoint Sponsor

User Tag List

Results 1 to 3 of 3

Thread: many-to-many

  1. #1
    SitePoint Guru momos's Avatar
    Join Date
    Apr 2004
    Location
    Belgium
    Posts
    919
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question many-to-many

    I'm in search for a more performant way to implement a many to many in php; which essentially comes down to trying to cut the amount of queries or make them more performant(correct me if I'm wrong).

    So, this is what I have:

    T1
    (
    id INT UNSIGNED AUTO_INCREMENT,
    name VARCHAR(50),
    rating TINYINT(100),
    PRIMARY KEY(id)
    )


    T2
    (
    id INT UNSIGNED AUTO_INCREMENT,
    name VARCHAR(50),
    valid BOOL,
    comment TEXT,
    PRIMARY KEY(id)
    )

    T1_T2
    (
    T1_id INT UNSIGNED,
    T2_id INT UNSIGNED,
    PRIMARY KEY(T1_id,T2_id)
    )

    With this I need 5 queries for an insert:
    1. INSERT INTO T1 VALUES("name",50);
    2. $x=mysql_insert_id(); //for T1
    3. INSERT INTO T2 VALUES("name",0,"comment");
    4. $y=mysql_insert_id(); //for T2
    5. INSERT INTO T1_T2 VALUES($x,$y);
    This works, but if you have several many-to-many relationships on one page, it can be really hard on your server. Still this is the shortest way I know... Does anyone know another way?

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    yes you would have to do all 5 steps (note: only 3 of them are queries!) but only if the rows for T1 and T2 were new

    if you want to relate an existing T1 to an existing T2, it's only one step, assuming you have some way of knowing, like from a dropdown menu on a web form, which T1 and which T2 you were relating
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Guru momos's Avatar
    Join Date
    Apr 2004
    Location
    Belgium
    Posts
    919
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks, wanted to check...


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
  •