TUTO Création Tableau + Filtre autocomplétion [PHP]

Objective

A table with research filters is the most useful feature a table can have. For example, an Amazon user would need to sort out the articles that he is searching for, instead of navigate through thousands of pages. This tutorial will show how to implement simple filter text bars, as follows:

As you can see on the picture, there is text bars to search a specific value on any column. This is made to facilitate the research of something in a table. Once the user press ENTER, the table will be updated, and only the values that correspond to the text typed will be printed on the screen. We will explain you how does it works, and how to add the auto completion on your text inputs.

Setting up the page and functions

Let’s start with a simple HTML page : all we need is a table in our body, and a first button, that will be used to reset the filters. Your page should look something like this :

<!DOCTYPE HTML>
<html lang="fr">
    <head>
	    <meta charset="utf-8">
        <meta name="viewport" content="width=device-width, initial-scale=1, shrink-to-fit=no">
        <script src="dist/js/bootstrap.min.js"></script>
        <link href="https://code.jquery.com/ui/1.12.1/themes/smoothness/jquery-ui.min.css" rel="stylesheet" />
        <script src="https://ajax.googleapis.com/ajax/libs/jquery/3.4.1/jquery.min.js"></script>
        <script src="https://code.jquery.com/ui/1.12.1/jquery-ui.min.js"></script>
        <link href="css/bootstrap.min.css" rel="stylesheet">

        <title>BIG EYE</title>          
    </head>

    <body>
                 
        <table class="table table-bordered">    
			<tr>
               
            </tr>
        	
			<tr>
            
			</tr>

			

        </table>
  	</body>
</html>

I’ve added in the header some resources : the <script> tags with “jquery.min.js” and the <meta charset> tag are the most important resources you have to include to your HTML page, all the following code won’t work without it. Plus, the <table> tag have a class, but it’s only here on aesthetic purposes. You don’t have to give your table a class necessarily.

We’ll continue with another PHP page, that will contain our functions. We’ll use a class, because some of our variables need to be stocked somewhere. Here’s the basic declaration of a PHP class :

<?php
class utilisateur
{
    private $_bdd = null;
	private $_nom_table;
	
	public function __construct()
	{
        
	}
	
	public function getPDO()
	{
		return $this->_bdd;
	}
}
?>

Note that the <?php tag isn’t closed, you don’t need it if your page only have PHP code within. The __construct() function will be called right after the declaration of an user object. Now, let’s implement the first function of our class, that will set the connection to a database, which contain the data we want to display on our web page. Then, we’ll include our class in the main page, and call the function :

<?php
class utilisateur
{
    private this->_bdd;
    
    public function __construct()
	{
        
	}
	
	public function Connexion($SQL_DSN, $SQL_USERNAME, $SQL_PASSWORD)
	{
	    try
		{
			$this->_bdd = new PDO($SQL_DSN, $SQL_USERNAME, $SQL_PASSWORD);
		}

    	catch (Exception $e)
		{
			 die($e->getMessage());
		}
	}
?>

As a good class method, it needs to be reusable, so all the parameters of the new PDO() function are stocked in variables, which can be modified out of the class. We’re stocking the DataBase object in a private variable = $this->_DB.

<?php
    require('model.php');

    $SQL_DSN = "mysql:host=192.168.64.227;dbname=TPCamera";
    $SQL_USERNAME ="root";
    $SQL_PASSWORD = "root";
    
    $user = new utilisateur();
    $user->Connexion($SQL_DSN, $SQL_USERNAME, $SQL_PASSWORD);
?>

We are using require() instead of include() so if the file isn’t found, we’ll get an error. Next we use the define() function to place the parameters of the function in variables. Then, we’re creating an user object, and calling the function Connection().

In the class user : the method generateTab() permit to create header of table.

<?php
public function SetNomTable($nom_table)	//Attribue un nom de table pour la fonction generateTab()
	{
		$this->_nom_table = $nom_table;
	}
public function generateTab()	//Affiche le tableau, selon le nom de la table et le nom des colonnes spécifiées lors de l'appel de la fonction
	{
		//La fonction n'a pas de paramètres, car le nombre de paramètres est variable, chaque tableau n'a pas le même nombre de colonnes.
		foreach(func_get_args() as $value)	//On peut récupérer les paramètres grâce à func_get_args(), et on crée une case de tableau pour chaque colonne qu'on a en base de donné
		{
			//on crée un en tête de tableau avec un champ pour l'ecriture 
			//la fonction onkeydown permet a l'appuie sur la touche entré d'appliquer le filtre sur le tableau
			//la fonction autocomplete de jQuery permet de proposer une liste de mot contenue dans source :
			echo '<th>
			<form method="GET"><input type="hidden" name="champ" value="'.$value.'">
				<p>'.$value.'
					<input name="recherche" type="text" id="recherche'.$value.'" placeholder="recherche" onkeydown="if(keyCode==13){this.form.submit();return false;}"/>
				</p>
			</form> 
			<script> 
				$("#recherche'.$value.'").autocomplete({source: '.$this->filter($value, $this->_nom_table).'}); 
			</script>
		</th>';	
		}
	}
?>

The column as create for each case you write when you call the function in you page, it’s the job of func_get_args().

This header has Title of column, one field to write filter and if you field filter have same word the javaScript function autocomplete offers the end of this word if this word is in DB.

Filter Method: The filter method permits to refresh one table when you type the filter.

<?php
    public function filter($nom_colonne,$nom_table)	//Fetch in DB all the elements of a column that contain the word specified in the filter field
	{
		//$_GET['term'] is a global value, which designates any variable in the array $_GET
        if(!isset ($_GET['term']))	//So if there is no $_GET variable, it means that we have no filter entered
        {
            $_GET['term'] = ""; 
        }
		
		$term = $_GET['term'];

		//We prepare SQL request who selected in the good column in the good array all the values who match with who is register in field
    	$requete = $this->_bdd->prepare('SELECT DISTINCT '.$nom_colonne.' FROM '.$nom_table.' WHERE '.$nom_colonne.' LIKE :term ORDER BY '.$nom_colonne.' ASC');

		//We execute the request by replacing ":term" by the value register in the field
		$requete->execute(array('term' => '%'.$term.'%'));

		$array = array();

		//We retrieved in $donnee the result of request
		while($donnee = $requete->fetch())
		{
			array_push($array, $donnee[$nom_colonne]);	//For each value we have retrieved, we include it in the array $array
		}

		return $source = json_encode($array);	//We return the PHP array got in a JSON variable, because this value is used in a script
	}
?>

Filter method take two case’s Name of column and name of table.

First filter takes $_GET[‘term’] it’s the generic values for $_GET array

After SQL request are prepare this name of table and name of column and the order the result by alphabetic order.

Execute SQL request with :term = the word stocked in the $term

You use array_push to add all you data found in your array. Finally you use json_encode with you $array because our array is used in json function to return all possibilities of you autocomplete function

For the last part you need to call your fonction in your page table.php with this.

<?php
    // Donnée le nom de la table a afficher
    $nom_table = "HistoCommande";

    // Requete SQL du filtre et activation du bouton défiltré
    if (!isset($_GET['champ'])){
        $filtre_sup="";
        $actif= " disabled";
    }
    else{
        $filtre_sup=" WHERE ".$_GET['champ']." LIKE '".$_GET['recherche']."'";
        $actif= ""; 
    }

    $sql = 'SELECT * FROM '.$nom_table.''.$filtre_sup.'';   
    if (!is_null($user) && !is_null( $user->getPDO())){
        $req = $user->getPDO()->query($sql);
    } else{
        echo "Probleme connextion BDD";
    }
?>

Put in $nom_table your SQL table name. The second part of this picture permit to add a bonus condition to the $sql request, in casse the user has specified something in one of the text fields.

<!-- Bouton défiltré -->
<form action="tableau.php"><button class="btn btn-primary" role="button"<?php echo $actif;?>>Défiltrer</button></form>

<!-- Création du tableau -->
<table class="table table-bordered">    
<tr>
    <?php
    // On remplace la variable de classe nom_table 
    $user->SetNomTable($nom_table);
    // On génére la tableau
	$user->generateTab('ID','User','Commande','Heure');
	?>	
</tr>

<tr>
<?php
    // Affichage du tableau remplacé $row['***']  par les entete du tableau à afficher 
    while($row = $req->fetch()) { ?>
	<td><?php echo $row['ID']; ?></td>
	<td><?php echo $row['User']; ?></td>
	<td><?php echo $row['Commande']; ?></td>
	<td><?php echo date("H:i:s", strtotime($row['Heure'])); ?></td>
</tr>

<?php }    
$req->closeCursor(); 
?>

Finally, you just have to call the $user class methods, setNomTable() and generateTab(), with the right parameters. You also need to make a while loop to display, line after line, all the values returned by your function.

Don’t forget to place a Defilter button, that will just reload the page, like shown in the picture just above

ENJOY 🙃

Laisser un commentaire

Votre adresse de messagerie ne sera pas publiée. Les champs obligatoires sont indiqués avec *

+