SitePoint Sponsor

User Tag List

Results 1 to 8 of 8
  1. #1
    Always learning kigoobe's Avatar
    Join Date
    May 2004
    Location
    Paris
    Posts
    1,565
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Can anyone tell me what's wrong with this query please

    Code PHP:
    $kkdx = @mysql_query("select id, ".$kli." from secteur order by position") or die(mysql_error());
    while ($kkdx1=mysql_fetch_array($kkdx)) {
    print '<h5>'.$kkdx1[1].'</h5>';
    $kat_details=@mysql_query("
    select 
       members.lastname, 
       members.firstname, 
       members.title,  // title = member's field
       members.localization // localization = member's field
    from 
       members, 
       localization, // localization = seperate table
       title // title = seperate table
    where 
       members.Secteur = ".$kkdx1[0]." and   
       members.localization = localization.id 
    order by 
      localization.position, 
      title.position
    ");
    while ($abc=mysql_fetch_array($kat_details)) {
    print $abc['lastname'].' '.$abc['firstname'];
    }
    }

    I am getting much more rows than what I should get, not ordered as I want, etc ... totally messed up

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,347
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    you have a partial cross join

    the WHERE clause will return every member whose Secteur is the chosen value, then every localization that is associated with each member, then all titles for every member

    you're missing a join condition

    this would not happen if you were to use explicit JOIN syntax
    Code:
      from members
    inner
      join localization
        on localization.id = members.localization
    inner
      join title 
        on title.??? = ????
     where members.Secteur = ".$kkdx1[0]."
    also, you have a couple of columns in the ORDER BY that aren't in the SELECT -- while this is okay (as long as it's not a GROUP BY query), it doesn't make much sense

    finally, if i understand your perl code correctly, you seem to be doing a query inside a loop? that will perform very poorly
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    Always learning kigoobe's Avatar
    Join Date
    May 2004
    Location
    Paris
    Posts
    1,565
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    that will perform very poorly
    Really, what do you suggest here then?

    Basically I'm having a few fields like sect., loc., title, etc. in member's table, where I'm storing the id of different sects, locs, and titles.

    Then, i've tables like sect., loc., title, etc. where they are ordered by a field called position.

    I want to get them listed according to sectors, that's why I thought about the loop.

    I mean, I want to have members list in blocks, like

    || sector1 ||
    || loc1 ||
    ||member1 of loc1 ||
    || member2 of loc2 ||

    etc. Do you have any other suggestion that will perform better?

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,347
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    one query for all secteurs
    Code:
    select ...
      from secteur
    inner
      join members
        on members.Secteur = secteur".$kli."
    inner
      join localization
        on localization.id = members.localization
    inner
      join title 
        on title.??? = ????
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    Always learning kigoobe's Avatar
    Join Date
    May 2004
    Location
    Paris
    Posts
    1,565
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    wow, thanks Rudy. I'll give it a try .

  6. #6
    Always learning kigoobe's Avatar
    Join Date
    May 2004
    Location
    Paris
    Posts
    1,565
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Well, wonderful. This is giving the result very nicely. I bet, I would have needed n different queries to pull them together ...

    Code MySQL:
    SELECT secteur.id, secteur.ttl_fr, members.id, members.lastname, members.firstname, members.title, members.localization, localization.id, localization.ttl_fr, localization.rgb, title.id, title.ttl_fr, secteur.position, localization.position, title.position
    FROM secteur
    INNER JOIN members ON members.Secteur = secteur.id
    INNER JOIN localization ON localization.id = members.localization
    INNER JOIN title ON title.id = members.title
    ORDER BY secteur.position, localization.position, title.position

    Now, I'm getting results like -
    sect1 | member1 | loc1 | title
    sect1 | member2 | loc1 | title
    sect1 | member1 | loc2 | title, etc.

    Means, all are coming in the same line, where sects and locs are getting repeated with each member.

    I needed to display them as -

    Sect1
    Loc1
    memb1
    memb2
    membn
    Loc2
    memb1
    memb2
    membn
    Locn
    memb1
    memb2
    membn

    Sect2
    Loc1
    memb1
    memb2
    membn
    Loc2
    memb1
    memb2
    membn
    Locn
    memb1
    memb2
    membn

    Sectn
    ...
    ...
    ...

    Wondering if this can be done using mysql, or I will have to do that using php's if, else, and loop. Any suggestion? Thanks.

  7. #7
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,347
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    do it with php while looping over the results

    add this to the query:
    Code:
    ORDER
        BY secteur.ttl_fr, 
         , localization.ttl_fr
         , members.lastname, members.firstname
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  8. #8
    Always learning kigoobe's Avatar
    Join Date
    May 2004
    Location
    Paris
    Posts
    1,565
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks a lot again Rudy. Relief ...


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
  •