Results 1 to 5 of 5
  1. #1
    Join Date
    May 2009
    Posts
    8

    Unanswered: getting both rows in a many to many type database

    Hi,

    I have a fairly simple question, but I can't get my head arround this. Here's the basics of my site.

    Friend-Party Agenda
    A _ a Table with items
    B _ a Table with replies to that item
    AB _ the table that links these two tables by the primairy key.

    When I send my query with PHP it looks like this:

    PHP Code:
    $id 3// just a random id to search for
    $query=
    "SELECT b.*  
    FROM ab
    LEFT JOIN b ON (b.id = ab.b_id)  
    WHERE (ab.a_id = 
    {$id})"
    Now this gives me all the replies that are stored in table b, but here comes the question:

    How can extend this query so that it will also send back the data of corresponding row a? This will be just 1 row.. I need it, very badly.

    I hope someone can help me out I know it's easier to do it with a parent ID stored for each reply, but I've read that this is a better way to do it.

    Greetings,

    Geit

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    SELECT a.*
         , b.*   
      FROM a
    INNER
      JOIN ab
        ON ab.a_id = a.id
    INNER
      JOIN b 
        ON b.id = ab.b_id   
     WHERE a.id = $id
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    May 2009
    Posts
    8
    oh right, I kinda tried that before without success, but now I see the problem wasn't with my query. I sorted everything with MYSQL_ASSOC, but both tables had same names in them. I didn't know they would overwrite eachother.

    Is there anyway I can use the result type MYSQL_ASSOC without this side effect? (thus not overwriting but putting them in seperate arrays?)

    Many thanks to you r937

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by geitvdplas View Post
    Is there anyway I can use the result type MYSQL_ASSOC without this side effect?
    sorry, i have no idea what you're talking about, as this appears to be a php function and i don't know php

    if you're having problems because there are columns with the same name in both table, you may assign column aliases in the SELECT statement

    change this:
    Code:
    SELECT a.*
         , b.*
    to this:
    Code:
    SELECT a.foo AS a_foo
         , a.bar AS a_bar
         , b.foo AS b_foo
         , b.bar AS b_bar
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    May 2009
    Posts
    8

    Smile Success

    This seems very logical and will surely work. I didn't know I could do that. Thanks for helping out a newbe

    ps:
    With PHP you can do mysql_fetch_array() to fetch a mysql result as an array. MYSQL_ASSOC is just a parameter for this fucntion. It means it will return an Associative array. (with strings as keys, instead of numbers)


Posting Permissions

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