homepage Welcome to WebmasterWorld Guest from 54.237.98.229
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Pubcon Platinum Sponsor 2014
Home / Forums Index / Code, Content, and Presentation / PHP Server Side Scripting
Forum Library, Charter, Moderators: coopster & jatar k

PHP Server Side Scripting Forum

    
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



 
Msg#: 4656440 posted 12:01 pm on Mar 23, 2014 (gmt 0)

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

WebmasterWorld Administrator mack us a WebmasterWorld Top Contributor of All Time 10+ Year Member



 
Msg#: 4656440 posted 3:03 am on Mar 25, 2014 (gmt 0)

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

WebmasterWorld Senior Member penders us a WebmasterWorld Top Contributor of All Time 5+ Year Member Top Contributors Of The Month



 
Msg#: 4656440 posted 11:16 am on Mar 25, 2014 (gmt 0)

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.

Global Options:
 top home search open messages active posts  
 

Home / Forums Index / Code, Content, and Presentation / PHP Server Side Scripting
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved