Daniel Underwood Daniel Underwood - 4 months ago 9
MySQL Question

MYSQL For/While Loop

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

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