Query with multiple where clauses

[wouterblacquiere]

Hello,

 

I'm fairly new to php and I'm working on a website for a chess club. I'd like the user to select all of his or her personal results from a general results table.  The results table looks as follows:

 

id        name_white        name_black        result          season_id

 

1          john                    pete                    1-0              2010-2011

2          charles                steve                  0-1              2010-2011

3          richard                john                    1-0              2010-2011

etc

11        isaac                  john                    1-0              2011-2012

12        william                kate                    0-1              2011-2012

13        john                    bobby                  1-0              2011-2012

etc

 

Let us say that the user wants to see all the results of player john in the 2010-2011 season.  The following query (probably quite naively) gives me the error "supplied argument is not a valid MySQL result resource":

 

$result_personal = mysql_query("SELECT * FROM results WHERE name_white = '$seleted_name' AND name_black = '$selected_name' AND season id ='$selected_season'");

 

Could anyone help me steer in the right direction?  Should I use a JOIN query instead?  Or a IN query?

 

All the best and thanks in advance for your time,

 

Wouter.

[trq]

You have season id instead of season_id.

 

Having said that however, you likely want OR clauses instead of AND.

[wouterblacquiere]

Thanks very much for the tip. It worked beautifully!

 

However, I do not understand why it works. You would think that if I want the player's name that can appear in both the white and the black columns, surely I need the AND clause? Why does the OR clause show them both?

 

All the best,

 

Wouter.

[Mahngiel]

However, I do not understand why it works. You would think that if I want the player's name that can appear in both the white and the black columns, surely I need the AND clause? Why does the OR clause show them both?

 

Because you stated in your original post you wanted to retrieve ALL the records for a player.  If you wanted to only display the results requiring conditionally met circumstances (such as black and white) then AND would be appropriate.

[trq]

An AND clause would only find records where both name_white and name_black were the same.