SQLSRV sort table by table header

KelvinMorel · July 23, 2017

For days now I'm trying to add some functionality to my table, driven from SQLSRV to PHP, in this case I would like to be able to by clicking on a table header the table get sorted by the header just clicked, like by job, by firstname, etc... I found some good tutorials but only pointing to MySQL codes [$_GET], tried several ways but couldn't get it to work.

Here under my code I'm using to test, the production code is almost the same but pointing to another server and database. I've attached a working MySQL/PHP template.

Maybe can someone help me out.

<html>
<head>
<title>PHP & SQL Server (sqlsrv)</title>
</head>
</head>
<body>
<div class="table-title">
    <h3>Test table</h3>
<?php
   ini_set('display_errors', 1);
   error_reporting(~0);

    $serverName = "localhost";
    $userName = "username";
    $userPassword = "password";
    $dbName = "AdWorks";

   $connectionInfo = array("Database"=>$dbName, "UID"=>$userName, "PWD"=>$userPassword, "MultipleActiveResultSets"=>true);

   $conn = sqlsrv_connect( $serverName, $connectionInfo);

   if( $conn === false ) {
      die( print_r( sqlsrv_errors(), true));
   }

?>
<form action="list.php" method="POST">
<select name="RegSelect"><option> Choose </option>
<?php

$sql = "SELECT DISTINCT CountryRegionName FROM dbTest ORDER BY CountryRegionName";
$resultRg = sqlsrv_query($conn,$sql) or die("Couldn't execut query");
while ($data=sqlsrv_fetch_array($resultRg, SQLSRV_FETCH_ASSOC)){
    echo '<option value="'.$data['CountryRegionName'].'">';
    echo $data['CountryRegionName']; 
    echo "</option>";
}

if(empty($_POST['RegSelect'])){
    $_SESSION['tower'] = '';
} else {
    $stmt = "SELECT BusinessEntityID, FirstName, LastName, JobTitle, PhoneNumber, PhoneNumberType, EmailAddress, AddressLine1, City, PostalCode, CountryRegionName, Status FROM dbTest WHERE CountryRegionName = '".$_POST['RegSelect']."'";

    $qry = sqlsrv_query($conn, $stmt);
}
?>
<input type="submit" value="Select Tower">
</select></br></br>
</form>
<table align="center">
  <tr>
    <th width="91"> <div align="center">BusinessEntityID </div></th>
    <th width="98"> <div align="center">FirstName </div></th>
    <th width="198"> <div align="center">LastName </div></th>
    <th width="97"> <div align="center">JobTitle </div></th>
    <th width="59"> <div align="center">PhoneNumber </div></th>
    <th width="71"> <div align="center">PhoneNumberType </div></th>
    <th width="30"> <div align="center">EmailAddress </div></th>
    <th width="30"> <div align="center">AddressLine1 </div></th>
    <th width="30"> <div align="center">City </div></th>
    <th width="30"> <div align="center">PostalCode </div></th>
    <th width="30"> <div align="center">CountryRegionName </div></th>
    <th width="30"> <div align="center">Status </div></a></th>
    <th width="30"> <div align="center">Edit </div></th>
  </tr>
 <tbody class="rows">
<?php

while($result = sqlsrv_fetch_array($qry, SQLSRV_FETCH_ASSOC)){
    //print_r($result);
?>
  <tr>
    <td><div align="center"><?=$result["BusinessEntityID"];?></div></td>
    <td><div align="center"><?=$result["FirstName"];?></div></td>
    <td><?=$result["LastName"];?></td>
    <td align="right"><?=$result["JobTitle"];?></td>
    <td align="right"><?=$result["PhoneNumber"];?></td>
    <td align="right"><?=$result["PhoneNumberType"];?></td>
    <td align="right"><?=$result["EmailAddress"];?></td>
    <td align="right"><?=$result["AddressLine1"];?></td>
    <td align="right"><?=$result["City"];?></td>
    <td align="right"><?=$result["PostalCode"];?></td>
    <td align="right"><?=$result["CountryRegionName"];?></td>
    <td align="right"><?=$result["Status"];?></td>
    <td align="center"><a href="edit_bck.php?BusinessEntityID=<?php echo $result["BusinessEntityID"];?>">Edit </a></td>
  </tr>
<?php
}
?>
</table>
<?php
sqlsrv_close($conn);
?>
</body>
</html>

test.php

Jacques1 · July 23, 2017

Your code has SQL injection and cross-site scripting vulnerabilities all over the place, so that's the first thing you need to take care of. I also suggest you switch from the SQLSRV functions to PDO, because this is a far more programmer-friendly API (read: you are less likely to fuck up), and it works with all mainstream database systems, not just the Microsoft ones.

When that's done, do some research about table sorting plug-ins (like this one) and try them out.

KelvinMorel · July 23, 2017

Hi Guru,

Thx, I was not using secure PDO string because this is something to be used into an global secure intranet connection but just modified my code to PDO.

I'll try the plug-in.

<html>
<head>
<title>PHP & SQL Server (sqlsrv)</title>
</head>
</head><body>
<div class="table-title">
<h3>TEST PAGE</h3>
<?php
   ini_set('display_errors', 1);
   error_reporting(~0);

$serverName = "servername";
    $database = "AdWorks";
    $uid = 'username';
    $pwd = 'password';
    try {
        $conn = new PDO(
            "sqlsrv:server=$serverName;Database=$database",
            $uid,
            $pwd,
            array(
                //PDO::ATTR_PERSISTENT => true,
                PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION
            )
        );
    }
    catch(PDOException $e) {
        die("Error connecting to SQL Server: " . $e->getMessage());
    }
?> 
<form action="list.php" method="POST">
<select name="RegSelect"><option> Choose </option>
<?php
$query1 = "SELECT DISTINCT CountryRegionName FROM someDB ORDER BY CountryRegionName";
$stmt1 = $conn->query( $query1 );
    while ($data = $stmt1->fetch(PDO::FETCH_ASSOC)){
        echo '<option value="'.$data['CountryRegionName'].'">';
echo $data['CountryRegionName']; 
echo "</option>";
    }
if(empty($_POST['RegSelect'])){
    $_SESSION['tower'] = '';
} else {
$query2 = "SELECT BusinessEntityID, FirstName, LastName, JobTitle, PhoneNumber, PhoneNumberType, EmailAddress, AddressLine1, City, PostalCode, CountryRegionName, Status FROM someDB WHERE CountryRegionName = '".$_POST['RegSelect']."' ORDER BY JobTitle ASC";

$stmt2 = $conn->query( $query2 );
}
?>
<input type="submit" value="Select Tower">
</select></br></br>
</form>
<table align="center">
  <tr>
    <th width="91"> <div align="center">BusinessEntityID </div></th>
    <th width="98"> <div align="center">Fist Name </div></th>
    <th width="198"> <div align="center">Last Name </div></th>
    <th width="97"> <div align="center">Job Title </div></th>
    <th width="59"> <div align="center">Phone Number </div></th>
    <th width="71"> <div align="center">Phone NumberType </div></th>
    <th width="30"> <div align="center">Email Address </div></th>
<th width="30"> <div align="center">Address Line1 </div></th>
<th width="30"> <div align="center">City </div></th>
<th width="30"> <div align="center">Postal Code </div></th>
<th width="30"> <div align="center">Country RegionName </div></th>
<th width="30"> <div align="center">Status </div></th>
<th width="30"> <div align="center">Edit </div></th>
  </tr>
 <tbody class="rows">
<?php
    while ($result = $stmt2->fetch(PDO::FETCH_ASSOC)){
?>
  <tr>
    <td><div align="center"><?=$result["BusinessEntityID"];?></div></td>
    <td><div align="center"><?=$result["FirstName"];?></div></td>
    <td><?=$result["LastName"];?></td>
    <td align="right"><?=$result["JobTitle"];?></td>
    <td align="right"><?=$result["PhoneNumber"];?></td>
    <td align="right"><?=$result["PhoneNumberType"];?></td>
<td align="right"><?=$result["EmailAddress"];?></td>
<td align="right"><?=$result["AddressLine1"];?></td>
<td align="right"><?=$result["City"];?></td>
<td align="right"><?=$result["PostalCode"];?></td>
<td align="right"><?=$result["CountryRegionName"];?></td>
<td align="right"><?=$result["Status"];?></td>
<td align="center"><a href="edit_bck.php?BusinessEntityID=<?php echo $result["BusinessEntityID"];?>">Edit </a></td>
  </tr>
<?php
}
?>
</table>
</body>
</html>

Edited July 23, 2017 by KelvinMorel

Jacques1 · July 23, 2017

I was not using secure PDO string because this is something to be used into an global secure intranet connection

This is a fundamental misunderstanding.

Your query code is simply defective and can blow up at any time even when nobody has any bad intentions. If you don't know what I mean, try to insert the string "O'Reilly" and watch the query crash. I'm sure we agree this is a problem.

Secondly, it's naive to assume that intranets are somehow immune to attacks. PCs can get infected and spread malware over internal networks. Not everybody with intranet access deserves infinite trust. And sometimes people are just stupid.

So before you do anything, I strongly recommend you rethink your approach to programming and review the code you've already written. If the entire application is based on the assumption that everybody is nice, nobody ever makes a mistake and input is always alphanumeric, you have bigger problems than sorting a table.

KelvinMorel · July 23, 2017

Hei Guru,

You're totally right, for me it's the first time ever I play with SQLSRV/PHP, I do have some slight notions of MySQL, but really limited.

My apologize to ask this but do you think I should change the code with PDO functions?

Jacques1 · July 23, 2017

Yes, you should change the code. Learn how to use PDO correctly, in particular how to safely pass PHP values to queries through prepared statements. Also learn when and how to apply HTML-escaping.

Never trust user input. Always expect invalid or even malicious data. This applies to the Internet, an intranet and even local applications. All of them must behave reasonably under all circumstances.

KelvinMorel · July 24, 2017

Thx very much Jacques!

KelvinMorel · July 24, 2017

Done, found the way to do it with JQuery tablesorter

<html>
<head>
<title>SQL Server (sqlsrv)</title>
<link rel="stylesheet" href="jquery/themes/blue/style.css" type="text/css" media="print, projection, screen" />
<script type="text/javascript" src="jquery/jquery-3.2.1.js"></script> 
<script type="text/javascript" src="jquery/jquery.tablesorter.js"></script>
<script type="text/javascript" src="jquery/jquery.tablesorter.widgets.js"></script>

<script type="text/javascript">
$(document).ready(function() 
    { 
        $("#myTable").tablesorter(); 
    } 
);
</script>
</head>
<body>
<div class="table-title">
	<h3>DB List</h3>
<?php
   ini_set('display_errors', 1);
   error_reporting(~0);
   
   include('connect.php');
?> 
<form action="list.php" method="POST">
<select name="RegSelect"><option> Choose </option>
<?php
	$query1 = "SELECT DISTINCT CountryRegionName FROM someDB ORDER BY CountryRegionName";
	$stmt1 = $conn->query( $query1 );
    while ($data = $stmt1->fetch(PDO::FETCH_ASSOC)){
        echo '<option value="'.$data['CountryRegionName'].'">';
		echo $data['CountryRegionName']; 
		echo "</option>";
    }
if(empty($_POST['RegSelect'])){
    $_SESSION['tower'] = '';
} else {
	$query2 = "SELECT BusinessEntityID, FirstName, LastName, JobTitle, PhoneNumber, PhoneNumberType, EmailAddress, AddressLine1, City, PostalCode, CountryRegionName, Status FROM someDB WHERE CountryRegionName = '".$_POST['RegSelect']."' ORDER BY JobTitle ASC";
	
	$stmt2 = $conn->query( $query2 );
}
?>
<input type="submit" value="Select Tower">
</select></br></br>
</form>
<table id="myTable" class="tablesorter" align="center">
<thead>
  <tr>
    <th width="91">Entity ID</th>
    <th width="98">Fist Name</th>
    <th width="198">Last Name</th>
    <th width="97">Job Title</th>
    <th width="59">Phone Number</th>
    <th width="71">Phone Type</th>
    <th width="30">Email Address</th>
	<th width="30">Address</th>
	<th width="30">City</th>
	<th width="30">Postal Code</th>
	<th width="30">Region Name</th>
	<th width="30">Status</th>
	<th width="30">Edit</th>
  </tr>
<thead>
<tbody>
<?php
	if(empty($stmt2)){
		echo '';
	}else{
		while ($result = $stmt2->fetch(PDO::FETCH_ASSOC)){
?>
	<tr>
		<td><?=$result["BusinessEntityID"];?></td>
		<td><?=$result["FirstName"];?></td>
		<td><?=$result["LastName"];?></td>
		<td><?=$result["JobTitle"];?></td>
		<td><?=$result["PhoneNumber"];?></td>
		<td><?=$result["PhoneNumberType"];?></td>
		<td><?=$result["EmailAddress"];?></td>
		<td><?=$result["AddressLine1"];?></td>
		<td><?=$result["City"];?></td>
		<td><?=$result["PostalCode"];?></td>
		<td><?=$result["CountryRegionName"];?></td>
		<td><?=$result["Status"];?></td>
		<td><a href="edit_bck.php?BusinessEntityID=<?php echo $result["BusinessEntityID"];?>">Edit </a></td>
	</tr>
<?php
		}
	}
?>
</tbody>
</table>
</body>
</html>

Edited July 24, 2017 by KelvinMorel

Sign In

SQLSRV sort table by table header

Recommended Posts

KelvinMorel

Link to comment

Share on other sites

Jacques1

Link to comment

Share on other sites

KelvinMorel

Link to comment

Share on other sites

Jacques1

Link to comment

Share on other sites

KelvinMorel

Link to comment

Share on other sites

Jacques1

Link to comment

Share on other sites

KelvinMorel

Link to comment

Share on other sites

KelvinMorel

Link to comment

Share on other sites

Join the conversation

Browse

Activity

Important Information