Results 1 to 3 of 3
  1. #1
    Join Date
    Mar 2008
    Posts
    10

    Unanswered: sorting sql results

    I am sure there is something simple i am doing wrong, but i cant seem to get this to work. Basically, i have a page that has some embedded PHP, that has an HTML form. The user types in the criteria to search on, and then clicks submit. This results in a table being displayed BELOW the form, which is a select statement that uses some of the boxes in the form in the WHERE clause.

    What i want this to do is, let the user narrow down our current stock of diamonds. This is currently working - narrowing it down does work based on the form input boxes, but again, when the headers are clicked, it basically does a refresh of the page, leaving the boxes blank, so the sorted results are actually the TABLE sorted (hope this makes sense - cant think of an easier way to describe this)

    I have now made the headers clickable so the results are sortable. The problem is however, once you click the header to sort the results, you are not REALLY sorting the results, you are sorting based on all the variables being blank - meaning, sorting the entire table. I want to sort only the results. I am using the following code:

    Code:
    <form method="post">
    <table border=1>
    	<tr>
    		<td colspan =2>
    			ENTER DIAMONDS INFORMATION
    		</td>
    	</tr>
    	<tr>
    		<td>
    			Color: 
    		</td>
    		<td>
    			<input type="text" name="stoneColor" value="<?php if (isset($_POST['stoneColor'])) echo $POST['stoneColor']; ?>" ><br>
    		</td>
    	</tr>
    	<tr>
    		<td>
    			Clarity: 
    		</td>
    		<td>
    			<input type="text" name="stoneClarity" value="<?php if (isset($_POST['stoneClarity'])) echo $POST['stoneClarity']; ?>" ><br>
    		</td>
    	</tr>
    	<tr>
    		<td>
    			Beginning Weight:
    		</td>
    		<td>
    			<input type="text" name="stoneBWeight" value="<?php if (isset($_POST['stoneBWeight'])) echo $POST['stoneBWeight']; ?>"><br>
    		</td>
    	</tr>
    	<tr>
    		<td>
    			Ending Weight: 
    		</td>
    		<td>
    			<input type="text" name="stoneEWeight" value="<?php if (isset($_POST['stoneEWeight'])) echo $POST['stoneEWeight']; ?>"><br>
    		</td>
    	</tr>
    </table>
    <input type="submit" name="submit">
    </form>
    <br><BR><BR><BR>
    
    <?php
    $conn=odbc_connect("LUX2","","","");
    $stoneColor = $_REQUEST['stoneColor'];
    $stoneClarity = $_REQUEST['stoneClarity'];
    $stoneBWeight = $_REQUEST['stoneBWeight'];
    $stoneEWeight = $_REQUEST['stoneEWeight'];
    $sql="select top 100 ItemNum, Description, StoneShape1, StoneType1, StoneWeight1, StoneClarity1, StoneColor1 from Item where DepartmentNum = 1 and StoneShape1 = 'R' "; //and vendornum = 1877
    //if beginning weight is not entered, set it to 0
    if(empty($stoneBWeight))
    {
    	$stoneBWeight="0";
    }
    ///if ending weight not entered, set it to 999
    if(empty($stoneEWeight))
    {
    	$stoneEWeight="999";
    }
    //adds weight to SQL statement
    $sql .= " and StoneWeight1>$stoneBWeight and StoneWeight1<$stoneEWeight";
    //check color
    if(!empty($stoneColor))
    {
    	$sql .= " and StoneColor1 = '$stoneColor'";
    }
    if(!empty($stoneClarity))
    {
    	$sql .= "and StoneClarity1 = '$stoneClarity'";
    }
    $sort = (isset($_GET['sort'])) ? $_GET['sort'] : 'ItemNum';
    switch ($sort) {
    	case 'Description':
    		$order_by = 'Description ASC';
    	break;
    
    	case 'StoneShape1':
    		$order_by = 'StoneShape1 ASC';
    	break;
    
    	case 'StoneWeight1':
    		$order_by = 'StoneWeight1 ASC';
    	break;
    
    	case 'StoneClarity1':
    		$order_by = 'StoneClarity1 ASC';
    	break;
    
    	case 'StoneColor1':
    		$order_by = 'StoneColor1 ASC';
    	break;
    	case 'ItemNum':
    		$order_by = 'ItemNum ASC';
    	break;
    
    	default:
    		$order_by = 'ItemNum ASC';
    	break;
    }
    $sql .= " ORDER BY $order_by";
    
    echo "\n";
    $rs=odbc_exec($conn,$sql);  
    echo "<table border=1>\n";
    $numfields = odbc_num_fields($rs);
    echo '<tr>
    	<td><b><a href="http://192.168.2.203/pervasive2.php?sort=ItemNum">ItemNum</a></b></td>
    	<td><b><a href="http://192.168.2.203/pervasive2.php?sort=Description">Description</a></b></td>
    	<td><b><a href="http://192.168.2.203/pervasive2.php?sort=StoneShape1">StoneShape1</a></b></td>
    	<td><b><a href="http://192.168.2.203/pervasive2.php?sort=StoneType1">StoneType1</a></b></td>
    	<td><b><a href="http://192.168.2.203/pervasive2.php?sort=StoneWeight">StoneWeight1</a></b></td>
    	<td><b><a href="http://192.168.2.203/pervasive2.php?sort=StoneClarity1">StoneClarity1</a></b></td>
    	<td><b><a href="http://192.168.2.203/pervasive2.php?sort=StoneColor1">StoneColor1</a></b></td>
    ';	
    while(odbc_fetch_row($rs)){ 
    	echo "<tr>\n";
    	for($i=1;$i<=$numfields;$i++){
    	   $fv=odbc_result($rs,$i);
    	   echo "<td>$fv</td>";
    	}   
    	echo "</tr>\n";
    } 
    echo "</table>\n";
    ?>
    i just want this to sort the RESULTS - is there a simple way to do that?

    TIA

  2. #2
    Join Date
    Apr 2005
    Posts
    30
    Not sure if I totally understand your problem but thought this may help.

    http://www.kryogenix.org/code/browser/sorttable/

    It's ajax based table sorting. Might be easy to do it this way?

    Good luck

  3. #3
    Join Date
    Mar 2008
    Posts
    10
    thank you for the help - that javascript is GREAT...and works perfectly.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •