PHP leaderboard from mysql database

Beginners - Learning PHP
#1

Howdy I’m looking for some help / start with this as I cant seem to get it going.

I want to build a leaderboard that pulls results from a mysql database. There are 4 columns FIRST, SECOND, THIRD and FOURTH and each column contains the names of the people who won that place. Also there will be duplicate names across each of the columns.

I plan to assign points to each column too, so column FIRST =100pts and column SECOND = 75pts etc.

I run the query to get all the data.

[php]$query = mysqli_query($con,“SELECT FIRST, SECOND, THIRD, FOURTH FROM results”);[/php]

So then I’m not really sure the best way to tackle it.

I am trying this

[php]while($row = mysqli_fetch_array($query))
{
$firstarr[] = $row[‘FIRST’];
$secondarr[] = $row[‘SECOND’];
$thirdarr[] = $row[‘THIRD’];
$fourtharr[] = $row[‘FOURTH’];
}[/php]

So my thinking here is I have I have all names including duplicates saved to arrays so I can count them to do the multiplication sum for the points later.

[php]while($row = mysqli_fetch_array($query))
{
$firstarr[] = $row[‘FIRST’];
$secondarr[] = $row[‘SECOND’];
$thirdarr[] = $row[‘THIRD’];
$fourtharr[] = $row[‘FOURTH’];
}

$result1 = array_unique($firstarr);
$result2 = array_unique($secondarr);
$result3 = array_unique($thirdarr);
$result4 = array_unique($fourtharr);

$arrcount1 = array_count_values($firstarr);
$arrcount2 = array_count_values($secondarr);
$arrcount3 = array_count_values($thirdarr);
$arrcount4 = array_count_values($fourtharr);[/php]

So here is where I’m stumped…I have all the names and how many times any names are unique to each column across the table. But I have 8 arrays 4 with names and 4 with the counts and not sure where to proceed…

#2

Might I suggest a design more like this:

[php]
create table users
(
user_id int not null auto increment primary key,

other needed user data

)
engine=innoDB;

create table results
(
result_id int not null auto increment primary key,
user_id int not null,
ranking int,
game_id int,
score int,
CONSTRAINT FOREIGN KEY (user_id) REFERENCES user (user_id) ON DELETE CASCADE ON UPDATE CASCADE
)
engine=innoDB;
[/php]

Then, you add the where clause to determine which game you are trying to get the leaders from.

#3

I think you can do what you want in the query and you won’t have a big mess of code.

Instead of this.

[php]SELECT FIRST, SECOND, THIRD, FOURTH FROM results[/php]

I think you can do this and get exactly what you are looking for, I didn’t test it. so there might be a mistake someplace.

[php]Select name, sum(points) as points from
(Select FIRST as name, 100 as Points FROM RESULTS
UNION ALL
Select SECOND as name, 75 as Points FROM RESULTS
UNION ALL
Select THIRD as name, 50 as Points FROM RESULTS
UNION ALL
Select Fourth as name, 25 as Points FROM RESULTS) Totals
group by name
[/php]

#4

If you have duplicate anything in the database, you need to redesign your database.

#5

Based on how I’m reading his question, he has a table where he stores the winners.

One week a person will take first, the next they can take second, etc

So the data isn’t duplicated, it’s just storing the same user that can win different events. So the users names will appear more then once.

The approach the OP was taking to retrieve and calculate the points total over all the events was overly complicated and can be simplified with a single query.

#6

You will likely have duplicate data at some point regardless. You sell stuff, unless it is one off pieces, there will be duplicates for at least 1 column.

Topcoder, your method works well. Mine is just scalable. If more points are given for a specialty games, it handles it. If you want to see a user’s average ranking, it handles it.

#7

RE: My Comment, let me rephrase, The OP’s DB design is bad. [member=72272]astonecipher[/member] had the right idea in his post. While [member=69011]Topcoder[/member] 's solution will work, the DB design is still wrong.

#8

This is right up my alley for I have a degree in Interactive media and game design. :wink:

I’m in the process of developing a online trivia game that gives a top ten scores every day, here’s the query I did for it.
[php]$this->query = “SELECT user_name, score, DATE_FORMAT(date_played,’%d %b %y’) as date_played FROM highscores WHERE date_played <= DATE_ADD(DATE(now()), INTERVAL 1 DAY) ORDER BY score DESC LIMIT 10”; [/php]

Ignore the $this->query and substitute it with $query. The main point is let the MySQL Query do the work for you, you could substitute date_played <= DATE_ADD(code…) with user’s id or what have you This was done using PDO:
[php] public function top_ten_scores() {
$this->query = “SELECT user_name, score, DATE_FORMAT(date_played,’%d %b %y’) as date_played FROM highscores WHERE date_played <= DATE_ADD(DATE(now()), INTERVAL 1 DAY) ORDER BY score DESC LIMIT 10”;
try {
$this->stmt = $this->pdo->prepare($this->query);
$this->stmt->execute();
$this->stmt->setFetchMode(PDO::FETCH_OBJ);
return $this->stmt;
} catch (Exception $ex) {
echo $ex->getMessage();
}
}[/php]

What I’m basically trying to say is setup some kind of scoring system and then just let the Query sort out the scores -> You can even grab the winner(s) when you output the table.

Though I’m sure it can be done in mysqli just as easily…hope that helps. John

#9

Thanks for all your replies guys I feel I have lot more options to choose from :slight_smile: :slight_smile:

I didn’t realise mysql queries could be so workable / useable.

Sponsor our Newsletter | Privacy Policy | Terms of Service