MYSQL For/While Loop

Question

Is it possible to do a For or While loop in MYSQL?

I've got the following code extract, but the full code goes up to home_id_15, home_score_15, away_id_15 and away_score_15:

$query3 = '
SELECT match_date, fixture_id, COUNT(a.home) AS home, SUM(a.points) AS points FROM
    (  
      SELECT match_date, fixture_id, home_id_1 AS home, home_score_1 AS points FROM scores
      WHERE home_id_1 =' .intval($_REQUEST['ID']).'  
      UNION ALL  
      SELECT match_date, fixture_id, away_id_1 AS home, away_score_1 AS points 
      FROM scores
      WHERE away_id_1 =' .intval($_REQUEST['ID']).'  
      UNION ALL  
      SELECT match_date, fixture_id, home_id_2 AS home, home_score_2 AS points 
      FROM scores
      WHERE home_id_2 =' .intval($_REQUEST['ID']).'  
      UNION ALL  
      SELECT match_date, fixture_id, away_id_2 AS home, away_score_2 AS points 
      FROM scores
      WHERE away_id_2 =' .intval($_REQUEST['ID']).'  
      UNION ALL) a  
     GROUP BY match_date'

The first and second sub-SELECTS are basically being repeated until they reach 15.

This seems a bit long-winded and I was wondering if it's possible to use a loop in MYSQL to output

home_id_1, home_score_1, away_id_1, away_score_1 [up to] home_id_15, home_score_15, away_id_15, away_score_15

, respectively?

Thanks, Dan.

Answer 1

It looks like you might need to normalize your database a little bit more. Let's say you had 6 scores for each row. Instead of making each score a column, make a separate table called "scores" or something like that with a foreign key column and a score column. Then join the table with this scores table.

Example:

• TABLE: team

  • team_id
  • name

• TABLE: scores

  • team_id
  • score

SELECT t.*, s.score 
FROM team t
join scores s 
on t.team_id=s.team_id;

Todo: Add the concept of matches into your schema and the Join