[SOLVED] HTML links used for DB sorting

[soycharliente]

Anyone know of any tutorials about the best way to use column headers as links for sorting DB results to reorder table information?

 

I hope that makes sense.

 

Specifically, a "best practice" type of deal. Do I use a $_GET and then a switch() for multiple situations? Or is there an easier way than coding for every situation?

 

Recently I've been seeing a lot of new ways to do things that I had never thought about before, so I thought I'd ask if anyone knew of a good way to handle this. (I'd share one, but it's not really PHP related. More CSS background-image related.)

[soycharliente]

I feel like I should say this too:

 

I already know HOW to do it, I just feel like WHAT I'm doing could be better. For like of a better way to say this, I feel like my code is what someone who is just learning what to do would write when it could be much better.

[soycharliente]

Ok. I've been trying to think outside the box and come up with my own solution. Either no one has any help to offer yet (which is fine, honestly) or everyone is waiting for me to try something on my own. Either way, still looking for a possible tutorial while also trying my own stuff.

 

Details:

I have a series of tables that contain information about where and when I ate lunch. I'm displaying a list of all the unique locations with their respective tally counts. I'd like the "headings" to serve as sort links. Right now I'm only working with the tally link. It's supposed to default to ASC and then switch to DESC if sorting by tally ASC.

 

Problem:

The tally link always shows ASC.

 

Code so far:

<div>
<?php $sort = $_GET['s']; $sort_data = explode('.',$sort); ?>
<?php $columns = array(-1=>'tally',1=>'loc'); $orders = array(-1=>'DESC',1=>'ASC'); ?>
<?php
	switch($sort_data[0])
	{
		case "tee":
			$column = "tally";
			break;
		default:
			$column = "loc";
			break;
	}
	switch($sort_data[1])
	{
		case "DESC":
			$order = "DESC";
			$other_order = $orders[abs(array_search($order,$orders))];
			break;
		default:
			$order = "ASC";
			$other_order = $orders[abs(array_search($order,$orders))];
			break;
	}
?>
<h3><a href="?s=el.DESC">Locations</a> (<a href="?s=tee.<?php echo empty($sort_data[1]) ? "ASC" : $other_order; ?>">#</a>)</h3>
<?php
dbconnect();
$sql = "SELECT el.`loc`,tee.`tally`,tee.`pid` FROM `lunch_locations` el
			JOIN `lunch_tally` tee ON el.id=tee.pid
			ORDER BY `".$column."` ".$order;
$result = mysql_query($sql) OR DIE ("/Index Error #1.");
$rows = mysql_num_rows($result);
if ($rows > 0)
{
	echo '<ul>';
	while ($r = mysql_fetch_array($result))
	{
		/* $pid = $r['pid']; */
		$loc = htmlspecialchars($r['loc']);
		$tally = $r['tally'];
		echo '<li>'.$loc.' ('.$tally.')</li>';
	}
	echo '</ul>';
}
dbclose();
?>

</div>

[soycharliente]

Wow. I'm an idiot.

 

The absolute value of both -1 and 1 is 1. If I want to switch back and forth, I need to multiply by -1.

 

Now the problem is that both links are ASC or DESC at the same time. What can I do to make their sorting status' independent of each other?

 

<?php $sort = $_GET['s']; $sort_data = explode('.',$sort); ?>
<?php $columns = array(-1=>'tally',1=>'loc'); $orders = array(-1=>'DESC',1=>'ASC'); ?>
<?php
switch($sort_data[0])
{
	case "tee":
		$column = "tally";
		break;
	default:
		$column = "loc";
		break;
}
switch($sort_data[1])
{
	case "DESC":
		$order = "DESC";
		$other_order = $orders[-1*array_search($order,$orders)];
		break;
	default:
		$order = "ASC";
		$other_order = $orders[-1*array_search($order,$orders)];
		break;
}
?>
<h3>
<a href="?s=el.<?php echo empty($sort_data[1]) ? "ASC" : $other_order; ?>">Locations</a>
(<a href="?s=tee.<?php echo empty($sort_data[1]) ? "ASC" : $other_order; ?>">#</a>)
</h3>
<?php
dbconnect();
$sql = "SELECT el.`loc`,tee.`tally`,tee.`pid` FROM `lunch_locations` el
		JOIN `lunch_tally` tee ON el.id=tee.pid
		ORDER BY `".$column."` ".$order;
$result = mysql_query($sql) OR DIE ("/Index Error #1.");
$rows = mysql_num_rows($result);
if ($rows > 0)
{
echo '<ul>';
while ($r = mysql_fetch_array($result))
{
	/* $pid = $r['pid']; */
	$loc = htmlspecialchars($r['loc']);
	$tally = $r['tally'];
	echo '<li>'.$loc.' ('.$tally.')</li>';
}
echo '</ul>';
}
dbclose();
?>

[soycharliente]

Anyone have a chance to read this? Can anyone offer some help?

[soycharliente]

*bump*

 

I worked a little bit on this over the weekend, but I'm still getting nowhere. The "work" I did was try to figure out the different situation that would occur between the links and figure out if I could modify what I have posted below. I haven't made any changes yet.

[dzelenika]

*bump*

 

I worked a little bit on this over the weekend, but I'm still getting nowhere. The "work" I did was try to figure out the different situation that would occur between the links and figure out if I could modify what I have posted below. I haven't made any changes yet.

 

I have worked the same problem.

I created object called grid which processed requests associated with grid state changing (change order field and type, change page to show, processing showed data filtering ...)

Object is saving the actual state to database or session variable.

State means: actual page, sort type and field and values and names of fields by which is grid filtered. After each page is requested, object is loaded, requests processed and HTML (XML in my case) with data to show created:

 

<?php
...
$tab = new grid($state, $DSDoc, $_REQUEST, true);// $state is loaded from db or sessionm
$rs_client = new rowSet('client', $client, " $afWh ");// data to show
$tab->processRequest($rs_client); //process request over data and eventually change existing state
$tab->fillXML(); // create formated data for showing
...
?>

 

[jjacquay712]

Hey guys, this is the way i do it:

 

<?php
function order( $field ) {
if ( $_GET['field'] == $field ) {
	if ( $_GET['order'] == "desc" ) {
		return "javascript: updateTable('view_upload_ajax.php?field=" . $field . "&order=asc');";	
	} else {
		return "javascript: updateTable('view_upload_ajax.php?field=" . $field . "&order=desc');";	
	}
} else {
	return "javascript: updateTable('view_upload_ajax.php?field=" . $field . "&order=desc');";
}
}
?>

 

This is the js that uses ajax to update the html table:

function updateTable(url) {
var request = getAjaxObject();

request.onreadystatechange = function() {
    	if ( request.readyState == 4 ) {
    		document.getElementById("uploadtable").innerHTML = request.responseText;
}
    }
    
    request.open("GET", url, true);
request.send(null);
}

 

[soycharliente]

Thanks for the help. I'll probably use this or something super similar and keep trying to figure out what I can do to wrap in the <noscript> tags. I'd really like to figure out a way to do it with straight HTML and PHP in case I have any users that have JavaScript disabled.

 

So basically, I'm still stuck on my code.

[jjacquay712]

you can do the same thing without javascript, ill try to code it up that way for you, give me 10 minutes

[soycharliente]

I've already posted code and what's wrong with it. You don't have to code something up. You could just take a look at my code and see if you can figure out what's wrong with it.

[jjacquay712]

Im to lazy to debug someone elses code . but i have tested the code and it works well, 5 more minutes.

[jjacquay712]

Sorry it took so long. Here is the code:

 

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Table</title>
</head>

<body>

<table>
<?php
require("db_config.php");

function order( $field ) {
if ( $_GET['field'] == $field ) {
	if ( $_GET['order'] == "desc" ) {
		return "demo.php?field=" . $field . "&order=asc";	
	} else {
		return "demo.php?field=" . $field . "&order=desc";	
	}
} else {
	return "demo.php?field=" . $field . "&order=desc";
}
}

if ( $_GET['field'] && $_GET['order'] ) {
$order = " ORDER BY " . $_GET['field'] . " " . $_GET['order'];
}
$query = mysql_query("SELECT * FROM visitors" . $order);

$first = false;
while ( $results = mysql_fetch_assoc($query) ) {
if ( !$first ) {
	echo '<tr>';
	foreach( $results as $table_column => $column_data ) {
		echo "<td><a href=\"" . order($table_column) . "\">$table_column</a></td>";
    }
	echo '</tr>';
	$first = true;
}
echo '<tr>';
	foreach( $results as $table_column => $column_data ) {
		echo "<td>$column_data</td>";
    }
echo '</tr>';
}


?>
</table>
</body>
</html>

 

And here is a working demo: http://johnj.gknu.com/demo.php

 

EDIT: This is vulnerable to to sql injection, so sanitize the input if you end up using this code

[soycharliente]

Thanks. I'll look through it, compare with my code, and see what comes out.

 

I love how you added the border after the fact. It randomly showed up while I was checking it out.

[aggrav8d]

i store the page name, sortby_column, sortby_direction, and pagination_number in a session variable.  I store this separately *per page* so that if the user has two windows open she can keep them both organized they way she wants.

 

I use very small forms that POST the queries rather than expose my column names to every user, and I use up/down arrow image buttons so that I don't need javascript but everything stays pretty intuitive.  (the currently selected column uses arrows in a different color)

 

I wrote a set of wrapper methods that simplify 99% of this work for me and all I have to do is tell it which variables I want to query.  In the case of non-trivial queries I still have to do most of this by hand but those are pretty rare for me.

 

[nadeemshafi9]

http://extjs.com/deploy/dev/examples/grid/array-grid.html

http://extjs.com/deploy/dev/examples/grid/paging.html

http://extjs.com/deploy/dev/examples/samples.html

 

you can add a paging toolabr and compleetly remove the panel and just use the grid, the system works on a data.store it can be xml json or a js array. You can connect a search bar to it, what it basicaly does is, when you search in teh bar or click next on teh paging bar it sends the variables throgh the store defined autoload URL which then returns the resul again and then you reload the grid. MAGIC, you can have remote or local search or paging.

[soycharliente]

http://extjs.com/deploy/dev/examples/grid/array-grid.html

 

I've worked with ExtJS before. Pretty nice stuff. I researched it for my last coop job.

 

Again:

I'd really like to figure out a way to do it with straight HTML and PHP in case I have any users that have JavaScript disabled.

[nadeemshafi9]

http://extjs.com/deploy/dev/examples/grid/array-grid.html

 

I've worked with ExtJS before. Pretty nice stuff. I researched it for my last coop job.

 

Again:

I'd really like to figure out a way to do it with straight HTML and PHP in case I have any users that have JavaScript disabled.

 

there isnt much you cna do with javascripot disabled you should stop worrying about that, anywyas

 

you send the variables to the script with the url eg direction and ordercolumn and you basicaly perform the sql lookup based on them whats so hard ?

[nadeemshafi9]

i am now programming in pure extjs and php with json i dont use HTML anymore

[soycharliente]

i am now programming in pure extjs and php with json i dont use HTML anymore

 

That doesn't matter.

 

 

there isnt much you cna do with javascripot disabled you should stop worrying about that, anywyas

 

you send the variables to the script with the url eg direction and ordercolumn and you basicaly perform the sql lookup based on them whats so hard ?

 

It's "hard" because I can't get what I'm trying to do to work.

 

It sounds like you haven't read any of the past posts or your comments would have been answered already. Don't try to jump in to the middle without reading everything first. Maybe you should leave this thread.

 

 

[nadeemshafi9]

i read the first post, i asumed you would have highlighted your issues there, i just offered you some solutions. If you dont like me for no apparent reason then i will leave, this is a silly thread anyways, you have too much time on your hands what are you a millionaire ?

 

The first version i ever used in my career was str8 off a thread on this site,

 

my issue wasent teh sorting that was way easy.

 

the issue is teh paging in pure html and php.

[soycharliente]

Thread "solved." I'll post new questions later. I wouldn't want to read this thread anymore after he typed all that smut.

[nadeemshafi9]

Thread "solved." I'll post new questions later. I wouldn't want to read this thread anymore after he typed all that smut.

 

what are you talking about ? are u a mental case ?

 

you asked a question i gave you 2 answers

 

1. stop trying to use old methods and skip developing on them it will save you time and you will develoop more richer apps

 

2. stop concentrating on issues in the feild of paging and ordering grids that are prety simple, rather concentrate on teh problem area wich is paging, i would appreciate a nice neat algo for that.

 

hope that helps