SitePoint Sponsor

User Tag List

Results 1 to 8 of 8
  1. #1
    JavaScript Guru (Big Ego) Arielladog's Avatar
    Join Date
    Jul 1999
    Location
    SC, USA
    Posts
    390
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    MySQL Query Confusion

    Hey guys,

    I can't seem to figure out what my MySQL query should be. Here's the basic layout of the database (atleast the important parts I want to retrieve):

    Code:
    Members (this table holds member information)
    ID
    FN (First Name)
    LN (Last Name)
    City (City)
    HE (Home E-Mail)
    WE (Work E-Mail)
    WID (Work ID--the ID corresponding to the Work Table)
    
    Work (this table holds work information)
    ID
    Name
    
    Assoc (this table holds the different associations people could join...they could join 0 or more)
    ID
    Name (Name of association)
    
    MTA (Member ID corresponds to 0 or more Association ID)
    MID (Member ID)
    AID (Association ID)
    Now, the part that's throwing me the loops is the MTA table. What I want to do is to be able to retrieve all that info and be able to order by each thing.

    Here's the code I'm using:

    SELECT members.ID AS UID, LN, FN, members.City AS MCity, HE, WE, members.WID, work.ID, work.Name AS WName, assoc.Name AS AName FROM members, work, assoc, MTA WHERE members.WID=work.ID AND MTA.MID=members.ID AND MTA.AID=assoc.ID GROUP BY UID ORDER BY LN, FN LIMIT 0,20

    But when I go:

    PHP Code:
    while($result mysql_fetch_array($sql)){
    echo(
    $result["AName"]);

    It only returns the first association they are in. How can I get it to return them all...I tried:

    PHP Code:
    echo($result["AName"][1]); 
    But that just returned the second letter in the first association the user joined.

    aDog

  2. #2
    SitePoint Zealot Alarion's Avatar
    Join Date
    May 2001
    Location
    Virginia
    Posts
    126
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    hmm.. try issuing that SQL into the MYSQL CLI (command line interface).

    this is usually accessed by typing "mysql" at the command prompt.

    Anyhow, I am suspecting you might need a LEFT JOIN on that one.. but not sure.

    Play with it int he MYSQL CLI so you can see ALL the results returned without going through the script.

    if you still can't figure it out, lemme know and I will create the tables on my test database and fiddle with it
    -=Alarion=-
    Protollix - Linux hosting from $3.95/m

  3. #3
    JavaScript Guru (Big Ego) Arielladog's Avatar
    Join Date
    Jul 1999
    Location
    SC, USA
    Posts
    390
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hey,

    I've read about joins, tried to use them here, but I can't seem to get it to work. I guess as my MySQL book explains, it's confusing and comes with experience, which I'm lacking.

    aDog

  4. #4
    Node mutilating coot timnz's Avatar
    Join Date
    Feb 2001
    Location
    New Zealand
    Posts
    516
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I'm of no help in figuring out why it does that, but try stripping out everything from your query and starting again. Thus adding another thing to select and another table and WHERE clause one at a time, to try to see where your problem lies. My guess is that you are telling it to only select one row by mistake.

  5. #5
    JavaScript Guru (Big Ego) Arielladog's Avatar
    Join Date
    Jul 1999
    Location
    SC, USA
    Posts
    390
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    But how can I tell it to select more than one value for associations???

    aDog

  6. #6
    SitePoint Zealot Alarion's Avatar
    Join Date
    May 2001
    Location
    Virginia
    Posts
    126
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    it might be better to just use two queries here. If you select everything together, you will be getting back alot of repeating data. What you might do is use the first query to get the member info, and use the second query to get the associations

    first query:
    Code:
    select * from members, work where work.ID = members.WID AND members.ID = ?
    and the second:
    Code:
    select * from MTA LEFT JOIN assoc on assoc.ID = mta.AID where mta.MID = ?
    give those a try. I don't know if that is correct, but it looks right
    -=Alarion=-
    Protollix - Linux hosting from $3.95/m

  7. #7
    Talk to the /dev/null Theiggsta's Avatar
    Join Date
    Mar 2001
    Location
    Tampa, FL
    Posts
    376
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    makesure a value goes where the ? is over there.

  8. #8
    JavaScript Guru (Big Ego) Arielladog's Avatar
    Join Date
    Jul 1999
    Location
    SC, USA
    Posts
    390
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hey,

    What a MySQL expert told me to do was that it was impossible alone with MySQL. Instead, he told me to take out the group by clause. This gave me more rows than I needed, but each row had a different association. Then, with PHP, I detect for the same member ID and combine associations if so.

    Thanks for all the help,
    aDog


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
  •