0

I have made a score system, which sum up multiple points for a team. It's made using PHP, MySQL and HTML.

Each team can get one point (lets say a number between 1 and 10) pr. task. The system handles multiple tasks.

Each team is represented by a row in the table "teams". Each task is represented by a row in the table "tasks" Every point is represented by a row in the table "points".

Now I want to make a HTML which show all of these data in a single HTML table.

The structure of "teams" is

ID | TeamName

The structure of "tasks" is

ID | TaskName

The structure of "points" is

ID | Team | Task | Point

Now I want to create a table which look like the following:

Teams  | Task x   | Task y   | Total     |
-------|----------|----------|-----------|
Team 1 | 3 points | 1 point  | 4 points  |
-------|----------|----------|-----------|
Team 2 | 9 points | 2 points | 11 points |
-------|----------|----------|-----------|
Team 3 | 5 points | 8 points | 13 points |

I have made a solution which does, what I want. The problem is, that it's made with loops inside loops which slows the load. And that's critical because I have like 200 teams and 50 tasks.

So can anyone help me with a hint to do this smarter?

Improve this question
2
  • Try this Answer stackoverflow.com/questions/11943479/… Commented Jul 25, 2015 at 21:38
  • Thank you :) But my problem is not to get the data out, but to optimize the proces where I build the rows with teams and points. Today I do it like: Call that gets all teams in a while. For each team I find all points where team.id = points.team. But I think that process slows load time, so I'm searching for a method to do it smarter :) Commented Jul 25, 2015 at 21:54

1 Answer 1

Reset to default
0

Well... My first thought was to change the SQL query, to obtain the results in the same way as you would want to display them. However, that would make the query rather complex and changes the processing power from PHP to MySQL. The best method I can think of, still requires two loops and a smart storing of data.

For the teams and tasks table you can just select the data as is, and store the ID and name in some array.

$q = mysql_query( "SELECT * FROM teams" );
$q = mysql_query( "SELECT * FROM tasks" );

For the points table, you can store the tasks as subarray of teams, and the points as value of a task. Creating a 2 dimentional array.

$q = mysql_query( "SELECT * FROM points" );
while( $data = mysql_fetch_assoc( $q ) )
    $Points[ $data['Team'] ][ $data['Task'] ] = $data['Point'];

The variables will than look like this:

$Teams = array( 1 => 'Team 1', 2 => 'Team 2', 3 => 'Team 3' );
$Tasks = array( 1 => 'Task X', 2 => 'Task Y' );
$Points = array(
    1 => array(
        1 => 3,
        2 => 1
    ),
    2 => array(
        1 => 9,
        2 => 2
    ),
    3 => array(
        1 => 5,
        2 => 8
    )
);

And the most dynamic way to display them on your website would be as following.

echo '<table>';
echo '  <tr>';
echo '      <td>Teams</td>';
foreach( $Tasks AS $taskName ) {
    echo '      <td>' . $taskName . '</td>';
}
echo '      <td>Total</td>';
echo '  <tr>';
foreach( $Points AS $teamID => $arr ) {
    echo '<tr>';
    echo '  <td>' . $Teams[ $teamID ]  . '</td>';
    foreach( $arr AS $taskID => $arr2 ) {
        echo '  <td>' . $Points[ $teamID ][ $taskID ] . '</td>';
    }
    echo '  <td>' . array_sum( $arr )  . '</td>';
    echo '</tr>';
}
?>

Note that the array_sum( $arr ) automatically calculates the sum of all points for you. Also note that you only have to do 3 SQL queries, but you still require 2 loops to display the data, due to the dynamic structure of your table.

edit: You could reduce the amount of SQL queries to one.

SELECT
    teamName AS Team,
    taskName AS Task,
    point
FROM
    points p
    LEFT JOIN team t ON (t.ID = p.teamID)
    LEFT JOIN task ta ON (ta.ID = p.taskID)

But, that means that the size of the array that is storing the information, increases. This is because it no longer stores the ID of the team, but the teamname.

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

5 Comments

Thanks for your thoughts :) I will try to build the system storing the data in arrays and hope that speeds up the process.
Hi Jeffrey Again, thank you very much for your answer. Unfortunately this didn't help on the load time. The amount of data and queries seems to be the same. Do you have maybe have some second thoughts on how I could optimize the process?
The amount of data wont reduce unless you show less info e.g. team 1-10, page 2 11-20, etc. A normal webserver should have no problem at all with creating the above table, even if it is 200 teams with 50 tasks. What is the pageload time? how busy is the server? Havy you tried to optimise the table OPTIMIZE TABLE tablename? Do you have an example?
Hi @Jeffrey Thank you for your answer. The pageload is about 30 seconds after I tried optimising the table. I just installed some cache, which reduced the load time with about 20 seconds. Amazing, but still frustrating. How can I check how busy the server is?
All above 2-5 seconds is way to slow! Can you run the query in phpmyadmin, what time does it take to run it? Secondly, have a look at php.net/manual/en/function.microtime.php (first example) and call this function on many places in your code, to see what is slow e.g. echo __FILE__ .' on line: ' __LINE__ . ' time: ' . microtime_float. Lastly you can look at a PHP profiler like Xdebug, xdebug.org/docs/profiler. Have a look at xdebug_memory_usage(), xdebug_peak_memory_usage(), xdebug_time_index(), xdebug_time_index()

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.