First off, I'm no SQL expert. In fact, I don't really know very much SQL at all, and the below is just something I cobbled together from my limited knowledge. Don't be too mean!
Anyway, I am trying to design an application for my school, for handling scores for sporting events. This is currently done on an excel sheet, but it gets very complex. So I am writing a web app for it using PHP and SQL.
For this app, I am currently designing a front end where it totals up all the points scored per house and displays them. There are 4 houses, Roddick, Whittle, Brunel and Newton.
My DB is set up as follows:
Students
idStudent
| studentname | studentgender | studentyear | studenthouse |
| 1 | foo bar | male | 11 | roddick |
| 2 | foo bar | female
| 11 | whittle |
athletics_results
idResult
| idStudent | idEvents | time | position | points |
| 1 | 1 | 1 | 1:03:56 | 1 | 14 |
| 2 | 2 | 2 | 0:46:12 | 2 | 13 |
idStudent is a foreign key, as is idEvents.
team_results
idTeamResult
| idEvents | house | position | points |
| 1 | 1 | roddick | 1 | 14 |
| 2 | 2 | whittle | 2 | 13 |
idEvents is the foreign key. In team events, only the house is needed, not an individual student name.
events
idEvents
| EventName | eventType |
| 1 | roddick | 1 |
| 2 | whittle | 2 |
----
I hope that makes sense. Now, for the front end, I need to total up all the points scored per house to display it in a screen like this:
Currently, I am doing this very strangly, but it works. This was the only way I could get it to work with my limited knowledge:
PHP:
$link = mysqli_connect('localhost', 'root', '', 'sportsday') or die("Error: " . mysqli_error($link));
$roddick_query = mysqli_query($link, "SELECT SUM(points) FROM athletics_results JOIN students ON students.idStudent=athletics_results.idStudent WHERE students.studenthouse = 'roddick'") or die ('Unable to run query: '.mysqli_error($link));
$roddick_query_team = mysqli_query($link, "SELECT SUM(points) FROM team_results WHERE team_results.house = 'roddick'") or die ('Unable to run query: '.mysqli_error($link));
$brunel_query = mysqli_query($link, "SELECT SUM(points) FROM athletics_results JOIN students ON students.idStudent=athletics_results.idStudent WHERE students.studenthouse = 'brunel'") or die ('Unable to run query: '.mysqli_error($link));
$brunel_query_team = mysqli_query($link, "SELECT SUM(points) FROM team_results WHERE team_results.house = 'brunel'") or die ('Unable to run query: '.mysqli_error($link));
$newton_query = mysqli_query($link, "SELECT SUM(points) FROM athletics_results JOIN students ON students.idStudent=athletics_results.idStudent WHERE students.studenthouse = 'newton'") or die ('Unable to run query: '.mysqli_error($link));
$newton_query_team = mysqli_query($link, "SELECT SUM(points) FROM team_results WHERE team_results.house = 'newton'") or die ('Unable to run query: '.mysqli_error($link));
$whittle_query = mysqli_query($link, "SELECT SUM(points) FROM athletics_results JOIN students ON students.idStudent=athletics_results.idStudent WHERE students.studenthouse = 'whittle'") or die ('Unable to run query: '.mysqli_error($link));
$whittle_query_team = mysqli_query($link, "SELECT SUM(points) FROM team_results WHERE team_results.house = 'newton'") or die ('Unable to run query: '.mysqli_error($link));
$r_row = mysqli_fetch_row($roddick_query);
$r_row_team = mysqli_fetch_row($roddick_query_team);
$roddick_score = $r_row[0] + $r_row_team[0];
$b_row = mysqli_fetch_row($brunel_query);
$b_row_team = mysqli_fetch_row($brunel_query_team);
$brunel_score = $b_row[0] + $b_row_team[0];
$n_row = mysqli_fetch_row($newton_query);
$n_row_team = mysqli_fetch_row($newton_query_team);
$newton_score = $n_row[0] + $n_row_team[0];
$w_row = mysqli_fetch_row($whittle_query);
$w_row_team = mysqli_fetch_row($whittle_query_team);
$whittle_score = $w_row[0] + $w_row_team[0];
As you can tell, I am currently querying the athletics table, and the team table for a sum of points, and then adding them in PHP. However, this is slow. It currently takes about a second to load on a local host, and there's not much in the DB at the moment. What I really need to do is combine the two queries per house into one, more efficient one.
Can any one help me? I hope I have given enough details!
Thanks so much,
Stephen
