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.

you can do a while loop in stored procs. If you can not use an sp then don't — Drew
– Drew, Commented Jul 11, 2016 at 22:03
It would be best to normalize your schema, so you don't have 15 different sets of columns to process. They should be in 15 different rows of a table. — Barmar
– Barmar, Commented Jul 11, 2016 at 22:06
I totally wouldn't use this schema. No offense intended. You can't join nuttin' and the indexes, well, no strategy. — Drew
– Drew, Commented Jul 11, 2016 at 22:08
In the last 2 or 3 months there have been a few questions on a homeId and awayId for matches on the stack. I recollect a decent join strategy with those questions. A search is Here — Drew
– Drew, Commented Jul 11, 2016 at 22:21

Drew · Accepted Answer · 2016-07-11 22:17:39Z

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

edited Jul 11, 2016 at 22:17

Drew

25k10 gold badges47 silver badges81 bronze badges

answered Jul 11, 2016 at 22:05

Aaron Adrian

5293 silver badges13 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Daniel Underwood Over a year ago

Thanks very much. Looks like I need to re-evaluate that side of the database. I have a teams, players and scores and venues table. The home_id, away_id, etc all point towards the players.id foreign key, but there's a lot of players, and a lot of scores, hence this getting a bit unwieldly.

Collectives™ on Stack Overflow

MYSQL For/While Loop

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related