Sorting PHP/HTML table by time

justforfun · Post by **justforfun** » Sun Dec 01, 2013 6:34 pm

hey guys i was just wondering if you could help i a trying to create a table in which will be pulled from the DB for all players in the same group/rank and then the best times will be show in a list/table

here is my code so far:

Code: Select all

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" xml:lang="en" lang="en">
<head>
<meta http-equiv="content-type" content="text/html; charset=utf-8" />
<meta name="description" content="" />
<meta name="keywords" content="" />
<meta name="author" content="" />
<link rel="stylesheet" type="text/css" href="style.css" media="screen" />
<title>Motor Racing Online</title>
</head>
    <body>
        <div id="wrapper">
<?php include('includes/header.php');
include('includes/nav.php'); ?>

<div id="content">
<h4>Staff And Facilities</h4>

<?php
include_once 'connect.php';

if(!isset($_SESSION)){
    session_start();
}

if (isset($_SESSION['player']))
{
  $player=$_SESSION['player'];
}
else
{
  echo "Not Logged in <br><br> <A href='login.php'>Login</a>";
  exit;
}
$bypass=0;
$playerinfo ="SELECT * from players where name='$player'";
$playerinfo2=mysql_query($playerinfo) or die (mysql_error());
$playerinfo3=mysql_fetch_array($playerinfo2);
$id=$playerinfo3['id'];
$staffinfo ="SELECT * from testing where level='$playerinfo3[level]'" ;
$staffinfo2=mysql_query($staffinfo) or die ("could not get staff stats");
$staffinfo3=mysql_fetch_array($staffinfo2);
$driversname=$playerinfo3['name'];
$levelinfo = "SELECT * from players where level ='$playerinfo3[level]'";
$levelinfo2=mysql_query($levelinfo) or die (mysql_error());
$levelinfo3=mysql_fetch_array($levelinfo2);
$id1info = "SELECT * from groups where id=1";
$id1info2=mysql_query($id1info) or die (mysql_error());
$id1info3=mysql_fetch_array($id1info2);
$id2info = "SELECT * from groups where id=2";
$id2info2=mysql_query($id2info) or die (mysql_error());
$id2info3=mysql_fetch_array($id2info2);
$testing1info = "SELECT * from testing where pid=$id1info3[pid]";
$testing1info2=mysql_query($testing1info) or die (mysql_error());
$testing1info3=mysql_fetch_array($testing1info2);
$testing2info = "SELECT * from testing where pid=$id2info3[pid]";
$testing2info2=mysql_query($testing2info) or die (mysql_error());
$testing2info3=mysql_fetch_array($testing2info2);


if ($testing1info3['lap1'] < $testing1info3['lap2'] && $testing1info3['lap1'] < $testing1info3['lap3'] && $testing1info3['lap1'] < $testing1info3['lap4'] &&
$testing1info3['lap1'] < $testing1info3['lap5'])
{$bestlapid1 = $testing1info3['lap1'];}
elseif($testing1info3['lap2'] < $testing1info3['lap3'] && $testing1info3['lap2'] < $testing1info3['lap4'] && $testing1info3['lap2'] < $testing1info3['lap5'])
{$bestlapid1 = $testing1info3['lap2'];}
elseif($testing1info3['lap3'] < $testing1info3['lap4'] && $testing1info3['lap3'] < $testing1info3['lap5']){$bestlapid1 = $testing1info3['lap3'];}
elseif($testing1info3['lap4'] < $testing1info3['lap5']) {$bestlapid1 = $testing1info3['lap4'];} else {$bestlapid1 = $testing1info3['lap5'];}

if ($testing2info3['lap1'] < $testing2info3['lap2'] && $testing2info3['lap1'] < $testin21info3['lap3'] && $testing2info3['lap1'] < $testing2info3['lap4'] &&
$testing2info3['lap1'] < $testing2info3['lap5'])
{$bestlapid2 = $testing2info3['lap1'];}
elseif($testing2info3['lap2'] < $testing2info3['lap3'] && $testing2info3['lap2'] < $testing2info3['lap4'] && $testing2info3['lap2'] < $testing2info3['lap5'])
{$bestlapid2 = $testing2info3['lap2'];}
elseif($testing2info3['lap3'] < $testing2info3['lap4'] && $testing2info3['lap3'] < $testing2info3['lap5']){$bestlapid2 = $testing2info3['lap3'];}
elseif($testing2info3['lap4'] < $testing2info3['lap5']) {$bestlapid2 = $testing2info3['lap4'];} else {$bestlapid2 = $testing2info3['lap5'];}


$pid=$staffinfo3['pid'];
$laptime1 = $staffinfo3['lap1'];
$laptime2 = $staffinfo3['lap2'];
$laptime3 = $staffinfo3['lap3'];
$laptime4 = $staffinfo3['lap4'];
$laptime5 = $staffinfo3['lap5'];
$a = $laptime1;
date('H:i:s.', substr($a, 0, -3)) . substr($a, -3);

$b = $laptime2;
date('H:i:s.', substr($b, 0, -3)) . substr($b, -3);

$c = $laptime3;
date('H:i:s.', substr($c, 0, -3)) . substr($c, -3);

$d = $laptime4;
date('H:i:s.', substr($d, 0, -3)) . substr($d, -3);

$e = $laptime5;
date('H:i:s.', substr($e, 0, -3)) . substr($e, -3);

if($staffinfo3['tyre'] === 9){
   $choice = 'Xtra Soft';
  }else if($staffinfo3['tyre']=== 10){
  $choice = 'Soft';
  }else if ($staffinfo3['tyre']===11){
  $choice = 'Medium';
  }else if ($staffinfo3['tyre']===12){
  $choice = 'Hards';
  }




echo "<h2>Your Completed Testing laps</h2>";
echo"<small>";
     print "<center>";
     print "<table border='0' width='70%' cellspacing='5'>";
     print "<tr><td width='25%' valign='top'>";
     print "</td>";
     print "<td valign='top' width='75%'>";
     print "<table border='0' bgcolor='#f8f8f8'>";
     print "<tr><td>Position<font color='f8f8f8'>______________________</font></td><td>Managers Name<font color='f8f8f8'>______</td>
     <td>Best Lap Time <font color='f8f8f8'>______</td><td>Gap<font color='f8f8f8'>______</td></tr>";
     print "<tr><td>1<font color='f8f8f8'>_______</font></td><td>";echo "$id1info3[name]"; print "<font color='f8f8f8'>______</td>
     <td>";echo date('i:s.', substr($bestlapid1, 0, -3)) . substr($bestlapid1, -3);echo "<font color='f8f8f8'>______</td></tr>";
     print "<tr><td>"; if ($testing2info3['hastested'] == 1){echo "2";}
print "<font color='f8f8f8'>_______</font></td><td>";if ($testing2info3['hastested'] == 1) {echo "$id2info3[name]";}else{}; print "<font color='f8f8f8'>______</td>
     <td>";if ($testing2info3['hastested'] == 1) {echo date('i:s.', substr($bestlapid2, 0, -3)) . substr($bestlapid2, -3);}
	 echo "<font color='f8f8f8'>______</td><td></tr>";


     print "</table>";
     print "</td></tr></table>";
     print "</center>";


?>

</div> <!-- end #content -->
<?php include('includes/sidebar.php'); ?>

<?php include('includes/footer.php'); ?>

        </div> <!-- End #wrapper -->
    </body>
</html>

it is all over the place at the moment but it does work only thing is that it sorts the table by the players id as you can see there is only 2 players in the same level so this is the shorter code version any help would be appreciated

Post by **Jackolantern** » Sun Dec 01, 2013 7:18 pm

You know a SQL query can do that for you, and put the results in order by any column you like? See the ORDER BY clause here. That way they will come out of the results array in order, so putting them in order in the table will simply be a matter of iterating through the results array and formatting the results into table rows

justforfun · Post by **justforfun** » Sun Dec 01, 2013 7:22 pm

Jackolantern wrote:You know a SQL query can do that for you, and put the results in order by any column you like? See the ORDER BY clause here. That way they will come out of the results array in order, so putting them in order in the table will simply be a matter of iterating through the results array and formatting the results into table rows

sorry but i am completely stumped on this

the link you provided did confuse me

can you please provide a example please

Post by **Jackolantern** » Sun Dec 01, 2013 7:47 pm

ORDER BY can be added to SELECT any query, and whatever is returned from the query will be put into order by the column you selected to order by. You can also add DESC or ASC to the end to put it in descending order or ascending order. Here is a good tutorial with examples.

justforfun · Post by **justforfun** » Sun Dec 01, 2013 8:03 pm

thank you for your help i have managed to get that ORDER function but the variables are in different tables so what i have done is once Testing is complete to get the best lap i have added the best lap into another table along with the players ID and level/group now that is all working but the table is not showing any data

here is the updated table code

Code: Select all

echo "<h2>Your Completed Testing laps</h2>";
echo"<small>";
     print "<center>";
     print "<table border='0' width='70%' cellspacing='5'>";
     print "<tr><td width='25%' valign='top'>";
     print "</td>";
     print "<td valign='top' width='75%'>";
     $bestlapinfo1 = "SELECT * from testresult where level = '$playerinfo3[level]' ORDER by bestlap";
     $bestlapinfo2 = mysql_query($bestlapinfo1) or die (mysql_error());
     print "<table border='0' bgcolor='#f8f8f8'>";
     print "<tr><td>Managers Name<font color='f8f8f8'>______</td><td>Best Lap Time<font color='f8f8f8'>______</td><td>Gap<font color='f8f8f8'>______</td></tr>";
     while ($bestlapinfo3 = mysql_fetch_array($bestlapinfo2))
{
       $bestlap = $bestlapinfo3['bestlap'];
       print "<tr><td>$bestlapinfo3[name]</td><td>"; echo date('i:s.', substr($bestlapinfo3['bestlap'], 0, -3)) . substr($bestlapinfo3['bestlap'], -3);
       print "</td><td>Gap</td></tr>";
}
                     ###<tr><td>Position<font color='f8f8f8'>______________________</font></td>


     print "</table>";
     print "</td></tr></table>";
     print "</center></small>";

sorry to be a pain

justforfun · Post by **justforfun** » Sun Dec 01, 2013 8:06 pm

all done thank you so much for your help

Post by **Jackolantern** » Mon Dec 02, 2013 1:29 am

Don't add the same piece of data in multiple parts of your database. Every piece of data needs to only be represented once, or you risk an "update anomaly", where the two different locations do not reflect the same value (in which case both have to be thrown out since you can't trust either one).

As with the other issue, the solution here is already in MySQL, and is called "joining". It allows you to query more than one table at a time, so you can compare any column in any table, compare, and compare, filter, sort, and everything else with them.

justforfun · Post by **justforfun** » Mon Dec 02, 2013 8:31 pm

thanks for that all done now i have to go back and change some of my MYSQL tables i just merged some of them together to reduce variables and what you stated of the abnormalities.

Another thing that i wanted to know is there any way that i can implement time into my site more specifically what i am wanting to do is have deadlines for certain pages e.g:

Qually 1 page deadline for the page to be used is Sunday 12am
Qually 2 page deadline for the page to be used is Monday 12am
Race Setup page deadline for the page to be used is Monday 6pm

(further to this)
between Monday 7:30 pm and 10:00 pm the Race screen will come live in order for users to watch there race (the site will go into Downtime after 10:00pm for 1 Hour to allow testing during Alpha and Beta)

is there a way that this can be implemented at all

Thanks

Post by **Jackolantern** » Mon Dec 02, 2013 10:41 pm

You can do that on any page you want. For example, say you have a main page for qualifications (I am assuming that is what qually means lol). The page would have code to check the database for active qualifications running right now. If there are none (probably like if you got no results when looking for active qualifications), then using an IF/ELSE block, you could show an error message saying something like "Sorry, there are no qualifications for you right now" and then exit the script. Or, if you structure your game so that the player goes to the qualifications page with a specific qualification they wanted to enter, then a quick database query could tell you if they are too late for it, and you could give a message telling them that they missed the qualification, and possibly tell them about the next one coming up.

The main thing you would need for this are MySQL Dates and working with PHP dates.

As far as users being able to go watch the race at a specific time, it would likely work much like the above. I am not sure how the races themselves are going to work, but the time-based functions would probably be a lot like the qualifications.

justforfun · Post by **justforfun** » Mon Dec 02, 2013 10:47 pm

Jackolantern wrote:You can do that on any page you want. For example, say you have a main page for qualifications (I am assuming that is what qually means lol). The page would have code to check the database for active qualifications running right now. If there are none (probably like if you got no results when looking for active qualifications), then using an IF/ELSE block, you could show an error message saying something like "Sorry, there are no qualifications for you right now" and then exit the script. Or, if you structure your game so that the player goes to the qualifications page with a specific qualification they wanted to enter, then a quick database query could tell you if they are too late for it, and you could give a message telling them that they missed the qualification, and possibly tell them about the next one coming up.

The main thing you would need for this are MySQL Dates and working with PHP dates.

As far as users being able to go watch the race at a specific time, it would likely work much like the above. I am not sure how the races themselves are going to work, but the time-based functions would probably be a lot like the qualifications.

yer mate was referring to Qualifying lol sorry for my abbreviations

that sounds so simple i think that i was over complicated the matter

thinking now of using this and have some sort of countdown near each page to show users the time that they have left to do certain tasks, and then break the link when inactive so they cannot even get into the page. of course with a query in the page to watch for cheaters who just type the URL in manually.

ow and i was only talking about the race screen to provide as much detail as i could mate to help with the answer

will get on this now lol

Cheers

Sorting PHP/HTML table by time

Sorting PHP/HTML table by time

Re: Sorting PHP/HTML table by time

Re: Sorting PHP/HTML table by time

Re: Sorting PHP/HTML table by time

Re: Sorting PHP/HTML table by time

Re: Sorting PHP/HTML table by time

Re: Sorting PHP/HTML table by time

Re: Sorting PHP/HTML table by time

Re: Sorting PHP/HTML table by time

Re: Sorting PHP/HTML table by time