[RESOLVED] How to sort a table both asc or desc by clicking on table titles/columns

chrisdee · Jan 26, 2012

Sorry for the long and bad title,. In the script below Im using glob to output wave files from a directory called audio. Currently I output 3 rows from the directory. They are filename, size and date. The script works fine as is but I want to add code so I can sort the table column categories name, size and date both asc or desc by clicking on them. Any tips how I do this in a easy and simple way ?

<?php
$pageNo = $_GET['pageNo'];
$filesPerPage = "25";

$dir = "audio/*.wav";
$read = glob($dir);
$slice = array_slice($read,$pageNo,$filesPerPage);

echo "<table border='1'>";
echo "<tr><td colspan='3'><a href=./>Phonetic Database</a> [";
echo count($read)." files] ";
echo "<select onChange='location=this.options[this.selectedIndex].value;' size='1'>";
echo "<option>Select page</option>";
for($i=1; $i<=count($read)/$filesPerPage; $i++) {
 echo "<option value=".$_SERVER['PHP_SELF']."?pageNo=$i>".$i."</option>";
}
echo "</select> [Page ".$pageNo."]</td></tr>";

$titles = array('name','size','date');
echo "<tr>";
foreach($titles as $title) {
 echo "<td bgcolor='lightgrey'><a href='$title'>$title</a></td>";
}
echo "</tr>";

foreach ($slice as $filename) {
echo "<tr>";
  echo "<td><a href='$filename'>".substr($filename,6)."</a></td>";
  echo "<td>&nbsp;".round(filesize($filename)/(1024)/(1024),1)." MB&nbsp;</td>";
  echo "<td>&nbsp;". date("d.m.Y H:i:s", filemtime($filename))."&nbsp;</td>";
echo "</tr>";
}
echo "</table>";
?>

niroshan · Jan 26, 2012

Hi chrisdee,

why don't you do this,

$sortArr = array();

if($sortType == 'filesize') //you can use this for name / date etc...
{
	foreach($read as $file)
	{
		$sortArr[] = filesize($file);
	}
}


arsort($sortArr); //this will sort the array decending maintain index association

while(list($key, $val) = each($sortArr))
{
	echo $read[$key].' => '.filesize($read[$key]).'<br />';
}

Hope this helps.

Thanks,
Regards,
Niroshan

chrisdee · Jan 27, 2012

Hello niroshan.

Thank you very much for your reply and your code suggestion.
Im trying to put your code into my script but it doesnt seem to do anything.
I guess Im doing it wrong.

<?php
$pageNo = $_GET['pageNo'];
$filesPerPage = "25";

$dir = "audio/*.wav";
$read = glob($dir);
$slice = array_slice($read,$pageNo,$filesPerPage);

echo "<table border='1'>";
echo "<tr><td colspan='3'><a href=./>Phonetic Database</a> [";
echo count($read)." files] ";
echo "<select onChange='location=this.options[this.selectedIndex].value;' size='1'>";
echo "<option>Select page</option>";
for($i=1; $i<=count($read)/$filesPerPage; $i++) {
 echo "<option value=".$_SERVER['PHP_SELF']."?pageNo=$i>".$i."</option>";
}
echo "</select> [Page ".$pageNo."]</td></tr>";

$sortArr = array();
if($sortType == 'filesize') //you can use this for name / date etc...
{
 foreach($read as $file)
 {
  $sortArr[] = filesize($file);
 }
}
arsort($sortArr); //this will sort the array decending maintain index association

while(list($key, $val) = each($sortArr))
{
 echo $read[$key].' => '.filesize($read[$key]).'<br />';
}

$titles = array('name','size','date');
echo "<tr>";
foreach($titles as $title) {
 echo "<td bgcolor='lightgrey'><a href='$title'>$title</a></td>";
}
echo "</tr>";

foreach ($slice as $filename) {
echo "<tr>";
  echo "<td><a href='$filename'>".substr($filename,6)."</a></td>";
  echo "<td>&nbsp;".round(filesize($filename)/(1024)/(1024),1)." MB&nbsp;</td>";
  echo "<td>&nbsp;". date("d.m.Y H:i:s", filemtime($filename))."&nbsp;</td>";
echo "</tr>";
}
echo "</table>";
?>

bradgrafelman · Jan 27, 2012

Where did you define $sortType?

chrisdee · Jan 27, 2012

Hi. I did not define it. It came from niroshan suggestion.

bradgrafelman · Jan 27, 2012

chrisdee;10995362 wrote:
Hi. I did not define it. It came from niroshan suggestion.

No it didn't. niroshan was showing you the basic idea, not something you can just copy and paste into your script and expect your problem to be solved (which is, IMHO, far from "helping" a person at all).

If you didn't define it, where did you expect that variable to come from? Or did you not even understand niroshan's suggestion and all and instead just blindly copied the first snippet of code that someone posted?

chrisdee · Jan 27, 2012

Well Im sorry but I just don't understand it completly.
I se some of the same variables in his suggestion as are in my script
so I thought I could almost cut and paste it.

Im not lazy though or looking for a free ride, because iv been trying to figure out this script for days now.

niroshan · Jan 29, 2012

Hi chrisdee,

Well Im sorry but I just don't understand it completly.

If you can tell me which part of the code that you don't understand then i`m surly able to help you out.

Thanks,
best regards,
niroshan

Weedpacket · Jan 30, 2012

chrisdee wrote:
Well Im sorry but I just don't understand it completly.

Then you shouldn't have tried to use it. For all you know it might have gone ahead and reset all their modification times to something useless; or even deleted the files themselves. (No offence, niroshan; I honestly don't think you're at all malicious and you wouldn't have done that. But if someone else trying to help misunderstood the requirements, or suggestions from two different people got conflated without understanding, who knows what the result might be?)

It's your responsibility to understand your code.

chrisdee · Jan 30, 2012

niroshan;10995430 wrote:
Hi chrisdee,

If you can tell me which part of the code that you don't understand then i`m surly able to help you out.

Thanks,
best regards,
niroshan

Thanks niroshan. I'll try to break it into parts.

1) If im not mistaken you are defining a empty array here, correct ? But I don't understand why you define an empty array ?

$sortArr = array();

2) Next, where does the variable $sortType come from, and how is it used ? Why is it set == filesize ?

if($sortType == 'filesize') //you can use this for name / date etc...

3) What does the [] mean infront of $sortArr, and why is it set = filesize($file) ?

$sortArr[] = filesize($file);

chrisdee · Jan 30, 2012

Weedpacket;10995483 wrote:
Then you shouldn't have tried to use it. For all you know it might have gone ahead and reset all their modification times to something useless; or even deleted the files themselves. (No offence, niroshan; I honestly don't think you're at all malicious and you wouldn't have done that. But if someone else trying to help misunderstood the requirements, or suggestions from two different people got conflated without understanding, who knows what the result might be?)

You are right, but niroshan's code did not look malicious to me. To me nothing in his code looks like it can delete anything or do harm.

Weedpacket;10995483 wrote:
It's your responsibility to understand your code.

Absolutely agree. Thats why I ask for advice and explenation.

laserlight · Jan 30, 2012

chrisdee wrote:
1) If im not mistaken you are defining a empty array here, correct ? But I don't understand why you define an empty array ?

Because it will be populated in the loop.

chrisdee wrote:
2) Next, where does the variable $sortType come from, and how is it used ?

That is for you to decide. For example, maybe you have a table in HTML, and the column names are clickable links. Clicking the "filesize" link sets a query string variable named "sortType" to the value of "filesize" that is processed to be the value of $sortType.

chrisdee wrote:
Why is it set == filesize ?

Recall the difference between = and ==

chrisdee wrote:
3) What does the [] mean infront of $sortArr, and why is it set = filesize($file) ?

Read the PHP manual on arrays.

niroshan · Jan 30, 2012

Hi chrisdee,

Here goes the explanation.

If im not mistaken you are defining a empty array here, correct ? But I don't understand why you define an empty array ?
$sortArr = array(); 

Well as you can see I`m using that array inside the foreach($read as $file) loop. Even most people consider PHP as loosely typed language, Its always best (advised) to define your variables. This has more serious issue when it comes to security so I advised you to read about it in the net.

Next, where does the variable $sortType come from, and how is it used ? Why is it set == filesize ?
if($sortType == 'filesize') //you can use this for name / date etc... 

As according to your original requirement you have mentioned that you need to sort it by name, size and date. That`s why I defined that variable to tell the PHP script to execute which type of sorting. Consider below example,

<a href="index.php?sort=name">Name</a> | <a href="index.php?sort=filesize">Size</a> |  <a href="index.php?sort=date">Date</a>


if(isset($_GET['sort']))
  $sortType = strtolower($_GET['sort']);

if($sortType == 'name')
  //do the sorting according to the name
else if($sortType == 'filesize')
  //do the sorting according to the size
if($sortType == 'date')
  //do the sorting according to the date

I`m sure you can modify the size example for name and date easily.

What does the [] mean infront of $sortArr, and why is it set = filesize($file) ?
$sortArr[] = filesize($file);

Well this is the basic of arrays. Im strongly suggest you to read about PHP arrays more. Consider below example:

Assume you have 3 files which has sizes as 2, 4, 6 (Kb`s).

//This basically loop though the file list and store the file size in the array. I`m storing the file size because I need to sort the files according to size. So for sort by name or date store name / date in that array
foreach($read as $file) 
{ 
  $sortArr[] = filesize($file); 
} 


// according to my example the  $sortArr will be
$sortArr[0] = 2;
$sortArr[1] = 4;
$sortArr[2] = 6;


//This is also equal to

$x = 0;
foreach($read as $file) 
{ 
  $sortArr[$x] = filesize($file); 
  $x++;
}

Hope this clear out your doubt`s.

Thanks,
Best regards,
Niroshan

chrisdee · Jan 31, 2012

Thank you very much for your explenation. Unfortunately Im down with the flu, but I will go through your explenations as soon as i get better.

chrisdee · Feb 9, 2012

Ok, I need to simplify things, because I just dont understand it yet.
Below I have stripped down the script so I hopefully will understand better.

I don't know if array_multisort is correct to use in this case, but I have used it. I can se the table content changes when I change SORT_DESC or SORT_ASC, so thats good. Now I need to some how use this to make the table titles name, size and date sortable by clicking the title.

Futher suggestions/examples are greatly apreciated.

<?php
$dir            = "audio/*.wav";
$read_dir       = glob($dir);
array_multisort($read_dir, SORT_DESC);

echo "<table border='1'>";
echo "<tr>";
$table_titles = array('name','size','date');
foreach($table_titles as $titles) {
        echo "<td><a href=".$_SERVER[PHP_SELF]."?sort=$titles>$titles</a></td>";
        }
echo "</tr>";

foreach ($read_dir as $files) {
        echo "<tr>";
                echo "<td>" . substr($files,6) . "</td>";
                echo "<td>" . round(filesize($files)/(1024)/(1024),1) . " MB</td>";
                echo "<td>" . date("d.m.Y H:i:s", filemtime($files)) . "</td>";
        echo "</tr>";
        }
echo "</table>";
?>

niroshan · Feb 9, 2012

Hi chrisdee,

array_multisort is perfect for this but the way your using it has some issues. The point that array_multisort is better is it allows us to sort one array and use that re-indexed array to sort another array. Please read about array_multisort in php.net. they have very good examples.

so back to your problem,

$dir            = "audio/*.wav"; 
$read_dir       = glob($dir); 
$nameArr = array();
$sizeArr = array();
$dateArr = array();

//create temp arrays so we can sort them and use their index to sort our main array
foreach ($read_dir as $files) { 
	$nameArr[] = substr($files,6);
	$sizeArr[] = round(filesize($files)/(1024)/(1024),1);
	$dateArr[] = filemtime($files);
}

//check the sort type
//when we use the temp arrays with multisort it first sort the temp array and use its index to sort the original array
if(isset($_GET['sort']))
{
	switch(strtolower(trim($_GET['sort'])))
	{
		case 'name':
			array_multisort($nameArr, SORT_ASC, $read_dir); 
			break;
		case 'size':
			array_multisort($sizeArr, SORT_ASC, $read_dir); 
			break;
		case 'date':
			array_multisort($dateArr, SORT_ASC, $read_dir); 
			break;
	}	
}

echo "<table border='1'>"; 
echo "<tr>"; 
$table_titles = array('name','size','date'); 
foreach($table_titles as $titles) { 
        echo "<td><a href='index.php?sort=$titles'>$titles</a></td>"; 
        } 
echo "</tr>"; 

foreach ($read_dir as $files) { 
        echo "<tr>"; 
                echo "<td>" . substr($files,6) . "</td>"; 
                echo "<td>" . round(filesize($files)/(1024)/(1024),1) . " MB</td>"; 
                echo "<td>" . date("d.m.Y H:i:s", filemtime($files)) . "</td>"; 
        echo "</tr>"; 
        } 
echo "</table>";

Hope this helps,

Thanks,
niroshan

chrisdee · Feb 9, 2012

It seems like I finally found a way to do it on my own, but thank you very much for your suggestion.

Here is what I did. I don't know if its good practice or not, but it works

<?php
$dir            = "audio/*.wav";
$read_dir       = glob($dir);

function sort_name($a,$b) {
        return glob($a) < glob($b);
}
function sort_size($a,$b) {
        return filesize($a) < filesize($b);
}
function sort_time($a,$b) {
        return filemtime($a) < filemtime($b);
}

if($_GET['sort']=='size') {
        usort($read_dir, "sort_size");
}
elseif($_GET['sort']=='time') {
        usort($read_dir, "sort_time");
}
else {
        usort($read_dir, "sort_name");
}

echo "<table border='1'>";
        echo "<tr>";
                echo "<td><a href=index.php?sort=name>Name</a></td>";
                echo "<td><a href=index.php?sort=size>Size</a></td>";
                echo "<td><a href=index.php?sort=time>Time</a></td>";
        echo "</tr>";

foreach ($read_dir as $files) {
        echo "<tr>";
                echo "<td>" . substr($files,6) . "</td>";
                echo "<td>" . round(filesize($files)/(1024)/(1024),1) . " MB</td>";
                echo "<td>" . date("d.m.Y H:i:s", filemtime($files)) . "</td>";
        echo "</tr>";
}
echo "</table>";
?>

chrisdee · Feb 22, 2012

I didnt want to start a new thread for this because its the same topic but for a mysql table.

Im trying to make a table with content form a mysql table sortable both asc and desc but I get several error messages. Se below :

Warning: mysql_fetch_array() expects parameter 1 to be resource, boolean given in /var/www/pdb/index.php on line 18

Warning: array_keys() expects parameter 1 to be array, null given in /var/www/pdb/index.php on line 25

Warning: Invalid argument supplied for foreach() in /var/www/pdb/index.php on line 25

Warning: mysql_fetch_array() expects parameter 1 to be resource, boolean given in /var/www/pdb/index.php on line 37

Warning: mysql_free_result() expects parameter 1 to be resource, boolean given in /var/www/pdb/index.php on line 49

Warning: mysql_free_result() expects parameter 1 to be resource, boolean given in /var/www/pdb/index.php on line 50

I apreciate if anybody could tell me / show me how to solve this.
Here is my script :

<?php           echo "<a href=./>Home</a>";
// mysql connect
mysql_connect("localhost", "user", "pass") or die(mysql_error());
mysql_select_db("pdb");

// variables
$sort   = $_GET['sort'];

if (empty($sort)) {
        $sort = "ASC";
}
else {
        $sort = "DESC";
}

// query table pdb
$query  = mysql_query("SELECT * FROM pdb ORDER BY " . $sort);
$keys   = mysql_fetch_array($query, MYSQL_ASSOC);

// html table
echo "<table border=1>";

// html table column keys
echo "<tr>";
foreach(array_keys($keys) as $key) {
        if($sort==$key) {
                echo "<td bgcolor=lightgreen><a href=" . $_SERVER['PHP_SELF'] . "?sort=" . $key . "><font color=red>&#8681</font></a>" . $key . "</td>";
        }
        else {
                echo "<td bgcolor=lightblue><a href=" . $_SERVER['PHP_SELF'] . "?sort=" . $key . ">&#8681</a>" . $key . "</td>";
        }
}
echo "</tr>";

// html table content
$result = mysql_query("SELECT * FROM pdb ORDER BY " . $sort);
while ($rows = mysql_fetch_array($result, MYSQL_ASSOC)) {
        echo "<tr>";
        foreach($rows as $row) {
                echo "<td>" . $row . " ";
        }
        echo "</tr>";
}

// end html table
echo "</table>";

// close mysql connection
mysql_free_result($query);
mysql_free_result($result);
?>

Derokorian · Feb 22, 2012

You should check that mysql_* functions executed succesfully before trying to use the result. SEE: http://www.phpbuilder.com/board/showthread.php?p=10991229 (From BG's signature)

Also note that mysql extension has been deprecated in favor of mysqli extension (the i stands for improved)

chrisdee · Feb 23, 2012

Derokorian;10997380 wrote:
You should check that mysql_* functions executed succesfully before trying to use the result. SEE: http://www.phpbuilder.com/board/showthread.php?p=10991229 (From BG's signature)

Also note that mysql extension has been deprecated in favor of mysqli extension (the i stands for improved)

Thank you very much for the information.
I finally found a solution based on another thread. I post only the link to it. As you can se I have used mysqli instead of mysql. Again, thank you.

http://www.phpbuilder.com/board/showpost.php?p=10997464&postcount=8

[RESOLVED] How to sort a table both asc or desc by clicking on table titles/columns

Cchrisdee

Nniroshan

Cchrisdee

Bbradgrafelman

Cchrisdee

Bbradgrafelman

Cchrisdee

Nniroshan

Weedpacket

Cchrisdee

Cchrisdee

laserlight

Nniroshan

Cchrisdee

Cchrisdee

Nniroshan

Cchrisdee

Cchrisdee

DDerokorian

Cchrisdee