I need some help with a select statement

[bschultz]

I have two tables...one is scheduled_umps and one is games.  Both tables have a column 'game_id'. 

 

I need to select how many games a person is scheduled for in a given time frame.

 

Here's what I have for the select...and it's not working (You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHERE scheduled_umps.ump_id = '34' AND games.game_id = scheduled_umps.game_id AN' at line 1)

 

<?php
$today_is_this_date = date('Y-m-d');
$four_months_ago = date('Y-m-d', strtotime('-4 months', strtotime($today_is_this_date)));
$four_months_from_now = date('Y-m-d', strtotime('+4 months', strtotime($today_is_this_date)));

/// How many games have you worked? 
//$query = "SELECT COUNT(ump_id) FROM scheduled_umps WHERE `ump_id` = '$_SESSION[ump_id]'";   ///this one selects how many games you've worked NO MATTER WHAT THE DATE

$query = "SELECT scheduled_umps.game_id, games.game_id, COUNT(scheduled_umps.ump_id) WHERE scheduled_umps.ump_id = '$_SESSION[ump_id]' AND games.game_id = scheduled_umps.game_id AND games.game_id BETWEEN $four_months_ago AND $four_months_from_now";

$result = mysql_query($query) or die(mysql_error());
while($row = mysql_fetch_array($result)){
echo "You have been scheduled on <b>". $row['COUNT(ump_id)'] ."</b> dates for the $_SESSION[association_name].";
}
?>
[\code]

Any ideas where I'm going wrong?  Thanks for the help...I HATE multi-table selects!

[codebyren]

Well, for a start you seem to be missing the FROM `table_name` JOIN `another_table_name` ON ... part of your statement.

 

Try simplify the statement - then once you know you're on the right track and can see results from both tables,  add the BETWEEN etc.

[gizmola]

Let's clarify something first.  You have this criteria:

 

games.game_id BETWEEN $four_months_ago AND $four_months_from_now

 

This seems to indicate that the games.game_id is both the primary key of the games table AND a DATETIME column storing the value corresponding with the date/time of the game.  Surely this can't be true?

 

Usually on a scheduling app you would have "startdate" + "enddate" or at least a "startdate" column for this. 

 

Until you can clarify this, I can't provide you anything I would expect to work.

 

[bschultz]

The table 'games' contains the column game_id (which is PRIMARY, AI, INT).  That table also contains column 'date' (which is DATE).

 

Then on table scheduled_umps, there is a column 'game_id' (which is INT...but NOT an index).  That table also contains column 'ump_id'.

 

So, I need a select where games.game_id = scheduled_umps.game_id (game id from both tables matches)  AND games.date falls between the given values, and the scheduled_umps.ump_id = $_SESSION[ump_id].

[Psycho]

Then on table scheduled_umps, there is a column 'game_id' (which is INT...but NOT an index).  That table also contains column 'ump_id'.

You should index it if you plan on using it for JOINs

 

gizmola's response was based upon the fact that you have a WHERE clause that checks if game_id is a value between two date values - when you should be checking if the "date" is between those two values.

 

Give this a try

$query = "SELECT COUNT(g.game_id)

          FROM scheduled_umps AS u
          JOIN games AS g USING(game_id)

          WHERE u.ump_id = '$_SESSION[ump_id]' 
            AND g.date BETWEEN $four_months_ago AND $four_months_from_now

          GROUP BY u.ump_id";

[bschultz]

Thanks Pysco!

 

Unknown column 'g.date' in 'where clause'

 

Here's the echo of $query

 

SELECT COUNT(g.game_id) FROM scheduled_umps AS u JOIN games AS g USING(game_id) WHERE u.ump_id = '34' AND g.date BETWEEN 2011-10-13 AND 2012-06-13 GROUP BY u.ump_id

 

Oops...that column is called day...not date.  Once I fixed that, it's returning 0 rows...

[bschultz]

I have also added an index to the column game_id on games

[Psycho]

I think your dates need to be enclosed in quotes

[bschultz]

That did it...thanks a bunch!