How to Sort Table Columns with PHP and MySQL
This post will show you how to create a sample form which enable User to perform the tasks below.
- Select a Company Name
- List out all personnel associate to the Company Selected
- Sort the Column according to individual column
Files involve
1.This sample contains 1 Files (1)selectCompanySortPeople.php
Prior to that you need to complete the items listed Below
- Mamp or xampp with phpMyAdmin install into your Computer , check out here
- A Sample Database created Check out Here
- New User Created . Check out Here
- A New table created , check out Here
- Insert Data into the Table Here
selectCompanySortPeople.php
<?php
/*
* File: SelectCompanySortPeople
*/
$hostName ="localhost";
$userName ="jane";
$userPassword ="jane";
$database ="janedb";
$comArray = array();
$orderClause ='';
$tempcompany ='';
$dbConnectionStatus = new mysqli($hostName, $userName, $userPassword,$database);
//Connection Error
if ($dbConnectionStatus->connect_error){
die("Connection failed: " . $dbConnectionStatus->connect_error);
}
// Connected to Database JaneDB
// Object oriented -> pointing
if($dbConnectionStatus->query("SELECT DATABASE()")){
$dbSuccess =true;
//
$result = $dbConnectionStatus->query("SELECT DATABASE()");
$row = $result->fetch_row();
printf("Default database is %s.\n", $row[0]);
$result->close();
}
// DB Connect Successful
if ($dbSuccess) {
// Check whether The User Select any Company or the previous Company Name
// Pass into http and retrieved value by GET
if (isset($_POST["infoID"]) or isset($_GET["toSort"]) ){
// company selected by User
if (isset($_POST["infoID"])) {
$companyName = $_POST["infoID"];
$tempcompany = $_POST["infoID"];
$selectData = "SELECT * FROM testtable WHERE companyname = '$companyName' ORDER BY lastname ";
}
// Pass into http and retrieved value by GET
if (isset( $_GET["toSort"])) {
$orderClause = $_GET["toSort"];
$tempcompany = $_GET["tempID"];
$selectData = "SELECT * FROM testtable WHERE companyname = '".$tempcompany."' ORDER BY " .$orderClause;
}
// Send Select Query
$selectData_Query = mysqli_query($dbConnectionStatus,$selectData );
//--------------------------------------------------------------------------
// Declare Array
// Get all the User data and save that in an array
$arrayData = array();
while($rows=mysqli_fetch_assoc($selectData_Query)){
$arrayData[] = $rows;
}
//--------------------------------------------------------------------------
// Free Queries
mysqli_free_result($selectData_Query );
// Table Structure
//Table Header declaration
$header_ID = '<a href="selectCompanySortPeople.php?toSort=id&tempID='.$tempcompany.'">ID</a>';
$header_firstName = '<a href="selectCompanySortPeople.php?toSort=firstname&tempID='.$tempcompany.'">First Name</a>';
$header_lastName = '<a href="selectCompanySortPeople.php?toSort=lastname&tempID='.$tempcompany.'">Last Name</a>';
$header_Email = '<a href="selectCompanySortPeople.php?toSort=email&tempID='.$tempcompany.'">Email</a>';
echo '<div style=" font-family: arial, helvetica, sans-serif; ">';
echo '<div style="margin-left: 100; ">';
echo '<table border="1" padding="5">';
echo '<tr>
<td>'.$header_ID.'</td>
<td>'.$header_firstName.'</td>
<td>'.$header_lastName.'</td>
<td>'.$header_Email.'</td>
<td>company</td>
</tr> ';
// Output Data Row by row
foreach($arrayData as $data){
echo'<tr>';
// Search through the array print out value if see the Key eg: 'id', 'firstname ' etc.
echo'<td>'.$data['id'].'</td>';
echo'<td>'.$data['firstname'].'</td>';
echo'<td>'.$data['lastname'].'</td>';
echo'<td>'.$data['email'].'</td>';
echo'<td>'.$data['companyname'].'</td>';
echo'</tr>';
}
echo '</table>';
echo '</div>';
echo '</div>';
// END: Output section
}
else {
$selectDataOption = " SELECT * FROM testtable";
// Send Select Query
$selectDataOption_Query = mysqli_query($dbConnectionStatus,$selectDataOption);
echo '<form action="SelectCompanySortPeople.php" method="post">';
echo '<select name="infoID">';
echo '<option value="" label="" selected="selected">..select company..</option>';
//-------------------------------------------------------------------------------
if (mysqli_num_rows($selectDataOption_Query)>0){
// while there is still data in row
// assign selected mySQL data into 'arrayData' array
while($rowss=mysqli_fetch_assoc($selectDataOption_Query)){
// Before insert Check whether the value inside Array
$ID = $rowss['id'];
$companyNames =$rowss['companyname'];
$firstName =$rowss['firstname'];
// Loop Through array and Check
// If the Value is not inside the Array
if(! in_array($companyNames,$comArray)){
array_push($comArray,$companyNames);
echo '<option value="'. $companyNames.'">'.$companyNames.'</option>';
}
}
//Populate the Option
mysqli_free_result($selectDataOption_Query );
}
echo '</select>';
echo '<input type="submit" />';
echo '</form>';
// END
}
// END: if ($dbSuccess)
}
echo "<br /><hr /><br />";
echo '<a href="SelectCompanySortPeople.php">Select Another</a>';
echo ' ';
echo '<a href="/">Back to LocalHost</a><br />';
?>
