...

View Full Version : Database output sorting



exzrael
07-07-2005, 09:49 PM
Im going to make this one short, sry to bother you all.

I have a dropdown-list and a button. When an option is selected in the list and I click the button a table with certain data should be presented on the page, depending on what option was selected.

This has been done correctly. The problem is that I wan't to be able to sort the table using my "headlines" as links.

Name Weight Creator Store -->
**** ***** ****** ****

If I click Name the table should be sorted from the Name column. Same if I click on Weight, the table sorts from Weight then. How is this possibly done?

Code for my varor.php - the page with dropdownlist and button (and the created table when showed)


<p align="center"></p>
<form method="POST" action="">
<p align="center"><select size="1" name="D1">
<option value="brod">Bröd</option>
<option value="fruktgront">Frukt & Grönt</option>
<option value="fardigmat">Färdigmat</option>
<option value="godiskakor">Godis & Kakor</option>
<option value="hemkok">Hem & Kök</option>
<option value="konserver">Konserver</option>
<option value="kottfagelfisk">Kött, Fågel, Fisk</option>
<option value="mejerivaror">Mejerivaror</option>
<option value="mjolgryn ">Mjöl & Gryn</option>
<option value="pastapotatisris">Pasta, Potatis, Ris</option>
</select><br>
<br>
<input type="submit" value="Skicka" name="B1"><input type="reset" value="Rensa" name="B2"></p>
</form>
</body>

<?php
include './func/functions.php';
if (isset($_POST['D1'])) {
produkt_query();
}
else {
echo ("<p>Välj ett alternativ innan du söker.</p>");
}
?>

Function for creating the table and showing it:


function produkt_query() {
$vilkenknapp = $_POST['D1'];
$sortera = "namn";
db_intern();
echo "<p>Om ej vikt/volym anges är priset ett kg-pris.</p>";
$asking = "SELECT namn AS Namn, vikt AS Vikt, tillverkare AS Tillverkare, butik AS Butik, pris AS Pris, ursprung AS Ursprung FROM artiklar WHERE grupp = '$vilkenknapp' ORDER BY '$sortera'";
$resultat = mysql_query($asking);

$affected = mysql_num_rows($resultat);

?>
<table border="0" cellspacing="3" cellpadding="3" align="center">
<tr>
<th>Namn</th>
<th>Vikt/Volym</th>
<th>Tillverkare</th>
<th>Butik</th>
<th>Pris</th>
<th>Ursprung</th>
</tr>
<?
$x = 0;
while($raden = mysql_fetch_row($resultat))
if($x == 0) {
echo "<tr bgcolor=\"#f6f1ee\"><td>$raden[0]</td><td>$raden[1]</td><td>$raden[2]</td><td>$raden[3]</td><td>$raden[4]</td><td>$raden[5]</td></tr>";
$x++;
} else {
echo "<tr bgcolor=\"#f6f1ee\"><td>$raden[0]</td><td>$raden[1]</td><td>$raden[2]</td><td>$raden[3]</td><td>$raden[4]</td><td>$raden[5]</td></tr>";
$x = 0;
}
}

?>
</table>
</td>
</tr>
</table>

exzrael
07-09-2005, 09:47 AM
Anyone?

delinear
07-09-2005, 11:32 AM
You could turn your table headings into links which pass a variable to the url telling php which sort order to use. I think this would work and with very little modification required to your existing code:


function produkt_query() {
$vilkenknapp = $_POST['D1'];
$sortera = (isset($_GET['sort']) ? $_GET['sort'] : 'namn'); // this will get the sort order if one is specified, if not it will just use the default sort order
db_intern();
echo "<p>Om ej vikt/volym anges är priset ett kg-pris.</p>";
$asking = "SELECT namn AS Namn, vikt AS Vikt, tillverkare AS Tillverkare, butik AS Butik, pris AS Pris, ursprung AS Ursprung FROM artiklar WHERE grupp = '$vilkenknapp' ORDER BY '$sortera'";
$resultat = mysql_query($asking);

$affected = mysql_num_rows($resultat);

?>
<table border="0" cellspacing="3" cellpadding="3" align="center">
<tr>
// now create the links that tell php which sort order to use in the query
<th><a href="<?php echo basename($_SERVER['PHP_SELF']); ?>?sort=namn">Namn</a></th>
<th><a href="<?php echo basename($_SERVER['PHP_SELF']); ?>?sort=vikt">Vikt/Volym</a></th>
<th><a href="<?php echo basename($_SERVER['PHP_SELF']); ?>?sort=tillverkare">Tillverkare</a></th>
<th><a href="<?php echo basename($_SERVER['PHP_SELF']); ?>?sort=butik">Butik</a></th>
<th><a href="<?php echo basename($_SERVER['PHP_SELF']); ?>?sort=pris">Pris</a></th>
<th><a href="<?php echo basename($_SERVER['PHP_SELF']); ?>?sort=ursprung">Ursprung</a></th>
</tr>

However, you will lose your POST data when you click on a link so you'll need to find a workaround for this. I would suggest you store the post data as sessions then access it via sessions, that way it won't be lost when the page is refreshed. Alternatively you could store the original post data as hidden form variables and change the links in the above code to javascript form submit links, so that the hidden form data is passed correctly from page to page - the downside of that is that it will only work properly in javascript enabled browsers of course.

Before you try that, though, another method that may work is to pass the contents of D1 in your url along with the sort order. If you make your links as follows:

<th><a href="<?php echo basename($_SERVER['PHP_SELF']); ?>?sort=namn&id=<?php echo $_POST['D1']; ?>">Namn</a></th>
<th><a href="<?php echo basename($_SERVER['PHP_SELF']); ?>?sort=vikt&id=<?php echo $_POST['D1']; ?>">Vikt/Volym</a></th>
.
.
etc
Then add the following to the top of your code:

// this will check if the form was subsequently posted, if not it will assign the existing variable from the url
if(isset($_GET['id']) && !isset($_POST['D1'])) {
$_POST['D1'] = $_GET['id'];
}
if (isset($_POST['D1'])) {
produkt_query();
}
I think that will work the way you want it to.

exzrael
07-09-2005, 06:05 PM
Thats great :) Got it to work instantly...thou, I can't get the same code (after modification) to work with a textbox and four options. Very irritating.

Search-form:


<p align="center"></p>
<form method="POST" action="">
<p align="center"><input type="text" name="T1" size="45"><br>
<input type="radio" name="R1" value="namn"> Namn&nbsp;
<input type="radio" name="R1" value="vikt"> Vikt&nbsp;
<input type="radio" name="R1" value="tillverkare"> Tillverkare&nbsp;
<input type="radio" name="R1" value="pris"> Pris<br>
<br>
<input type="submit" value="Skicka" name="B1"><input type="reset" value="Rensa" name="B2"></p>
</form>
</body>
<?php
include './func/functions.php';
if (isset($_POST['B1'])) {
produkt_search();

}
else {
echo ("<p>Välj ett alternativ innan du söker.</p>");
}
?>

The direct change in the function to show the table will be this one:


$asking = "SELECT namn AS Namn, vikt AS Vikt, tillverkare AS Tillverkare, butik AS Butik, pris AS Pris, ursprung AS Ursprung FROM artiklar WHERE $vilkenknapp LIKE '%$sok%' ORDER BY namn";

Instead of...


$asking = "SELECT namn AS Namn, vikt AS Vikt, tillverkare AS Tillverkare, butik AS Butik, pris AS Pris, ursprung AS Ursprung FROM artiklar WHERE grupp = '$vilkenknapp' ORDER BY '$sortera'";

Both these down here is used to show where to get data from and what to get.


$sok = $_POST['T1'];
$vilkenknapp = $_POST['R1'];

SpirtOfGrandeur
07-11-2005, 07:39 PM
Is there any reason why you do not use JS to do this? I have a quick and dirty one that I use to sort tables. Put this into a JS file called SortTable.js


var SORT_DESC = -1;
var SORT_ASC = 1;

var SORT_DESC_IMG = './images/Move_Up.gif'
var SORT_ASC_IMG = './images/Move_Down.gif'

var CLASSNAME = 'sortabletable'




function setSortTables() {
var aTable = document.getElementsByTagName('table');
for ( var x = 0 ; x < aTable.length ; x++ ) {
//alert(aTable[x]['tablesort']);
if ( aTable[x].className.toLowerCase() == CLASSNAME ) {
createSortTable(aTable[x]);
}
}
}

function attachEvent(obj, sEvent, fun) {
if (obj.addEventListener) {
obj.addEventListener(sEvent.substr(2), fun, false);
return true;
} else if (obj.attachEvent) {
var r = obj.attachEvent(sEvent, fun);
return r;
} else {
return false;
}
}

function getParent(obj, tagName) {
if ( !obj || typeof tagName != 'string' ) return null;
var tObj = obj;
tagName = tagName.toUpperCase();
while ( ! ( tObj == null || tObj.tagName == tagName ) ) {
//alert(tObj.tagName);
tObj = tObj.parentNode;
}
return tObj;
}

function sortByMe(e) {
var obj = returnEventTarget(e);

if ( obj == null ) return true;
var oTable = getParent(obj, 'TABLE');

if ( typeof oTable['sortcol'] == 'undefined' ) oTable['sortcol'] = -10000;
if ( isNaN(parseInt(oTable['sortcol'])) ) {
oTable['sortcol'] = -10000;
} else {
oTable['sortcol'] = parseInt(oTable['sortcol']);
}






var iSortCol = Math.abs( oTable['sortcol'] )
var iIndex = obj.cellIndex + 1;
var cSort = SORT_ASC;

if ( oTable['sortcol'] != -10000 && iSortCol != iIndex ) {
var oTR = getParent(obj, 'TR');
var aTD = oTR.getElementsByTagName('td');
if ( aTD.length == 0 ) {
var aTD = oTR.getElementsByTagName('th');
}
var oTD = aTD[iSortCol - 1];

var aImg = oTD.getElementsByTagName('img');
if ( aImg.length > 0 ) {
oTD.removeChild(aImg[aImg.length - 1]);
}


}


var aImg = obj.getElementsByTagName('img');
if ( aImg.length > 0 ) {
var oImg = aImg[aImg.length - 1];
} else {
var oImg = obj.appendChild(document.createElement('img'));
}

if ( iSortCol == iIndex && oTable['sortcol'] > 0 ) {
cSort = SORT_DESC;
oImg.src = SORT_DESC_IMG;
} else {
oImg.src = SORT_ASC_IMG;
}





var aBody = oTable.getElementsByTagName('tbody');
if ( aBody.length == 0 ) return true;
var oBody = aBody[0];

var oTRs = oBody.getElementsByTagName('TR');




var arr = new Array();

for (var x = 0; x < oTRs.length; x++) {
arr[x] = oTRs[x];
}

oTable['sortcol'] = iIndex * cSort;

//alert(oTable['sortcol']);

arr.sort(function (a, b) { return oTable_Sort( oTable['sortcol'] , a, b ); });
for ( var x = 0; x < arr.length ; x++ ) {
oBody.removeChild(arr[x]);
oBody.appendChild(arr[x]);
}




}


function oTable_Sort( iIndex, a, b ) {

var iDesc = 1;
var Index = iIndex;
if ( Index < 0 ) { //Decending
iDesc = -1;
Index = Math.abs(Index);
}

var aCell = a.getElementsByTagName('td')[ Index - 1 ];
if ( aCell == null ) return (1 * iDesc);
var bCell = b.getElementsByTagName('td')[ Index - 1 ];
if ( bCell == null ) return (-1 * iDesc);

//alert(b.getElementsByTagName('td')[ Index - 1 ]);

var aVal = parseInt(aCell.innerHTML);
var bVal = parseInt(bCell.innerHTML);
if ( isNaN(aVal) || isNaN(bVal) ) {
aVal = aCell.innerHTML;
bVal = bCell.innerHTML;
}
//alert( aVal + ' - ' + bVal);
if ( aVal < bVal ) return (-1 * iDesc);
if ( aVal > bVal ) return (1 * iDesc);

return 0;
}



function returnEventTarget(e) {
var obj = null;
if (e.target) obj = e.target;
else if (e.srcElement) obj = e.srcElement;
return obj;
}

function createSortTable(oTable) {
var aHead = oTable.getElementsByTagName('thead');
if ( aHead.length == 0 ) {
var oHead = document.createElement('thead');
oTable.appendChild(oHead);

var oBody = oTable.getElementsByTagName('tbody')[0];
var oTR = oBody.removeChild(oBody.firstChild);
oHead.appendChild(oTR);
var aHead = oTable.getElementsByTagName('thead');
} else if ( aHead.length != 1 ) {
return false;
}
var oHead = aHead[0];

var aTD = oHead.getElementsByTagName('th');
if ( aTD.length == 0 ) {
aTD = oHead.getElementsByTagName('td');
if ( aTD.length == 0 ) return false;
}


for ( var x = 0 ; x < aTD.length ; x++ ) {
attachEvent(aTD[x], 'onclick', function ( event ) { sortByMe(event); } );
}
}

var prev_onload = window.onload;
window.onload = function() { if (prev_onload) {prev_onload();} setSortTables(); }


Then include that JS file in your HTML like so


<script language="javascript" type="text/javascript" src="./SortTable.js"></script>


Then on any tables you want to have sortable add the classname of 'sortabletable' such as


<table class='sortabletable'>


Now you can do it on the fly without hitting the server again.


*EDIT*
O yes you must change SORT_DESC_IMG and SORT_ASC_IMG to be images on your server. And yes this code can be cleaned up a bit, but I have no time for that!



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum