Jump to content

SQLSRV sort table by table header


KelvinMorel

Recommended Posts

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

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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>
Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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>
Link to comment
Share on other sites

Archived

This topic is now archived and is closed to further replies.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.