Page 1 of 1

Sorting output data in table BUT with tie-breaking criteria

Posted: Wed Jun 25, 2008 8:12 am
by lepage71
Hey everyone, I am a complete PHP newbie and have been trying to figure out how to do something that I believe is very possible to code (if you know what you're doing). :(

I am pulling "online racing league" stats from a MySQL DB and generating an HTML table for it. Here is an example I threw together: http://nhrl.net/demo/mytestcode.php

Here is the PHP portion for that page:

Code: Select all

<?php
 
include '../test/authentication/connection.php'; //login info
include '../test/authentication/open.php'; //open db code
 
$result = mysql_query("SELECT Driver, Races, Wins, Points FROM Demo ORDER BY Points DESC");
 
echo "<table width='300' cellspacing='0' cellpadding='0' class='basictable'>
<tr>
<th width='150'>Driver</th>
<th width='60'>Races</th>
<th width='60'>Wins</th>
<th width='75'>Wins</th>
</tr>";
 
while($row = mysql_fetch_array($result))
    {
    echo "<tr>";
    echo "<td>" . $row['Driver'] . "</td>";
    echo "<td>" . $row['Races'] . "</td>";
    echo "<td>" . $row['Wins'] . "</td>";
    echo "<td>" . $row['Points'] . "</td>";
    echo "</tr>";
    }
    
echo "</table>";
 
include '../test/authentication/close.php'; //close db code
 
?>
What I would like to do is sort each column of stats by clicking on the header / label row. If you keep clicking on the same column header, the descending / ascending action would go back and forth.

Now, I have found many codes that enable you to "sort by column header"," including in this forum, but I can't find an example that shows how to set up multiple levels of tie-breaking criteria. I could achieve this by making a million different .php files and setting their initial ORDER differently, but that's far, far from ideal.

I made another example page showing exactly what my table would need to do: http://nhrl.net/demo/functionality4.php

If you notice, when there's a tie in a stat, there are particular criteria that determine who gains the advantage in a tie break. For example, when you sort Wins in descending order, there are 2 tie breaks: 1) least number of races and 2) highest point total. When you sort Wins in ascending order, the "races" and "points" criteria then go in the other direction.

Obviously, there are many different combinations of tie breaks for other columns of stats that I'm not showing in these examples. I was wondering if anyone could help me get started with an example code showing how to achieve this type of sorting with (let's suppose) 2 levels of tie-breaking criteria.

Thanks for any guidance you can provide!

Re: Sorting output data in table BUT with tie-breaking criteria

Posted: Wed Jun 25, 2008 9:56 am
by Kieran Huggins
Your DB schema seems to be the stumbling block here.

You need to do something called "normalization" to be able to use the data properly:

Code: Select all

A Driver has many races.
A Race has many drivers
Each driver-race combination has a score.
 
so your DB should look like this:
 
drivers
----------------------------------
id
name
 
 
races
----------------------------------
id
name
 
drivers_races (join table)
----------------------------------
id
driver_id
race_id
points
You'll have to query using joins and at least one subselect for the number of wins, but it will make your DB more flexible in the end, and you can pull it out with multiple ORDER BY clauses for the correct order.

Re: Sorting output data in table BUT with tie-breaking criteria

Posted: Wed Jun 25, 2008 11:19 am
by lepage71
I think I see what you're describing here. After I configure the data tables like this, what step would I take?

Re: Sorting output data in table BUT with tie-breaking criteria

Posted: Wed Jun 25, 2008 5:02 pm
by Ctech
You will have to create variables in your mysql query?

Have a link on the header of each column in your table.
example: link "Wins" : http://nhrl.net/demo/functionality4.php?col=Wins

Then use this in the php scipt:
<?PHP
$column_name = $_GET['col']; // to get this variable from your link, will be Wins

and

$result = mysql_query("SELECT Driver, Races, Wins, Points FROM Demo ORDER BY '".$column_name."' DESC");

?>

This should do the trick, I think..

Re: Sorting output data in table BUT with tie-breaking criteria

Posted: Wed Jun 25, 2008 8:09 pm
by lepage71
Would you mind providing an example of what I might create for this? I'm still very fuzzy on what I'm doing...
You will have to create variables in your mysql query?
Here is my current code:

Code: Select all

<?php
 
include '../test/authentication/connection.php';
include '../test/authentication/open.php';
 
// enter more variables here?
  
$column_name = $_GET['col']; 
$result = mysql_query("SELECT Driver, Races, Wins, Points FROM Demo ORDER BY '".$column_name."' DESC");
 
echo "<table width='300' cellspacing='0' cellpadding='0' class='basictable'>
<tr>
<th width='150'><a href='http://nhrl.net/demo/mytestcode2.php?col=Driver'>Driver</a></th>
<th width='60'><a href='http://nhrl.net/demo/mytestcode2.php?col=Races'>Races</a></th>
<th width='60'><a href='http://nhrl.net/demo/mytestcode2.php?col=Wins'>Wins</a></th>
<th width='75'><a href='http://nhrl.net/demo/mytestcode2.php?col=Points'>Points</a></th>
</tr>";
 
while($row = mysql_fetch_array($result))
    {
    echo "<tr>";
    echo "<td>" . $row['Driver'] . "</td>";
    echo "<td>" . $row['Races'] . "</td>";
    echo "<td>" . $row['Wins'] . "</td>";
    echo "<td>" . $row['Points'] . "</td>";
    echo "</tr>";
    }
    
echo "</table>";
 
include '../test/authentication/close.php';
 
?>

Re: Sorting output data in table BUT with tie-breaking criteria

Posted: Wed Jun 25, 2008 11:09 pm
by WebbieDave
lepage71 wrote:there are 2 tie breaks: 1) least number of races and 2) highest point total
Would using multiple columns in the ORDER BY clause provide the functionality you're after? For instance, if the visitor sorts by Wins, you could use:

Code: Select all

SELECT Driver, Races, Wins, Points FROM Demo ORDER BY Wins DESC, Races ASC, Points DESC
Or something like that. Also, always escape outside data before sending it to the database (to avoid SQL injection):

Code: Select all

$column_name = mysql_real_escape_string($_GET['col']);

Re: Sorting output data in table BUT with tie-breaking criteria

Posted: Thu Jun 26, 2008 11:44 am
by lepage71
Using that select code sets the "initial" tie-breaking order for Wins that I would want, but the column headers still don't sort at all, clicking them just makes the page refresh to its original state again.

That is indeed the correct logic for sorting wins descendingly, I just wish I could implement a customized tie-breaking scheme on each column individually (but I first need to figure out how to get ANYTHING to sort). :?

Re: Sorting output data in table BUT with tie-breaking criteria

Posted: Fri Jun 27, 2008 3:00 am
by Stryks
The code you have should work fine, although it should be throwing a warning because $_GET['col'] will be unset on the first view.

Also, though normally a good idea to put quotation marks around values inserted into a query, in this case it means that 'Driver' would be evaluated as a string. You're going to need ...

Code: Select all

$result = mysql_query("SELECT Driver, Races, Wins, Points FROM Demo ORDER BY ".$column_name." DESC");
To help secure it a little, I wouldn't pass the column name in the URL, but a number representing the column. Then just convert the number to the appropriate column string and insert that way. This way, people can't send you text to go into your query, and you also don't give away knowledge of the structure of your table (feild names).

I'd also consider not using the 'col' parameter, but 'asc' or 'desc' instead. So
mytestcode2.php?asc=3
You could just as easily pass a second parameter, but this approach would be a single parameter that would allow a switching ASC / DESC sort on a column. The link would just switch to the opposite direction once a sort was applied, so a re-click would produce the opposite result.

Any help?

Re: Sorting output data in table BUT with tie-breaking criteria

Posted: Fri Jun 27, 2008 6:36 am
by lepage71
Yes, but in a surprisingly different way. Using the order-by link suggestions you've all shared here, I've actually gone in a different direction, instead setting the ORDER BY tie-breaking scenarios as stand-alone pages (within a single PHP document).

This ends up being the end result (new test DB table with different statistics): http://nhrl.net/demo/mytestcode2.php?id=1A

It does everything that I'm looking for: highlighted columns, changing header colors, unlimited ascending-descending toggling back and forth, etc. My method would give PHP experts nightmares but the code is simple and easy to work with. It is not ideal from a pure server-side perspective since the queries are being run over and over again, but I siimply don't care because I'm trading ease-to-work-with for this - and also the fact that I will never be using this on data tables with more than 300 entries. Any extra server-loading issues would be microscopic in scale.

Re: Sorting output data in table BUT with tie-breaking criteria

Posted: Fri Jun 27, 2008 8:42 am
by Stryks
Nothing wrong with pulling the data for each page view.

It's hard to know what you mean with the whole 'stand alone documents' thing, but if it means that you have the table coded four times, the one being shown depending on the parameter passed, well ... that's one way to do it.

We could probably get it much cleaner and simpler, if you want to work through it that is.

Post some code if you're interested in taking it further.

Re: Sorting output data in table BUT with tie-breaking criteria

Posted: Fri Jun 27, 2008 9:14 am
by lepage71
Here is the main page:

Code: Select all

<?php
 
include 'includes/top.php';
 
echo "<div align='center' class='pagetitletop'></div>
<div align='center' class='pagetitle'><strong>Noobie Test Sorting</strong></div>
<div><img src='/images/space.gif' width='1' height='24' /></div>
 
<div align='center'>";
 
include 'authentication/connect.php';
include 'authentication/opendb.php';
 
include 'testA.php';
 
// ID
 
if ($_GET["id"] == "1A") {
 
$match = "ID";
$order = "ORDER BY ID ASC";
 
}
 
elseif ($_GET["id"] == "1B") {
 
$match = "ID";
$order = "ORDER BY ID DESC";
 
}
 
// Driver
 
elseif ($_GET["id"] == "2A") {
 
$match = "Driver";
$order = "ORDER BY Driver ASC";
 
}
 
elseif ($_GET["id"] == "2B") {
 
$match = "Driver";
$order = "ORDER BY Driver DESC";
 
}
 
// Debut
 
elseif ($_GET["id"] == "3A") {
 
$match = "Debut";
$order = "ORDER BY Debut ASC, Driver ASC";
 
}
 
elseif ($_GET["id"] == "3B") {
 
$match = "Debut";
$order = "ORDER BY Debut DESC, Driver ASC";
 
}
 
// Number
 
elseif ($_GET["id"] == "4A") {
 
$match = "Number";
$order = "ORDER BY Number ASC, Driver ASC";
 
}
 
elseif ($_GET["id"] == "4B") {
 
$match = "Number";
$order = "ORDER BY Number DESC, Driver ASC";
 
}
 
else { 
 
$match = "ID";
$order = "ORDER BY ID ASC";
 
}
 
include 'testB.php';
include 'testC.php';
 
include 'authentication/closedb.php';
 
echo "</div>";
 
include 'includes/bottom.php';
 
?>
TestA contains the SELECT and FROM:

Code: Select all

<?php
 
$select = "SELECT ID, Driver, Debut, Number ";
$from = "FROM Drivers ";
 
?>
TestB contains the string to make the query command (remember, I needed to get ORDER BY out of one long string that would also have SELECT AND FROM):

Code: Select all

<?php
 
$stats = $select . $from . $order;
 
$result = mysql_query ($stats);
 
?>
Finally, TestC is tricked up with IFs to make the table behave:

Code: Select all

<?php
 
// column labels
 
$url = $_SERVER["QUERY_STRING"];
 
echo "<table width='auto' cellspacing='0' cellpadding='0' class='basictable'><tr>";
 
echo "<th width='65'"; if ($match == "ID") echo " class='highlight'>"; else echo ">"; echo "<a href='mytestcode2.php?id="; if ($url == "id=1A") 
echo "1B"; elseif ($url == "id=1B") echo "1A"; else echo "1A"; echo "'>ID</a></th>";
 
echo "<th width='150'"; if ($match == "Driver") echo " class='highlight'>"; else echo ">"; echo "<a href='mytestcode2.php?id="; if ($url == "id=2A") echo "2B"; elseif ($url == "id=2B") echo "2A"; else echo "2A"; echo "'>Driver</a></th>";
 
echo "<th width='85'"; if ($match == "Debut") echo " class='highlight'>"; else echo ">"; echo "<a href='mytestcode2.php?id="; if ($url == "id=3A") echo "3B"; elseif ($url == "id=3B") echo "3A"; else echo "3A"; echo "'>Debut</a></th>";
 
echo "<th width='85'"; if ($match == "Number") echo " class='highlight'>"; else echo ">"; echo "<a href='mytestcode2.php?id="; if ($url == "id=4A") echo "4B"; elseif ($url == "id=4B") echo "4A"; else echo "4A"; echo "'>Number</a></th>";
 
echo "</tr>";
 
// column statistics
 
while ($row = mysql_fetch_array ($result))
    {
    echo "<tr>";
    echo "<td"; if ($match == "ID") echo " class='highlight'>"; else echo ">"; echo $row['ID'] . "</td>";
    echo "<td"; if ($match == "Driver") echo " class='highlight'>"; else echo ">"; echo $row['Driver'] . "</td>";
    echo "<td"; if ($match == "Debut") echo " class='highlight'>"; else echo ">"; echo $row['Debut'] . "</td>";
    echo "<td"; if ($match == "Number") echo " class='highlight'>"; else echo ">"; echo $row['Number'] . "</td>";
    echo "</tr>";
    }
    
echo "</table>";
 
?>
All in all, it works pretty well and I don't need to repeat any complex pieces of code over and over again thanks to the PHP includes. I just don't know enough to do this any other way right now...I'm pretty much winging PHP at the moment.

If someone can take this further and make it more efficient, I'd be very interested in seeing what is possible.

Re: Sorting output data in table BUT with tie-breaking criteria

Posted: Sun Jun 29, 2008 9:42 pm
by Stryks
Sorry ... Had the weekend away. Much needed.

Anyhow, I'm wondering why you are using the includes for the code segments? Generally, I would try to keep the code that sets the data up for viewing with the code that does the displaying.

That being said, I think TestA and TestC belong together. Likewise TestB interacts with and expects values from the main page, so they would be merged. Then once you were down to two files, you'd notice that TestA expects values from the main file as well. So you'd merge and it would all be on one page again. There are exceptions for this, but for a basic page like this the single page approach will win out for me every time.

Now for creating the basic SQL, well ...firstly, I'd set up some basic conversion values, from numbers to table fields. And while we're doing that, why not set up some display options, seeing as how we're keeping this all together.

Code: Select all

    // Set the column parameters (displayed columns) 
    $column_list = array(0=>"ID", 1=>"Driver", 2=>"Races", 3=>"Wins", 4=>"Points");
    $column_size = array(0=>100, 1=>300, 2=>150, 3=>150, 4=>150);
    $column_default = 4;
So, we have the columns to be shown, the column widths for each, and a value holding the column that will be sorted by default. Next we need to capture the $_GET value and determine the action to be taken.

Code: Select all

    // Search for corresponding column in $column list
    if((isset($_GET['col'])) && (array_key_exists($_GET['col'], $column_list))) { 
        // Found the column, set some valaues to use lower
        $sort_id = $_GET['col'];
        $sort_col = $column_list[$sort_id];     
    } else {
        // No values were found so set default view to sort on $column_default
        $sort_id = $column_default;
        $sort_col= $column_list[$sort_id];
    }
    // Set search order to DESC if anything is passed, otherwise assume ASC
    if(isset($_GET['order'])) $sort_type = "DESC"; else $sort_type = "ASC";
 
    // Compile and commit the query
    $result = mysql_query("SELECT ID, Driver, Races, Wins, Points FROM Demo ORDER BY $sort_col $sort_type");
 
You'll notice that for normal variables, you can just add them amongst normal text if you use double quotation marks. This just makes it easier to see what is going to be inserted without using . 'this' . $annoying . 'syntax'

Anyhow, this next bit is probably debatable, but I try to keep HTML code out of PHP code. So I normally just break out of my <?php ?> tags and output HTML as needed, marking up as I go.

So ... for example, to display the table and draw the header row, and taking into account that I now have an array of columns to be shown, I'd use something like

Code: Select all

?>
<table width="<?php echo array_sum($column_size); ?>px" cellpadding="0" cellspacing="0">
   <tr> 
<?php
    foreach($column_list as $col_id=>$column) {
        // Set the base link
        $sort_link = "sort_switch.php";
        // For all columns that arent the default column ...
        if ($col_id != $column_default) {
            // Show descending link if this column is the current sort column and is sorted ascendingly
            if(($col_id == $sort_id) && ($sort_type == "ASC")) $sort_link .= "?col=$col_id&order=desc"; 
            // Or link to ascending (default) view for this column
            else $sort_link .= "?col=$col_id";
        // if default column is in ascending view, show descending link, otherwise show nothing and restore default view (COL 0 ASC)    
        } elseif(($col_id == $sort_id) && ($sort_type == "ASC")) $sort_link .= "?order=desc";
?>
        <th width="<?php echo $column_size[$col_id]; ?>"<?php  if($col_id == $sort_id) echo " class=\"highlight\""; ?>><a href="<?php echo $sort_link; ?>"><?php echo $column; ?></a>
<?php
    }
?>
   </tr>
<?php
 
As you can see, there's a little code in there looking more complex than it really is because of the comments. Basically, it just calculates what link needs to be shown. I've included some logic to remove the col number on the default columns, so sorting in the default way returns you to the base URL.

From there you just show the data ... the following should do it ...

Code: Select all

    while($row = mysql_fetch_array($result))
    {
?>
    <tr>
<?php
 
        foreach($column_list as $col_id=>$column) {
?>
        <td<?php  if($col_id == $sort_id) echo " class=\"highlight\""; ?>><?php echo $row[$column]; ?></td>
<?php
        }
?>
    </tr>
<?php    
    }
?>   
</table>
 
You could also build the SQL using $column_list, but I thought it would be good to leave the option to return data you don't display (such as ID's to use in links etc).

So you just specify the columns to reutn in your SQL, set up the columns in $column list, and set up display widths accordingly, then set the default search column.

You'll want to set $sort_link to the name of your page, but apart from that, it should pretty much draw itself.

Let me know if you need more help implementing this (but it can pretty much just be pasted together from the above).

Cheers

Re: Sorting output data in table BUT with tie-breaking criteria

Posted: Tue Jul 01, 2008 11:27 am
by lepage71
Thanks...that works pretty well.

About the TestA, B, and C...those need to be separate files for a particular reason. The code in each included file will be a little different on other "sortable stats pages" so I'll need to input some variation.

Re: Sorting output data in table BUT with tie-breaking criteria

Posted: Tue Jul 01, 2008 11:10 pm
by Stryks
I'm not saying that your includes approach is wrong, just that it probably adds more server load than replicating those few lines in multiple 'views' would.

I mean, two for your include pages have only two lines. Copying those four lines to a file would add practically nothing to the parse time of that page. On the other hand, the include needs to locate the file, then open it, and then parse the lines.

There is a place for code reuse, but this is not (I believe) one of them. Keep the code bundled as a functional unit. If you do want to compartmentalize your code, then you should aim to have it be independent of any other code. For example, make a class you have connect to the database, accept your query and then return the data for the view.

Just my two cents on that. And while I'm giving away advice, I'd really consider the normalizing advice you got earlier on in the thread. Your method works for this display, but there is so much more you could do with tat data. For example, in this display, you could click on a particular drivers 'wins' and return a list of the races they have won. A click on a race could return all drivers in that race and the placements they achieved. A click on a driver could show all the races they have participated in and their placement in each.

Cheers