SPARQL Protocol and RDF Query Language/Travail pratique/PHP avec MySQL

Après ce TP, vous saurez :
établir une connexion à une base de données MySQL modifier des données afficher des données rechercher des données
Durée : 1,5 heure
Matériel :
un serveur Web Linux avec PHP.

**MySQL**
Leçon : SPARQL Protocol and RDF Query Language

T.P. n^o 12

TP de niveau 15.
Précédent :	PHP les fonctions
Suivant :	PHP avec SPARQL

En raison de limitations techniques, la typographie souhaitable du titre, « Travail pratique : MySQL
SPARQL Protocol and RDF Query Language/Travail pratique/PHP avec MySQL », n'a pu être restituée correctement ci-dessus.

Introduction

Dans ce TP, nous allons enregistrer nos CV dans la base de données MySQL que nous avons créée dans le TP "TP Initiation à MySQL".

Ce TP a pour objectif de comprendre comment une base de données est utilisée par un langage comme PHP, pour pouvoir appliquer ensuite cette méthode avec SPARQL.

À savoir avant de commencer

Pour rappel:

Le code HTML ne sert qu’à l'affichage.
Le code PHP sert à créer un HTML dynamique.

Cependant, PHP n'a pas le "pouvoir" de mémoriser des données entre deux chargements de page. À moins que vous transmettiez des données d'une page Web à l'autre, via un formulaire HTML !

Si vous voulez conserver sur la durée les données entrées par les visiteurs de votre site via votre formulaire, de manière automatique et sans plus vous occuper de rien, vous devrez utiliser une base de données.

TP Base de données

De même que PHP vous permet de créer un langage HTML plus dynamique et personnalisé, il permet aussi de créer vos requêtes SQL de façon à automatiser l'alimentation de vos bases de données.

Pour le démontrer, nous allons créer un formulaire qui permettra de stocker les CV dans votre base de données.

Connexion

Pour utiliser une base de données, il faut se connecter à celle-ci. Chaque base de données SQL a sa propre méthode de connexion. Ici, nous n'allons étudier que la méthode avec une base de données MySQL.

Configurer la connexion

On met les paramètres de connexion dans une fonction, dans notre fichier fonctions.php par exemple :

<?php
function connectMaBase(){
     $base = mysql_connect ('localhost', 'root', 'VOTRE_MOT_DE_PASSE');
     mysql_select_db ('moncv', $base) ;
}
?>

la variable $base contient l’objet de connexion à votre base de données (BDD) avec:
- le nom du serveur (ici localhost ou 127.0.0.1 c’est équivalents),
- le nom de l’utilisateur (ici root qui signifie administrateur de votre BDD)
- remplacer le texte VOTRE_MOT_DE_PASSE par votre mot de passe.
La ligne suivante lance la commande de sélection avec le nom de votre BDD et l’objet de connexion...

Ouvrir la connexion

Pour appeler la fonction, il vous faudra :

dans le fichier form.php : écrire un include du fichier fonctions.php
placer la fonction à l'endroit opportun avec la ligne suivante :

<?php
connectMaBase();

Fermer la connexion

Quand on rédige une commande SQL via PHP, on procède ainsi:

on se connecte à la base (en utilisant notre fonction de connexion)
on prépare la commande SQL en la stockant dans la variable $sql
on l'exécute
on ferme la connexion avec la fonction PHP suivante

<?php
// on ferme la connexion
mysql_close();
?>

Insérer des données

Dans le fichier php, pour faire une insertion, on écrira par exemple :

<?php
//On récupère les valeurs entrées par l'utilisateur :
$pseudo=$_POST['pseudo'];
$age=$_POST['age'];
$sexe=$_POST['sexe'];
//On crée une variable date du jour grâce à la fonction date() de PHP
$today = date("d.m.y");
//On se connecte
connectMaBase();
//On prépare la commande sql d'insertion
$sql = 'INSERT INTO Utilisateurs VALUES("","'.$pseudo.'","'.$sexe.'","'.$age.'","'.$today.'")';
/*on lance la commande (mysql_query) et au cas où,
on rédige un petit message d'erreur si la requête ne passe pas (or die)
(Message qui affichera les causes d'erreur sql)*/
mysql_query ($sql) or die ('Erreur SQL !'.$sql.'<br />'.mysql_error());
// on ferme la connexion
mysql_close();
?>

La partie la plus complexe pour vous est probablement la création de la requête SQL :

<?php
	//On prépare la commande sql d'insertion
	$sql = 'INSERT INTO Utilisateurs 					 VALUES(NULL,"'.$pseudo.'","'.$sexe.'","'.$age.'","'.$today.'")';
?>

La première valeur est laissée vide, car c’est la place de l'ID qui doit s'auto-incrémenter.
- Si vous l'oubliez, vous aurez un message d'erreur SQL indiquant que le nombre de données insérées ne coïncide pas avec le nombre de champs.
Les autres définitions de valeurs de type texte sont construites à l'aide de variables PHP, d'où l’utilisation de guillemets simples (comme pour le HTML).

Exercice : SANS COPIER COLLER, vous allez :

Reprendre votre projet Eclipse pour que le CV soit enregistré dans votre nouvelle BDD.
Pour récupérer l’ID généré après un insert SQL, utiliser la fonction : mysql_insert_id()
N’oubliez pas de tester après chaque ajout dans le code.
Si vous ne trouvez pas l’erreur, utilisez les commentaires et le débuggueur.

Solution

Avec la nouvelle fonction connectMaBase, voici le fichier fonctions.php :

<?php 
function connectMaBase(){
	$base = mysql_connect ('localhost', 'testuser', 'mSBGJGxVDv7SmzQV');
	mysql_select_db ('moncv', $base) ;
}

function createArrayExperiences($arrayExperiences){
	//Crée le tableau où chaque ligne sera une expérience
	$nbExperience = count($arrayExperiences['poste']);
	$newArray = array();
	for($i = 0; $i < $nbExperience; $i++){
		$newExperience = array();
		foreach($arrayExperiences as $index=>$subarray){	
			if ($index!='valider'){
				$newExperience[$index] = $subarray[$i];
			}			
		}
		$newArray[] = $newExperience;
	}
	return $newArray;
}

function afficheExperiences($arrayExperiences){
	//Affiche les expériences
	foreach ($arrayExperiences 	as $experience){
		afficheExperience($experience);
	}
}

function afficheExperience($arrayExperience){
	//print_r($arrayExperience);
		
	if ($arrayExperience['poste_actuel'] == 'on'){
		echo '<b>Depuis le '.$arrayExperience['date_debut'].'</b>';
	}else{
		echo '<b>Du '.$arrayExperience['date_debut'].' au '.$arrayExperience['date_fin'].'</b>';
	}
	echo ' : ';
	echo '<b>'.$arrayExperience['poste'].' ('.$arrayExperience['societe'].')</b><br />';
	echo '<b>'.nl2br($arrayExperience['description']).'</b><br />';
	echo 'Mots clés : <b>'. implode(', ',explode(' ',$arrayExperience['mots_cles'])) .'</b><br />';
}

Et voici les modifications du fichier monCV.php pour l'enregistrement des CVs :

<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head><title>Mon CV</title></head>
<body>
<?php
	include('functions.php');
	
	//Initialisation des variables
	if(isset($_POST['valider'])){
		$nom = $_POST['nom']; 
		$prenom = $_POST['prenom']; 
		$adresse = $_POST['adresse']; 
		$code_postal = $_POST['code_postal']; 
		$ville = $_POST['ville']; 
		$telephone = $_POST['telephone']; 
		
		$arrayExperiences = array();
		$arrayExperiences['date_debut'] = $_POST['date_debut']; 
		$arrayExperiences['date_fin'] = $_POST['date_fin']; 
		$arrayExperiences['poste'] = $_POST['poste']; 
		$arrayExperiences['societe'] = $_POST['societe']; 
		$arrayExperiences['description'] = $_POST['description']; 
		$arrayExperiences['mots_cles'] = $_POST['mots_cles']; 
		$arrayExperiences['poste_actuel'] = $_POST['poste_actuel']; 
		
		
		// On crée le tableau d'expériences
		$experiences = createArrayExperiences($arrayExperiences);

		//On se connecte
		connectMaBase();
		//On prépare la commande sql d'insertion pour la table CVs
		$sql = 'INSERT INTO `CV`.`CVs` (`ID`, `nom`, `prenom`, `adresse`, `code_postal`, `ville`, `telephone`)
		 			VALUES (NULL, "'.$nom.'", "'.$prenom.'", "'.$adresse.'", "'.$code_postal.'", "'.$ville.'", "'.$telephone.'")';

		/*on lance la commande (mysql_query) et au cas où,
		on rédige un petit message d'erreur si la requête ne passe pas (or die)
		(Message qui intégrera les causes d'erreur sql)*/
		mysql_query ($sql) or die ('Erreur SQL !'.$sql.'<br />'.mysql_error());
		
		//récupère la clé du CV pour l'associer aux expériences
		$last_CV_ID = mysql_insert_id();
		
		foreach ($experiences 	as $experience){
			$posteactuel = $experience['poste_actuel'] == 'on';
			//On prépare la commande sql d'insertion pour la table Experiences
				$sql = 'INSERT INTO `CV`.`Experiences` (`ID`, `ID_CV`, `date_debut`, `date_fin`, `poste`, `societe`, `description`, `mots_cles`, `poste_actuel`) 
				VALUES (NULL, "'.$last_CV_ID.'", "'.$experience['date_debut'].'", "'.$experience['date_fin'].'", "'.$experience['poste'].'", "'.$experience['societe'].'","'.$experience['description'].'", "'.$experience['mots_cles'].'", "'.$posteactuel.'")';
		
				/*on lance la commande (mysql_query) */
				mysql_query ($sql) or die ('Erreur SQL !'.$sql.'<br />'.mysql_error());
		}		
		
		// on ferme la connexion
		mysql_close();
	}
	//affichage
?>
<b><?php echo $nom.' '.$prenom; ?></b><br />
<b><?php echo $adresse; ?></b><br />
<b><?php echo $code_postal.' '.$ville; ?></b><br />
Tel : <b><?php echo $telephone; ?></b><br>
Expériences : <br />
<hr/>
<?php
//print_r($arrayExperiences);
afficheExperiences($experiences);
 ?>
</body>
</html>

Afficher des données

On a déjà utilisé la syntaxe SQL d'une simple requête pour récupérer des données dans un TP précédent : pour récupérer tous les CV concernant Toto par exemple.

SELECT * FROM CVs WHERE nom= "Toto"

Pour chaque CV, PHP va nous envoyer plusieurs plusieurs informations (l'ID, le prénom, l’adresse...). Chaque enregistrement récupéré de la BDD sera utilisable sous la forme d'un tableau associatif.

On va utiliser la fonction suivante pour récupérer ce tableau :

mysql_fetch_array($TrucRecupereSurSql)

On récupère ainsi un tableau associatif qui a pour indice : le nom de chaque champ.

Voici la méthode pour afficher des données depuis une base de données :

On se connecte à la base (en utilisant notre fonction de connexion).
On prépare la commande SQL en la stockant dans la variable $sql.
On exécute la requête, en récupérant le résultat dans une variable que nous appellerons $req.
Si c’est un tableau : on parcourt $req avec une boucle while (car on ne sait pas toujours le nombre de champs, ni d'enregistrements envoyés par notre base de données)
- à chaque itération, la fonction mysql_fetch_array($req) instancie la variable $data
- chaque champ de l'enregistrement est accessible via la commande : $data['champ']
Maintenant qu'on a tout récupéré dans des variables PHP, on libère la mémoire de MySQL mobilisée par cette requête.
On ferme la connexion SQL.

Exemple :

<?php
include("fonctions.php");
?>
<html>
	<body>
	<?php
	connectMaBase(); //On se connecte
	$sql = 'SELECT * FROM utilisateurs WHERE sexe="F"'; // On prépare la requête
	// On lance la requête (mysql_query) et on impose un message d'erreur si la requête ne se passe pas (or die)
	$req = mysql_query($sql) or die('Erreur SQL !<br />'.$sql.'<br />'.mysql_error());
	//on organise $req en tableau associatif $data['champ']en scannant chaque enregistrement récupéré
	echo'<h2>TOUTES LES FILLES INSCRITES :</h2>';
	while ($data = mysql_fetch_array($req)) { //boucle
		// on affiche les résultats
		echo 'Pseudo : <strong>‘. $data['Pseudo'] .'</strong><br />';
		echo 'Son âge : '.$data['Age'].'<br />';
		echo 'Sa date d\'inscription : '.$data['DateInscription'].'<br />';
	}
	//On libère la mémoire mobilisée pour cette requête dans sql $data de PHP lui est toujours accessible !
	mysql_free_result ($req);
	//On ferme sql
	mysql_close ();
	?>
	</body>
</html>

Rechercher des données

Nous avons un formulaire qui enregistre des CV et une page qui peut les afficher. Il nous faut maintenant une page pour retrouver des CV. Cette page est un formulaire de recherche ; ce formulaire peut contenir un ou plusieurs critères de recherche. Un critère peut être la mobilité d'un candidat, son niveau en langue ou encore son expérience.

Pour notre TP, nous allons laisser à l'utilisateur le choix de son critère de recherche : l'utilisateur entrera son critère dans un formulaire. C'est une option peu ergonomique, car cela peut troubler les utilisateurs non informaticiens. Cependant, cela sera un bon exemple pour l’utilisation de SQL.

Cela signifie que l’on veut obtenir une variable $sql du type :

$sql = 'SELECT * FROM utilisateurs WHERE '.$champ.' ="'.$critere.'"';

Il faut pouvoir proposer un formulaire où l'utilisateur entrera son choix de champ et la valeur attendue...
Il faut donc construire la liste de champs sous forme de liste déroulante en lisant le contenu de la BDD une première fois, car l’utilisateur n’est pas censé connaître le contenu de la BDD.

<!-- Commentaires HTML
On construit une liste déroulante ( un select et plusieurs options)
Chaque option sera remplie par une donnée récupérée par notre requête SQL
-->
<form method="post" action="admin.php">
	<select name="champ">
	<?php
		connectMaBase(); //On se connecte
		$sql = 'Show fields from utilisateurs'; //On prépare la requête SQL qui récupère les champs
		/* On lance la requête (mysql_query) et on impose un message d'erreur si la requête ne passe pas (or die) */
		$req = mysql_query($sql) or die('Erreur SQL !<br />'.$sql.'<br />'.mysql_error());
		//On scanne le résultat et on construit chaque option avec
		while($data = mysql_fetch_array($req)){
			echo '<option name="'.$data['Field'].'">'.$data['Field'].'</option>'; // on affiche chaque champ
		}
		mysql_free_result ($req); //On libère mysql de cette première requête
	?>
	<!-- On ferme le select -->
	</select>
	Entrez votre critère de sélection sur ce champ : <input type="text" name="critere"/>
	<input type="submit" name="Valider" value="OK"/>
</form>
<!-- On ferme le formulaire -->

Exercice : Vous allez maintenant créer une page pour rechercher un CV. Dans une page PHP index.php, vous allez créer le formulaire :

Rechercher dans [liste champs]|V| [________] [Chercher]

Créez le formulaire ci-dessus, dont voici la description :
- [liste champs]|V| est un champ Select qui contient la liste des champs de la table Expériences.
- [________] est un champ texte
- [Chercher] est un bouton pour lancer la recherche
Vous afficherez les résultats en dessous du formulaire sous forme de lien pour afficher le CV correspondant
- Pour faire une recherche dans un texte, vous devrez utiliser non pas une égalité en SQL, mais la fonction LIKE comme ci-dessous :

$sql = 'SELECT * FROM Experiences WHERE '.$champ. ' LIKE "%'.$critere. '%"';

Solution

Voici un exemple du fichier index.php que vous devez avoir développé :

<html>
<head>
<title>Mon CV</title>
</head>
	<body>
		<a href="formMonCV.html">Créer un nouveau CV</a><br />
		<h2>Chercher un CV :</h2>
		<form method="post" action="index.php">
Rechercher dans 
<select name="champ">
<?php
include('functions.php');
connectMaBase(); //On se connecte
$sql = 'Show fields from Experiences'; //On prépare la requête SQL qui récupère les champs
/* On lance la requête (mysql_query) et on impose un message d'erreur si la requête ne passe pas (or die) */
$req = mysql_query($sql) or die('Erreur SQL !<br />'.$sql.'<br />'.mysql_error());
//On scanne le résultat et on construit chaque option avec
while($data = mysql_fetch_array($req)){
	if(! preg_match("/ID/",$data['Field']) )
		echo '<option name="'.$data['Field'].'">'.$data['Field'].'</option>'; // on affiche chaque champ
}
mysql_free_result ($req); //On libère mysql de cette première requête


?>
<!-- On ferme le select -->
</select>
<input type="text" name="critere"/> 
<input type="submit" name="valider" value="Chercher"/>
 <br />
</form>

<?php
	
	//Initialisation des variables
	if(isset($_POST['valider']) && $_POST['critere'] != ''){
		$champ = $_POST['champ']; 
		$critere = $_POST['critere']; 		
		$sql = 'SELECT DISTINCT CVs.ID, nom, prenom FROM CVs, Experiences WHERE CVs.ID = Experiences.ID_CV AND Experiences.'.$champ.' LIKE "%'.$critere.'%"';
		
		$req = mysql_query($sql) or die('Erreur SQL !<br />'.$sql.'<br />'.mysql_error());
		
		//On scanne le résultat et on construit la liste des candidats
		echo 'Liste des candidats :<br />';
		while($data = mysql_fetch_array($req)){
			echo '<a href="javascript:alert(\'Faire : Afficher un CV !\')">'.$data['ID'].' '.$data['nom'].' '.$data['prenom'].'</a><br />';
		}		
		
		mysql_free_result ($req); //On libère mysql de cette première requête
	}
	
//On se deconnecte
mysql_close();
?>


	</body>
</html>

TP SPARQL

PHP les fonctions

PHP avec SPARQL