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.

1. Select a Company Name
2. List out all personnel associate to the Company Selected
3. 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

1. Mamp or xampp with phpMyAdmin install into your Computer , check out here
2.  A Sample Database created Check out Here
3.  New User Created . Check out Here
4. A New table created , check out Here
5. Insert Data into the Table Here

Reference

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 '&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;';
echo '<a href="/">Back to LocalHost</a><br />';



?>