displaying a list from db, allowing users to 'sort by':...

[LearningCoder]

Hello,

I am creating a site and want to display the members list on a separate page. I want the user to be able to re-order this list by alphabet, by join date etc etc but I am not sure how to go about creating it.

Do I create a html form or do I need to use javascript? Or neither?

I guess I would have to create different functions to deal with the re-ordering? One to re-order to alphabetical, one to re-order to join date?

Can anyone guide me on the best way to achieve this?

I'm really stumped at the moment been trying to think of a way for a good hour now but I find a potential issue with everything I have thought of.

Thanks for any help in advance.

Kind regards,

LC.

[mlseim]

You'll have two links at the top of the column: asc | desc

<a href="sort.php?o=asc">asc</a> | a href="sort.php?o=desc">desc</a>

sort.php simply saves their choice as a cookie and returns back to the list.

Each time the list is displayed, it looks at the sort cookie and depending on
which one they pick, the script will change the ORDER BY in the query.

[LearningCoder]

Ah thank you very much.

So I only need 1 set of asc|desc links? Do I need to have a drop down box with the values Join Date and Alphabetical?

Regards,

LC.

[Fou-Lu]

ASC/DESC can be set toggled when using a link to draw your data. Typically you sort only by one specific type, so my querystring would include something like sort=field&dir=desc, where if no direction is set it assumes ASC.
Then you construct your links for each sortable header, and always provide a sort=fieldname. Then with the direction, you can either add it as whichever default you want, but the trick is to check and see if a sort is already selected, then invert the direction it goes for when you create that link.

Datagridviews are also used exclusively for these purposes. They are handled with a lot of ajax as well as PHP. You may be able to google up a generic data grid view for PHP.

[mlseim]

Is that like drastic data grid?

http://www.drasticdata.nl/DrasticTools/ExampleGrid1.php

PHP/AJAX/MySQL?

[LearningCoder]

So, simply put, I need to use AJAX to retrieve the data from the database so then it can dynamically reload within the page?

That link is pretty much exactly what I am trying to achieve, apart from I only want to display the username and join date.

Regards,

LC.

[Fou-Lu]

Originally Posted by LearningCoder

So, simply put, I need to use AJAX to retrieve the data from the database so then it can dynamically reload within the page?

That link is pretty much exactly what I am trying to achieve, apart from I only want to display the username and join date.

Regards,

LC.

You don't need AJAX. You only need it if you want it to load in the background. The same effect can be created statically with using just links and the querystring.

Originally Posted by mlseim

Is that like drastic data grid?

http://www.drasticdata.nl/DrasticTools/ExampleGrid1.php

PHP/AJAX/MySQL?

Yep, that's exactly what it is. Datagrid views are most common in C#/ASP.NET, as its built into the language. I'd assume that is created to mimic it in PHP.

[LearningCoder]

Yea, i want it to dynamically load without having to reload the page. I'll read into retrieving data with AJAX, then I'll look into AJAX with PHP. I read my JS book last night which has a whole chapter dedicated to AJAX. I know the basic syntax to setup the data retrieval, but it's just working with the AJAX and PHP which I know absolutely nothing about.

Also, because I want the data to display dynamically, do I display the results with PHP first, then use the AJAX to change it? Or do I have to dynamically load the PHP with AJAX first, then change again with AJAX?

Here is the code I have at the moment:

PHP Code:

<div id="content">

   <div id="order_links_div">
       <a href="sort.php?o=asc">ASC</a>|<a href="sort.php?o=desc">DESC</a>
   </div>

<table id="members_table" name="members_table">
  <tr>
     <th>Member No.</th>  
     <th>Username</th>  
     <th>Join Date</th>
  </tr>     
  <?php
  require ("connectdb.php");
  $sql = mysql_query("SELECT * FROM members");
  if(!$sql){
     echo "There was an error retrieving the data.";
     exit(0);
  }
  
  while($row = mysql_fetch_array($sql)){
     echo "<tr><td>".$row['id']."</td><td>".$row['username']."</td><td>".$row['join_date']."</td></tr>";
  }
  
  ?>
</table>

</div>

I'm guessing this is not the right way because I don't think this will change the data around because it is coded to display anything retrieved from the database in that order.....

My question is, do I have to load the database results as soon as the 'members.php' page loads using PHP, or do I use AJAX from the start?

Hope you can understand my issue.

Thanks for all your replies.

Regards,

LC.

[LearningCoder]

Here is my HTML:

 <a href="sort.phpo=asc" onclick="get_file()">ASC</a>|
	   <a href="sort.php?o=desc" onclick="javascripts/ajax_db_call.js">DESC</a>

Here is my ajax_db_call.js file:

function get_file(){

var xmlhttp;

if (window.XMLHttpRequest)
   {// code for IE7+, Firefox, Chrome, Opera, Safari
   xmlhttp=new XMLHttpRequest();
   }
else
   {// code for IE6, IE5
   xmlhttp=new ActiveXObject("Microsoft.XMLHTTP");
   }

xmlhttp.open("GET","sort.php",true);
xmlhttp.send();
}

Here is my sort.php:

PHP Code:

<?php

$order = $_GET['o'];

if(!isset($order)){
   exit(0);
}

require("connectdb.php");


if($order == "asc"){
   $sql = mysql_query("SELECT * FROM members ORDER BY username");
   
   if(!$sql){
      echo "error retrieving data";
      exit(0);
   }
   
   while ($row = mysql_fetch_array($sql)){
   
      echo "<tr><td>".$row['id']."</td><td>".$row['username']."</td><td>".$row['join_date']."</td></tr>";
   
   
   }
   
   
}

?>

Anyone know what I am doing wrong?

Regards,

LC.