[SOLVED] Need Assistance With A Query

[cfresia]

Hello.  I'm developing a site for my hockey league and its come down to this last query.  I need to pull individual team schedules from a table showing both, home and away games.  This is what I currently have as it will pull the home games, but not the away games.  With that said, I can change the home_id to away_id and the query will pull the away games.  I can't get it to work when I have both fields identified.

 

Here is the query pulling the home games...

 

$query = "SELECT DATE_FORMAT(sched_date, '%a %c/%d/%Y') AS gdate, DATE_FORMAT(sched_time, '%h:%i %p') AS gtime, ht.t_name AS home, vt.t_name AS away FROM schedule, teams ht, teams vt  WHERE ht.t_id = home_id AND vt.t_id = away_id AND home_id = $team ORDER BY sched_id ASC LIMIT $recordstart, $pagesize";

 

Here is one of the many things I've tried, but haven't had any success.  It returns an empty set.

 

$query = "SELECT DATE_FORMAT(sched_date, '%a %c/%d/%Y') AS gdate, DATE_FORMAT(sched_time, '%h:%i %p') AS gtime, ht.t_name AS home, vt.t_name AS away FROM schedule, teams ht, teams vt  WHERE ht.t_id = home_id AND vt.t_id = away_id AND home_id = $team AND away_id = $team ORDER BY sched_id ASC LIMIT $recordstart, $pagesize";

 

Any assistance would be appreciated.

 

V/R

 

CJ

[fenway]

First, let's make sure that the queries are correct -- echo them to the screen after variable interpolation.

 

Second, what do you expect them to return? 

 

Third, you're effectively using inner joins (which you should explicitly use), so maybe there's a failing join condition -- switching to left join will clarify this.

[roopurt18]

I have to wonder why you have two separate tables for teams.  It sounds like you are almost complete with your project, but if it's at all feasible I'd try to restructure your database design to have only a single `teams` table.

 

That being said, there is at least one solution to your problem that I'm aware of, although I'm not sure it's the best one possible.

<?php

  // This SQL statement will pull home games
  // This is just a direct copy of the first statement in your original post
  // Note I've removed the ORDER BY and LIMIT portion
  $sql_home = "
    SELECT 
      sched_id,
      DATE_FORMAT(sched_date, '%a %c/%d/%Y') AS gdate,
      DATE_FORMAT(sched_time, '%h:%i %p') AS gtime,
      ht.t_name AS home, vt.t_name AS away 
    FROM schedule, teams ht, teams vt  
    WHERE 
      ht.t_id = home_id AND 
      vt.t_id = away_id AND 
      home_id = $team 
  ";

  // This SQL statement will pull away games
  // I've inferred this statement from the previous one
  $sql_away = "
    SELECT 
      sched_id,
      DATE_FORMAT(sched_date, '%a %c/%d/%Y') AS gdate,
      DATE_FORMAT(sched_time, '%h:%i %p') AS gtime,
      ht.t_name AS home, vt.t_name AS away 
    FROM schedule, teams ht, teams vt  
    WHERE 
      ht.t_id = home_id AND 
      vt.t_id = away_id AND 
      away_id = $team 
  ";
  
  // If your version of mysql supports UNIONs, we can now combine the results
  // of both queries into a single query and apply the ORDER BY and LIMIT
  $sql = "
    ($sql_home) UNION ($sql_away)
    ORDER BY 
      sched_id ASC 
    LIMIT $recordstart, $pagesize
  ";
?>

 

A couple other general notes: 

• Make sure you are cleaning any data before inserting it into the database with mysql_real_escape_string().
  
• You should be enclosing your column and table names in backticks, i.e. the ` (unshifted tilde)
  
• I used to use the FROM schedule, teams ht, teams vt syntax myself until it bit me in the ass; I recommend using the FROM table1 t1 INNER JOIN table2 t2 ON ... INNER JOIN table3 t3 ON ... syntax
  
• I know its not technically required, but when using a PHP variable inside double quotes I recommend enclosing it within curly brackets, it will help you avoid hard to find bugs
  
• It *might* make more sense to order your result set by the games' schedule dates rather than their auto_incrementing id in the database; the reason for this is if a date is modified (due to weather or something else), it will still appear in the correct order on the page
  

 

(edit) Also, the solution I provided will likely suffer performance penalties on large data sets.  AFAI understand MySQL, the two individual queries should run quickly with proper indexing, but performing the UNION and then trying to ORDER and then LIMIT it will require a temporary table and that's where you take the performance hit.  I suppose leaving the ORDER BY and LIMIT parameters in the two queries will help somewhat in this area.  Someone correct me if I'm wrong, but here is my gut instinct.  Let's say there are 1000 records pertaining to the particular team, but we are only displaying 50 per page.  The way I have structured things the temp table will wind up with all 1000 records in it.  However, if the ORDER BY and LIMIT clauses are left in the "inner" queries, each one will at most return 50 records, which means the temp table would only have at most 100 records to sort through.

[cfresia]

It works!!!  Thank you so much for the help.  I greatly appreciate it.  I only have one teams table.  I had to create two aliases since the query was pulling the same name for both home and away games (Chuggers vs Chuggers).  By using two aliases on the teams table it corrected the problem (Chuggers vs Misers).  That's the only way I knew how to get around that problem.

 

Thank you for the additional suggestions.  They're helpful.  Especially the one suggesting I order by the date.  That can save some headaches later.

 

Take care,

 

CJ

[roopurt18]

Oh.  I r brain dead.  For some reason I saw two teams tables.

 

This might do it in a single query:

SELECT
  DATE_FORMAT(s.`sched_date`, '%a %c/%d/%Y') AS `gdate`,
  DATE_FORMAT(s.`sched_time`, '%h:%i %p') AS `gtime`,
  IF(
    `ht`.`t_id`={$team},
    `ht`.`t_name`,
    `vt`.`t_name`
  ) AS `home`,
  IF(
    `vt`.`t_id`={$team},
    `vt`.`t_name`,
    `ht`.`t_name`
  ) AS `away`
FROM
  `schedule` s
INNER JOIN `teams` `ht` ON s.`home_id`=`ht`.`t_id`
INNER JOIN `teams` `vt` ON s.`away_id`=`vt`.`t_id`
WHERE
  `ht`.`t_id`={$team} OR
  `vt`.`t_id`={$team}
ORDER BY 
  s.`sched_date`, s.`sched_time`
LIMIT {$recordstart}, {$pagesize}

 

(edit) I think I submit everything too early.  If that doesn't work and you don't mind, you can do an SQL dump of your `schedule` and `teams` tables and attach them to a post.  Then I could run the query myself until it does work, just because I enjoy the challenge!  RAWR

[cfresia]

I tried the single query, but it failed.  I'm not pulling large amounts of data from the db so there shouldn't be any delay.  There's only 160 games between two divisions so the most getting pulled at one time would be 80.

 

I tried to order by date and time, but it arranged it by the day of the week.  I've cut and paste the results below.  How can I get it to order off the time and day? 

 

Fri 1/12/2007  10:30 PM  Better Off Dead  Kings

Fri 2/09/2007 09:00 PM Better Off Dead Jets

Fri 2/23/2007 10:30 PM Better Off Dead Patriots

Mon 1/08/2007 09:00 PM Patriots Better Off Dead

Mon 1/22/2007 09:00 PM Better Off Dead Voyagers

Mon 1/29/2007 10:30 PM Better Off Dead Chuggers

Mon 2/05/2007 10:30 PM Misers Better Off Dead

Mon 2/19/2007 10:30 PM Swarm Better Off Dead

Sat 10/06/2007 09:00 PM Misers Better Off Dead

Sat 10/20/2007 10:30 PM Swarm Better Off Dead

Sat 11/10/2007 09:00 PM Voyagers Better Off Dead

Sat 11/17/2007 09:00 PM Chuggers Better Off Dead

Sat 12/01/2007 10:30 PM Better Off Dead Misers

Sat 12/15/2007 09:00 PM Better Off Dead Swarm

Sat 9/22/2007 10:30 PM Voyagers Better Off Dead

Sat 9/29/2007 10:30 PM Better Off Dead Chuggers

Tue 10/09/2007 09:00 PM Better Off Dead Jets

Tue 10/23/2007 10:30 PM Better Off Dead Patriots

Tue 10/30/2007 09:00 PM Kings Better Off Dead

Tue 12/04/2007 10:30 PM Jets Better Off Dead

 

Thanks again for the help.  I've learned something today. 

[roopurt18]

Make sure you're ordering by sched_date and sched_time in the schedule table and NOT the gdate and gtime fields you created, as they are formatted and will be sorted as text.

 

If you're using phpMyAdmin, if you click on the database name so that all of the tables are listed in the content frame, there is an export tab.  You can choose to export the two tables (and only the two tables, I don't care about your other data) into a .gzip file and attach them to a post (or send them to me via IM).  That's only if you want it done in a single query.