Creating scoreboard with mysql data in PHP

I want to create a scoreboard for a quiz website I am creating.

Here are my 3 db tables:
relationshiproundscoreteam
I have a query like this which give me all the data I need:
SELECT T.teamId, T.teamName, R.roundId, R.roundName, S.score, S.joker
                FROM round R
                CROSS JOIN team T
                LEFT JOIN score S ON S.teamId = T.teamId
                AND S.roundId = R.roundId
                ORDER BY t.teamId, r.roundId

Open in new window

Result:
query
I then need to loop over the data to create an HTML table.

I have a start here, but im not sure its the correct way of doing this and I am worried that the data may get put in the wrong places:
PHP Class
class ScoreBoardHandler {
    function get() {
        global $con;

        try {
            $query = $con->prepare('SELECT T.teamId, T.teamName, R.roundId, R.roundName, S.score, S.joker
                FROM round R
                CROSS JOIN team T
                LEFT JOIN score S ON S.teamId = T.teamId
                AND S.roundId = R.roundId
                ORDER BY t.teamId, r.roundId;
            ');
            
            $query->execute();
            $results=$query->fetchAll(PDO::FETCH_ASSOC);            
            return json_encode($results);
        } catch (PDOException $e) {
            echo (json_encode(array('success'=>false,'message'=>$e->getMessage())));
        }
    }
}

Open in new window


PHP Table Page
<?php
require_once ('includes/header.php');
require_once ('api/includes/database.php');
require_once('api/ScoreHandler.php');

$ScoreBoardObject = new ScoreBoardHandler();
$data = ($ScoreBoardObject->get());
$data = json_decode($data);

?>


    <h2 class="sub-header">Scoreboard</h2>
    	<table class="table table-striped">
	      	<thead>
				<tr>
					<td>Name</td>

					<?php
					$currentRoundId = "";
					foreach($data as $obj){
						if ($obj->roundId > $currentRoundId) {
							$currentRoundId = $obj->roundId;
							echo "<td>" . $obj->roundName . "</td>";
						}
					}


					?>
					<td>Total</td>
				</tr>
			</thead>

			<tbody>
				<?php
				$currentTeamId = "";
				foreach($data as $obj){
					if ($obj->teamId != $currentTeamId) {
						$currentTeamId = $obj->teamId;
						echo "<tr>";
						echo "<td>" . $obj->teamName . "</td>";
					}
					echo "<td>" . $obj->score . "</td>";

					$totalScore += $obj->score;
					if (next teamId != $currentTeamId){
						echo "<td>" . $totalScore . "</td>";
						echo "</tr>";
					}

				}


				?>
			</tbody>

		</table>
	</div>

Open in new window



Could someone offer some advice / solution to this?

Thanks

Steve
Steve TinsleyAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Julian HansenCommented:
Without looking at your code - one thing that does stand out is
class ScoreBoardHandler {
    function get() {
        global $con; <=== OUCH

Open in new window

Generally global is not a great idea but you don't want to be using it in your classes - do a search on dependency injection to see what the current thinking is regarding providing dependencies to objects.

The next thing I noticed is your get() json_encodes the data before returning it and in your output function you are decoding it again - why not simply return the data without going through encode / decode via JSON?

Onto your actual question - to be honest there is quite a bit to digest in your post - it is good you have provided us all the info - but if you could give a quick summary of what it is you are wanting. The implication is to read the code and figure out what to do next.

I understand you want to output a table but I gather it is not a simple table dump of the data as it comes out - your code has some logic in there that is governing the output and potentially aggregating data - maybe give us a synopsis - and preferably a mockup - of what you are trying to achieve.
1
Steve TinsleyAuthor Commented:
Thanks for looking,
I know my coding isnt the best... The system is being used on a large charity quiz. The table in question is going to show the results  on a big screen to everyone.

This is an example of how I want the output to loop.
example image from google(image from google)

It will need to show
TEAM NAME
SCORES FOR EACH ROUND (sport / music / tv etc..)
If the round hasnt been played there still needs to be a colum for it but it will be empty.
TOTAL added up of round scores.
0
Julian HansenCommented:
The trick is to do as much in the database as you can - fail over to script only when the database can't give you what you want - however SQL is quite powerful and oven underestimated in terms of what it can produce.
Lets work off this query
SELECT 
  T.teamId, 
  T.teamName, 
  SUM(IF(R.roundID=1, score,0)) AS 'round1',
  SUM(IF(R.roundID=2, score,0)) AS 'round2',
  SUM(IF(R.roundId=3, score,0)) AS 'round3',
  SUM(score) AS `total`
FROM `round` R
  CROSS JOIN `team` T
  LEFT JOIN `score` S ON S.teamId = T.teamId
  AND S.roundId = R.roundId
  GROUP BY T.`teamid`
  ORDER BY t.teamId, r.roundId;

Open in new window

This will produce the following results
teamId  teamName    round1    round2    round3    total
1       Team1       1.00      4.00      0.00       5.00
2       Team2       3.00      3.00      0.00       6.00
3       Team3       5.00      2.00      0.00       7.00
4       Team4       4.00      4.00      0.00       8.00
5       Team5       5.00      7.00      0.00      12.00
6       Team6       3.00      3.00      0.00       6.00
7       Team7       4.00      0.00      0.00       4.00
8       Team8       2.00      4.00      0.00       6.00

Open in new window


Now the problem is straight forward.

You can use the first row to work out the headings (modify the query 'AS' bits to make the headings what you wish).
And then just loop through the array and output the values.

You seem to be up to speed on coding the table so will leave the code to you - but if you get stuck feel free to post back and with where you are up to and we can take it from there.
1
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Steve TinsleyAuthor Commented:
THAT'S BRILLIANT
I thought you could do it using sql but I had no idea how.

I think the last 2 things I want it to do are
The rounds are created dynamically in the rounds table, so there could be 2 or there could be 20. Is there any way of pulling this data in the same query?
teams have the ability to use the a JOKER on some rounds. Any thoughts how I would include this data?? perhaps have a boolean for roundxjoker
0
Julian HansenCommented:
Unfortunately my SQL skills are not that sharp that I can do a dynamic query - but you could build one up in code by doing an initial query to find out how many rounds there are and then dynamically create the query to pull the actual results - that's the way I would do it.

select * from round;
$query = 'SELECT T.temid, T.teamName, ';
foreach (round as r) {
   $query .= "SUM(IF(R.roundID={$r['roundID']}, score,0)) AS '{$r['roundName']}',";
}
$query .= "SUM(score) AS `total`
FROM `round` R
	CROSS JOIN `team` T
	LEFT JOIN `score` S ON S.teamId = T.teamId
	AND S.roundId = R.roundId
	GROUP BY T.`teamid`
	ORDER BY t.teamId, r.roundId;";

Open in new window

This can now be used to get the rest of the results

Re Joker - how would this need to come out in the results?
1

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Steve TinsleyAuthor Commented:
The joker will double the score of the round it was used on (which the total needs to take into account) and i will apply a class to that score so it shows up in red.


So far:
output
class ScoreBoardHandler {
    function get() {

        global $con;

        try {


            $rounds = $con->prepare('SELECT * FROM round;');            
            $rounds->execute();

            $query = 'SELECT T.teamName AS `Team Name`, ';
            foreach ($rounds as $r) {
               $query .= "SUM(IF(R.roundId={$r['roundId']}, score,0)) AS '{$r['roundName']}',";
            }
            $query .= "SUM(score) AS Total
            FROM round R
                CROSS JOIN team T
                LEFT JOIN score S ON S.teamId = T.teamId
                AND S.roundId = R.roundId
                GROUP BY T.teamid
                ORDER BY t.teamId, r.roundId;";

            $query = $con->prepare($query);            
            $query->execute();
            $results=$query->fetchAll(PDO::FETCH_ASSOC);            
            
            return json_encode($results);

        } catch (PDOException $e) {
            //header("HTTP/1.0 500 Internal Server Error");
            echo (json_encode(array('success'=>false,'message'=>$e->getMessage())));
        }

    }
}

Open in new window


$ScoreBoardObject = new ScoreBoardHandler();
$data = ($ScoreBoardObject->get());
$data = json_decode($data);



	// foreach($data as $obj){
	// 	echo "-----GROUP-----<br />";
	// 	foreach( $obj as $key => $value ) {
	// 		echo "${key} =>  ${value} <br />";
	// 	}
	// }
?>

	<div id="table-container">
    	<table id="maintable" class="score-board">
	      	<thead>
				<?php
				foreach($data as $obj){
					echo "<tr>";
					foreach( $obj as $key => $value ) {
						echo "<th class=\"${key}\"> ${key} </th>";
					}
					break;
				}

				?>
			</thead>

			<tbody>
				<?php
				foreach($data as $obj){
					echo "<tr>";
					foreach( $obj as $key => $value ) {
						echo "<td class=\"${key}\"> ${value} </td>";
					}
				}


				?>
			</tbody>

		</table>

		<div id="bottom_anchor"></div>
	</div>

Open in new window

0
Julian HansenCommented:
You could try this query
SELECT 
  T.teamId, 
  T.teamName, 
  MAX(S.joker) AS Joker,
  SUM(IF(R.roundID=1, score,0)) AS 'round1',
  SUM(IF(R.roundID=2, score,0)) AS 'round2',
  SUM(IF(R.roundId=3, score,0)) AS 'round3',
  SUM(IF(S.joker=1,score*2, score)) AS `total`
FROM `round` R
  CROSS JOIN `team` T
  LEFT JOIN `score` S ON S.teamId = T.teamId
  AND S.roundId = R.roundId
  GROUP BY T.`teamid`
  ORDER BY t.teamId, r.roundId;

Open in new window

Basically we take the MAX(S.joker) because we are aggregating the score records - so we need to use max to find the highest joker value for a particular team across their scores
Then modify the total to sum using an if joker then add twice the score otherwise just add the score.

You should have a Joker column with 1 or 0 for you to make the decision on regarding the class and the score will now reflect the correct Joker adjustment.
1
Steve TinsleyAuthor Commented:
SOOOO close....
Ideally the scoreboard will show which round the team used their joker on (buy highlighting the round score).

Is it reasonable to have for example:
joker_sport, joker_news, etc after each round in the table. I can then use php to look for joker_ and apply the class to the particular round? We would also need to double the SCORE in the round as well as pass to TOTAL
0
Ray PaseurCommented:
Regarding Globals and Dependency Injection:
http://www.experts-exchange.com/articles/19999/PHP-Design-Avoiding-Globals-with-Dependency-Injection.html

On a design note, you don't have to do all of this in one query.  The amount of data you have is relatively small, and if you ran several queries (in order to simplify your logic) the page would still load fast.
0
Julian HansenCommented:
On a design note, you don't have to do all of this in one query.  The amount of data you have is relatively small, and if you ran several queries (in order to simplify your logic) the page would still load fast.
If you read the thread that is where we started. The SQL route is to simplify the code. This is not about speed but about making the DB do the work.

We would also need to double the SCORE in the round as well as pass to TOTAL
The last query is already doing that.

For the Joker you only need to know what round the joker was in - can this be a field called say Joker_Round that is set to the name of the round with the joker or null if none used?
0
Steve TinsleyAuthor Commented:
To complicate are things they sometimes have the ability to use a joker on multiple rounds. So I would need to display which rounds it was used on.

It would be good to show the doubled score in the round column too.

I agree, speed isn't an issue, especially as it will be running on a local server. But do you think it's getting overly complex for 1 query?
0
Julian HansenCommented:
Ok, how about this?
SELECT 
	T.teamId, 
	T.teamName, 
	MAX(S.joker),
	SUM(IF(R.roundID=1, IF(s.joker=1,score*2,score),0)) AS 'round1',
	MAX(IF(R.roundID=1, s.joker, 0)) AS round1_joker,
	SUM(IF(R.roundID=2, IF(s.joker=1,score*2,score),0)) AS 'round2',
	MAX(IF(R.roundID=2, s.joker, 0)) AS round2_joker,
	SUM(IF(R.roundId=3, IF(s.joker=1,score*2,score),0)) AS 'round3',
	MAX(IF(R.roundID=3, s.joker, 0)) AS round3_joker,
	SUM(IF(S.joker=1,score*2, score)) AS `total`
FROM `round` R
	CROSS JOIN `team` T
	LEFT JOIN `score` S ON S.teamId = T.teamId
	AND S.roundId = R.roundId
	GROUP BY T.`teamid`
	ORDER BY t.teamId, r.roundId;

Open in new window

1
Steve TinsleyAuthor Commented:
This is perfect!

The last thing the table needs to show is the position of the team...
I have given it a go but am not getting the results i was expecting:

SELECT
	@curRank := @curRank + 1 AS `Position`, 
	T.teamId, 
	T.teamName, 

	SUM(IF(R.roundID=1, IF(s.joker=1,score*2,score),0)) AS 'round1',
	MAX(IF(R.roundID=1, s.joker, 0)) AS round1_joker,
    
	SUM(IF(R.roundID=2, IF(s.joker=1,score*2,score),0)) AS 'round2',
	MAX(IF(R.roundID=2, s.joker, 0)) AS round2_joker,
    
	SUM(IF(R.roundId=3, IF(s.joker=1,score*2,score),0)) AS 'round3',
	MAX(IF(R.roundID=3, s.joker, 0)) AS round3_joker,
    
	SUM(IF(S.joker=1,score*2, score)) AS `total`
    
FROM (SELECT @curRank := 0) X, `round` R
	CROSS JOIN `team` T
	LEFT JOIN `score` S ON S.teamId = T.teamId
	AND S.roundId = R.roundId
	GROUP BY T.`teamid`
	ORDER BY Total DESC, t.teamId, r.roundId

Open in new window


query output
0
Julian HansenCommented:
Several ways to do this - you can simply change the ORDER BY to ORDER BY total like this
SELECT 
		T.teamId, 
		T.teamName, 
		MAX(S.joker),
		SUM(IF(R.roundID=1, IF(s.joker=1,score*2,score),0)) AS 'round1',
		MAX(IF(R.roundID=1, s.joker, 0)) AS round1_joker,
		SUM(IF(R.roundID=2, IF(s.joker=1,score*2,score),0)) AS 'round2',
		MAX(IF(R.roundID=2, s.joker, 0)) AS round2_joker,
		SUM(IF(R.roundId=3, IF(s.joker=1,score*2,score),0)) AS 'round3',
		MAX(IF(R.roundID=3, s.joker, 0)) AS round3_joker,
		SUM(IF(S.joker=1,score*2, score)) AS `total`
	FROM `round` R
		CROSS JOIN `team` T
		LEFT JOIN `score` S ON S.teamId = T.teamId
		AND S.roundId = R.roundId
		GROUP BY T.`teamid`
		ORDER BY total DESC

Open in new window

And then use PHP to fill in the position?
If you want it in the DB results then this will also work you can use the same query but then wrap it in another SELECT to generate the position.
SELECT 
	@curRank := @curRank + 1 AS `Position`, 
	A.* 
FROM (SELECT @curRank := 0) X,           
	(SELECT 
		T.teamId, 
		T.teamName, 
		MAX(S.joker),
		SUM(IF(R.roundID=1, IF(s.joker=1,score*2,score),0)) AS 'round1',
		MAX(IF(R.roundID=1, s.joker, 0)) AS round1_joker,
		SUM(IF(R.roundID=2, IF(s.joker=1,score*2,score),0)) AS 'round2',
		MAX(IF(R.roundID=2, s.joker, 0)) AS round2_joker,
		SUM(IF(R.roundId=3, IF(s.joker=1,score*2,score),0)) AS 'round3',
		MAX(IF(R.roundID=3, s.joker, 0)) AS round3_joker,
		SUM(IF(S.joker=1,score*2, score)) AS `total`
	FROM `round` R
		CROSS JOIN `team` T
		LEFT JOIN `score` S ON S.teamId = T.teamId
		AND S.roundId = R.roundId
		GROUP BY T.`teamid`
		ORDER BY total DESC) A;

Open in new window

0
Steve TinsleyAuthor Commented:
Any thought on if 2 or more teams have the same score??
I found this:
CASE 
WHEN @prevRank = score THEN @curRank 
WHEN @prevRank := score THEN @curRank := @curRank + 1
END AS rank

Open in new window

but wasnt sure where to put it?
:)
0
Julian HansenCommented:
Not sure what the issue is?
0
Steve TinsleyAuthor Commented:
If 2 different teams get the highest score of eg 50 then they should both have position 1.
0
Julian HansenCommented:
I would check for this in script based on the score.

$lastscore = 0;
while( ...) {
  if ($row['total'] != $lastscore) {
     $position++;
  }
  if ($row['total'] > $lastscore) {
    $lastscore = $row['total'];
  }
  ...

Open in new window

0
Steve TinsleyAuthor Commented:
Fantastic help!! Learnt some new MySQL skills too!!!!!
0
Julian HansenCommented:
You are most welcome.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
PHP

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.