Google Ads

Sorting Data in PHP/MySQL PHP Script


Sorting Data in PHP/MySQL PHP Script

Introduction:
This tutorial will be running off a tutorial found here (https://www.sourcecodester.com/php/7510/retrieving-data-mysqlphp.html) and will be showing you how to sort retrieved data. An example of how this could be used is in the leaderboards of a game.

Table Modifications:
First I need to modify my table from the previous ‘Retrieving Data’ tutorial. I am going to add a second column, after my first initial ‘id’ column. This new column will be called ‘score’, have an int data type, and a length of 5.

You may want to insert more rows of information to test this tutorial out.

Required Source:
So, from the ‘Retrieving Data’ tutorial, we have this code to retrieve the data and store it…

 

 
 
  1. <?php
  2.       $con = mysqli_connect('localhost', 'root', '', 'fln'); //server, username, password, database name
  3.       $q = mysqli_query($con, "SELECT * FROM `test`"); //Gets all from table 'test'
  4.       while ($row = mysqli_fetch_array($q)) {
  5.             //$row = next row of results/all rows from table 'test' within database 'fln'
  6.             echo $row['id']; //Echo current $row's column value of 'id'.
  7.       }
  8. ?>

 

 

Arrays:
To sort our data, we first need to put our data in to one array. So from the code above, we know that the while loop iterates through all the returned rows of data. We can use this to push the current row of data in to our array. First we create the array, named ‘scores’…

 

 
 
  1. $scores = array();

 

 

(Placed above our while loop).

Next, within the while loop, we push each value of ‘score’ in each iteration of the rows returned in our earlier query in to our newly created array…

 

 
 
  1. array_push($scores, $row['score']);

 

 

We now have an array of score data retrieved from our database and table!

‘Sort’:
The sort function built in to PHP arrays will allow us to easily sort the data in to an ascending or descending order (asort/arsort). We are going to list our leaderboard results in descending order so we will use the ‘arsort’ function to do this.

This is very simple. We simply call the ‘arsort’ function and pass it the array to sort the values in order from high to low (descending)…

 

 
 
  1. arsort($scores);
  2. $ind = 0;

 

 

We also create a simply integer variable named ‘ind’ to store our index as we are going to be using a foreach loop next instead of a for loop like before.

Finally we output the data using a foreach loop to loop through each data value within our now sorted ‘scores’ array…

 

 
 
  1. foreach($scores as $key => $value) {
  2.       echo 'After sorting. ID: ' . $ind . '. Score: ' . "$value <br />";
  3.       $ind += 1;
  4. }

 

 

For more information on PHP array functions, you may refer

 

 

 

Have any Question or Comment?

Leave a Reply

Your email address will not be published. Required fields are marked *

"