[SOLVED] Dates inbetween a set period?

[Mr Chris]

Hello,

 

I'm trying to work out the logic behind a query i'm running for getting football players in a query depending on the season they left or joined, but can't get my head around it.

 

I have one table named players

 

Name          | Joined    | Left    |

-------------------------------------

John Jones    | 20082009  | 20082009 |

Mark Wright  | 19961997  | 20012002 |

 

Now say I select a football season say the 2000-2001 season, so the code i'm using for my query is 20002001

 

How can I say get all the players in the database who:

 

- joined in this season

- left this season

- Or are somewhere inbetween the joined and left figure

 

Thanks

[kickstart]

Hi

 

Would probably be easier if you had a specific date when the season started / ended.

 

However this post from a few days ago dealt with a similar issue and might help:-

 

http://www.phpfreaks.com/forums/index.php/topic,252928.0.html

 

All the best

 

Keith

[Mr Chris]

Thanks kickstart.

 

Yeah that would be easier, but i'm dealing with players also from the 1980's so only have patchy records.

 

I've got the query working, apart from one thing:

 

SELECT player_id, player_name, current_player, substring_index(player_name,' ',-1) as surname FROM players 
                WHERE (season_start >= '20072008' AND season_start <= '20072008')
                OR (season_end >= '20072008' AND season_end <= '20072008')
                ORDER by surname

 

But say I queried the season 20072008 and the player had not left the club yet ie:

 

Name          | Joined    | Left    |

-------------------------------------

John Jones    | 20062007  |        |

 

So it's left Blank and has therefore played in the 20072008 season.  How could I deal with that in my query?

 

Thanks for your help

[kickstart]

Hi

 

2 simple ways, but neither particularly elegent.

 

Either add an extra check with an OR Left IS NULL, with brackets around it and the relevant part of the existing clause (to avoid issues with the order it processes the ANDs and ORs).

 

Or use IFNULL on the field that might be null and use a default date far in future to force it into range.

 

Not quite sure on your SQL as you seem to have a couple of columns seasons_start and seasons_end that do not seem to match up with anything on your tables.

 

All the best

 

Keith

[Mr Chris]

Thanks, but still don't quite get it:

 

Take this:

 

SELECT player_id, player_name, current_player, substring_index(player_name,' ',-1) as surname FROM players 
                WHERE (season_start >= '20072008' AND season_start <= '20072008')
                OR (season_end >= '20072008' AND season_end <= '20072008' OR season_end = '99999999')
                ORDER by surname

 

I've made all players that are still at the club have a default value of 99999999

 

Name          | season_start  | season_end    |

-------------------------------------

John Jones    | 20062007        |    99999999    |

 

 

So say we have season 20072008 the above query should check the season_start and then the season_end as well to see if 20072008 is somewhere between 20062007 and 99999999 which it is?

[kickstart]

Hi

 

You check that season start is >= and <= the same value, which is effectively checking it is = to that value.

 

SELECT player_id, player_name, current_player, substring_index(player_name,' ',-1) as surname FROM players

                WHERE (season_start <= '20072008' AND (season_end >= '20072008' OR season_end = '99999999'))

                ORDER by surname

 

That might do the job (the OR season_end = '99999999') is probably not really required if you have set the default value to 99999999).

 

All th ebest

 

Keith

[Mr Chris]

Thanks so much!