Displaying MySql Table Done, Just Need it To Show in Order - PHP Server Side Scripting forum at WebmasterWorld - WebmasterWorld

Forum Moderators: coopster

Message Too Old, No Replies

Displaying MySql Table Done, Just Need it To Show in Order

I need help on learning how to put my data in order that has been inputed.

jillchang

12:01 pm on Mar 23, 2014 (gmt 0)

10+ Year Member

Hey guys,
I successfully got my datatable list data from mysql.
However, now I learned I have to use some kind of jquery, which is something I am new to, in order to list them in order.

I am trying to make this into a stats page, where if I click on the name category, it will order them (not only the column, but accordingly by rows). [Put * to make it easier to see what I mean]

For example:
Name Points
1 4**
2 3*
5 7***

If I were to click on Points Category, I want it to do this:

Name Points
2 3*
1 4**
5 7***

It listed the points from low to high, the 3 AND the 2 went to row 1 TOGETHER.

Would be nice if I reclick on Points Category, it will do this:

Name Points
5 7***
1 4**
2 3*

Here is my code:

<html>

<head>

</head>

<body>
<?php
$con = mysql_connect ("user.site.nfoservers.com","user","password");
if (!$con){
die("Can not connect: " . mysql_error());
}
mysql_select_db ("user_nmrihstats", $con);
//query
$query = mysql_query("SELECT steam_id, name, points, kills, deaths from nmrihstats", $con);

//write the results
echo "<table border=1>
<tr>
<th>Name</th>
<th>Steam ID</th>
<th>Points</th>
<th>Kills</th>
<th>Deaths</th>
</tr>";

while ($row = mysql_fetch_array($query)) {

echo "<tr>";
echo "<td>" . $row['name'] . "</td>";
echo "<td>" . $row['steam_id'] . "</td>";
echo "<td>" . $row['points'] . "</td>";
echo "<td>" . $row['kills'] . "</td>";
echo "<td>" . $row['deaths'] . "</td>";
echo "</tr>";
}// close the loop

echo "</table>";
mysql_close($con);

?>
</body>
</html>

This code only lists them randomly successfully.

[edited by: phranque at 9:48 pm (utc) on Mar 24, 2014]
[edit reason] anonymized db connection data [/edit]

mack

3:03 am on Mar 25, 2014 (gmt 0)

WebmasterWorld Administrator

10+ Year Member

Top Contributors Of The Month

First off, welcome to WebmasterWorld jillchang.

I guess by Jquery you want the data to update when the user clicks on a criteria.. Suck as points.

I don't know much about jquery, but I can help you with the mysql select.

When you run a mqsql query, you are effectively selecting the records from the database newest to oldest. In some cases this would be enough, but not in your case.

What you want to do is use the "ORDER BY" clause in your sql syntax.

A normal query...

"SELECT * from tablename"

with order by...

"SELECT * from tabename ORDER BY points DESC"

This example assumes you have a row called points and it contains a numerical value. You can specify order by DESC (descending) or ASC (Ascending).

Regarding the use of jquery or any form of AJAX, I think what you need to do is have an area on your page where you want your data to be displayed, enclose this area within a div, and place the php code to display the data within the div. You should also have your links to "sort" your data by your parameters.

If you are happy for the page to reload when a user selects how they want to filter the data you could easily do this by having a link that appends the method to the url. For example if your current script uses the following..

example.net/script/display.php

You could use...
example.net/script/display.php?filter=points

Then on the page itself...

$filter = $GET['filter'];

and finaly within the mysql query...

"SELECT * from tabename ORDER BY $filter DESC"

You will need to make sure you sanitise the string before it is sent to the sql query to prevent sql injections. You will also need to check if there is a value for filter. If there is no filter then it should use a default value..

If ($filter == "")
{
$filter = "name";
}

Hope this gives you some ideas.

Mack.

penders

11:16 am on Mar 25, 2014 (gmt 0)

WebmasterWorld Senior Member

10+ Year Member

Top Contributors Of The Month

I would certainly start off doing it the way mack describes, which I would say follows best practises. This ensures good accessibility and potentially allows users to easily bookmark sorted results.

Then, using "progressive enhancement", you can add a jQuery solution on top of this if you wish. There is a "tablesorter" plugin that is specifically for sorting well formed HTML tables, without the need to send requests back to the server. For small (non-paginated) tables this gives great results with amazingly little effort.