SitePoint Sponsor

User Tag List

Results 1 to 17 of 17
  1. #1
    SitePoint Zealot
    Join Date
    Oct 2005
    Posts
    128
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    About using count() in PHP MySQL WHERE clause?

    Alright, I have a mysql table like the following.

    tbl_players
    Code:
    players		team		wins		losses
    
    john		leader		8		1
    frankie		john		20		3
    sally		john		1		10
    williams	frankie		10		5
    clarisa		williams	1		2
    What I want to do is get the players who are not on teams. I mean the following result:

    Code:
    players		team		wins		losses
    
    sally		john		1		10
    clarisa		williams	1		2
    I thought of using this...

    PHP Code:
    mysql_query("SELECT * FROM `tbl_players` WHERE `count(team)`<='0' GROUP BY `team`"); 
    Unfortunately I think both the syntax and logic are wrong. Any ideas?

  2. #2
    SitePoint Wizard bronze trophy
    Join Date
    Jul 2008
    Posts
    5,757
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    How do you determine that a player is not on a team? In your example, both of the resulting rows have a team.

  3. #3
    SitePoint Wizard bronze trophy
    Join Date
    Jul 2006
    Location
    Augusta, Georgia, United States
    Posts
    4,194
    Mentioned
    17 Post(s)
    Tagged
    5 Thread(s)
    If they aren't on a team shouldn't the team column be null?

    'SELECT * FROM tbl_players t WHERE t.team IS NULL'

  4. #4
    SitePoint Zealot
    Join Date
    Oct 2005
    Posts
    128
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    No I mean they don't have anyone in their own team or rather those that don't have anyone with them as pair team or so. For example consider there are two boxes the player is the first box and the "teams" are the second box. Here we need to get those that are in the first box but not in the second. Makes more sense?

    Sorry about that, I really am bad at naming fields.

    Note: I also i found that I can't use count() or such in WHERE clause, ow.

  5. #5
    SitePoint Wizard bronze trophy
    Join Date
    Jul 2008
    Posts
    5,757
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I don't understand.

  6. #6
    SitePoint Evangelist -T-'s Avatar
    Join Date
    Jun 2002
    Posts
    444
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    It's a parent child scenario, and you want to list everyone who doesn't have a child?

    This should do it
    Code:
    SELECT p.* FROM tbl_players p WHERE (SELECT count(pl.team) FROM tbl_players pl WHERE pl.team = p.players) < 1
    This needs optimization, but I think it shows the logic behind it pretty well
    chrome is a wrapper that combines a browser with spyware

  7. #7
    SitePoint Wizard bronze trophy
    Join Date
    Jul 2006
    Location
    Augusta, Georgia, United States
    Posts
    4,194
    Mentioned
    17 Post(s)
    Tagged
    5 Thread(s)
    SELECT * FROM tbl_players GROUP BY team HAVING COUNT(team)=1;

    That will give you all the players of teams that only have one person.

    all the people that are on teams of more then one person:

    SELECT * FROM tbl_players GROUP BY team HAVING COUNT(team)>1;


    all the people that are on teams of more then 5 people:

    SELECT * FROM tbl_players GROUP BY team HAVING COUNT(team)>5;

    I think you get the idea.

  8. #8
    SitePoint Zealot
    Join Date
    Oct 2005
    Posts
    128
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Edit: oops.. I'll read the responses #6 - #7.

    ok suppose i have this table...

    tbl_players
    Code:
    box1		box2
    
    john		leader
    frankie		john
    sally		john
    williams	frankie
    clarisa		williams
    What I want to do is get the names of those who are on the first box but not on the second box. I mean to get sally and clarisa. they are on the first box but not on the second. Also there are multiple names of the same in box2 like john so I think i'll need to use GROUP BY. Umm its really difficult to express what i'm thinking but I'm trying.

  9. #9
    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)
    Quote Originally Posted by VigilPHP View Post
    No I mean they don't have anyone in their own team or rather those that don't have anyone with them as pair team or so. For example consider there are two boxes the player is the first box and the "teams" are the second box. Here we need to get those that are in the first box but not in the second. Makes more sense?
    no

    your solution will most likely involve a LEFT OUTER JOIN with an IS NULL test

    however, you'll need to explain your data better

    i looked at the sample data you provided and i cannot figure out what you're doing

    perhaps show better sample data? or give a better explanation of why sally and clarissa should be chosen
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  10. #10
    SitePoint Wizard bronze trophy
    Join Date
    Jul 2006
    Location
    Augusta, Georgia, United States
    Posts
    4,194
    Mentioned
    17 Post(s)
    Tagged
    5 Thread(s)
    read and try out post #7

    When you want to group data and filter the group based on a aggregate function you need to use Having.

  11. #11
    SitePoint Zealot
    Join Date
    Oct 2005
    Posts
    128
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks everyone, I'm trying out your suggestions now.

  12. #12
    SitePoint Zealot
    Join Date
    Oct 2005
    Posts
    128
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Great! -T- was right on the mark!

    I figure my sample data was rather dull, assume an affiliate referring data (why didn't I explain with that one first? *silly*) Here the first columns are the "member ids" of the affiliates and the second column are the "sponsors" the affiliate who referred that affiliate. What I wanted to find was affiliates that have 0 sponsorships in their "downline".

    In other words find the lazy ones that never sponsored any sales... More illustrative? I don't know the use of subqueries but -T-'s suggestion works copy-paste.

    Now i'll move on to #7 response to see if it is a simpler solution.

  13. #13
    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)
    LEFT OUTER JOIN solution (probably faster than -T-'s correlated subquery) --
    Code:
    SELECT this.* 
      FROM tbl_players AS this 
    LEFT OUTER
      JOIN tbl_players AS that
        ON that.team = this.players
     WHERE that.team IS NULL
    oddz, you simply must stop using GROUP BY along with the dreaded, evil "select star" -- you simply aren't always going to get what you think you're going to get in the other columns (the columns that aren't mentioned in the GROUP BY clause)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  14. #14
    SitePoint Wizard bronze trophy
    Join Date
    Jul 2006
    Location
    Augusta, Georgia, United States
    Posts
    4,194
    Mentioned
    17 Post(s)
    Tagged
    5 Thread(s)
    The evil start is only there because that is what was in the original post. Why is GROUP BY bad?

  15. #15
    SitePoint Evangelist -T-'s Avatar
    Join Date
    Jun 2002
    Posts
    444
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    thanks for the optimization rudy

    Could you elaborate a bit more on the comment to oddz? thats news to me, and I'm intrigued
    chrome is a wrapper that combines a browser with spyware

  16. #16
    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)
    Quote Originally Posted by -T- View Post
    Could you elaborate a bit more on the comment to oddz? thats news to me, and I'm intrigued
    for a good explanation, see GROUP BY and HAVING with Hidden Columns
    Do not use this feature if the columns you omit from the GROUP BY part are not constant in the group. The server is free to return any value from the group, so the results are indeterminate unless all values are the same.
    let's take the last example oddz offered --

    SELECT * FROM tbl_players GROUP BY team HAVING COUNT(team)>5;

    this query will take all the rows in the table, arrange them into groups by team, and then collapse each group's rows into one aggregate row for each team

    so if several players are on a team, which player's values for the non-grouping columns will be returned when all the rows are collapsed?

    obvioulsy, these values are not the same for all rows in the group (they are different for each player)

    in theory, mysql could return the first player's id with the second player's name with the third player's jersey number, etc.

    in practice, mysql will likely return a consistent row (all values from the same player) but it is not guaranteede

    and besides, logically it doesn't make much sense to return any random player's data when you want one row per team

    i've seen people (mis)use this "feature" of mysql many, many times -- most often, they get unexpected results when, for example, they want the title of the latest article in each category

    SELECT category, title, MAX(published) FROM articles GROUP BY category

    invariably, the title that is returned isn't the one that corresponds to the MAX(published), and that's because, as the documentation says, "The server is free to return any value from the group, so the results are indeterminate unless all values are the same."


    does this help?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  17. #17
    SitePoint Evangelist -T-'s Avatar
    Join Date
    Jun 2002
    Posts
    444
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    great thanks
    chrome is a wrapper that combines a browser with spyware


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
  •