je viens vers vous à fin que vous m'aidez à trouver une piste pour apprendre l'exportation de recherche SQL vers Excel via PHP
voila mon formulaire de recherche :
Résultat après recherche :
Code PHP de recherche est le suivant :
Code : Tout sélectionner
//recuperation des valeurs
$IDDep = $_GET['Dep'] ?? "";
$IDSer = $_GET['Ser'] ?? "";
$IDEmp = $_GET['Emp'] ?? "";
$IDSea = $_GET['Sea'] ?? "";
$TxtIDDep = $_GET['DepTxt'] ?? "";
$TxtIDSer = $_GET['SerTxt'] ?? "";
$TxtIDEmp = $_GET['EmpTxt'] ?? "";
$TxtIDSea = $_GET["SeaTxt"] ?? "";
if ($_GET['DateFrom'] ?? "") {
$DateFrom = new DateTime($_GET['DateFrom'] ?? "");
}
if ($_GET['DateTo'] ?? "") {
$DateTo = new DateTime($_GET['DateTo'] ?? "");
}
$DayDiff = $_GET['DayDiff'] ?? "";
$SqlRecherche = null;
$Resultat = "";
$diff = null;
if (!empty($DateFrom) && !empty($DateTo)) {
if ($IDEmp == 'T') {
$SqlRecherche = "SELECT DISTINCT * FROM Employes,Service,Departement,Affectation,xhistorique WHERE Service.IDService=Affectation.IDService AND Employes.IDEmployes=xhistorique.IDEmployePointages AND Service.IDDepartement=Departement.IDDepartement AND Employes.UIDEmployes=Affectation.IDEmployes AND ActiveEmp!=0 AND Service.IDService=".$IDSer." AND Departement.IDDepartement=".$IDDep." AND xhistorique.seances=".$TxtIDSea." AND xhistorique.DatePointages BETWEEN '".$DateFrom->format('Y-m-d')."' AND '".$DateTo->format('Y-m-d')."' order by NomCompletEmployes";
} else {
$SqlRecherche = "SELECT DISTINCT * FROM Employes,Service,Departement,Affectation,xhistorique WHERE Service.IDService=Affectation.IDService AND Employes.IDEmployes=xhistorique.IDEmployePointages AND Service.IDDepartement=Departement.IDDepartement AND Employes.UIDEmployes=Affectation.IDEmployes AND ActiveEmp!=0 AND Service.IDService=".$IDSer." AND Departement.IDDepartement=".$IDDep." AND Employes.IDEmployes=".$IDEmp." AND xhistorique.seances=".$TxtIDSea." AND xhistorique.DatePointages BETWEEN '".$DateFrom->format('Y-m-d')."' AND '".$DateTo->format('Y-m-d')."' order by NomCompletEmployes";
}
// echo $SqlRecherche."<br/>";
echo '<div class="card border-left-success shadow h-50 py-2">
<div class="card-body">
<div class="row no-gutters align-items-center">
<div class="col mr-2">
<div class="text-xs font-weight-bold text-success text-uppercase mb-1">Recherche
</div>
<div class="row no-gutters align-items-center"> Département : <b>' . $TxtIDDep . '</b> - Filière : <b>' . $TxtIDSer . '</b> - Etudiant : <b>' . $TxtIDEmp . '</b> <b> - Seances : <b>' . $TxtIDSea . '</b> | De : <b>' . $DateFrom->format('Y-m-d') . '</b> - A : <b>' . $DateTo->format('Y-m-d') . '</b> - Total Jours : ' . $DayDiff . '</div>
</div>
<div class="col-auto">
<i class="fas fa-search fa-2x text-red-300"></i>
</div>
</div>
</div>
</div>';
$Resultat .= "<table class='table table-bordered' id='customer_data' width='100%' cellspacing='0'>
<thead>
<tr>
<th>Matricule</th>
<th>Nom</th>
<!--th>N° Card</th!-->
<th>Département</th>
<th>Filière</th>
<!--th>Pointage</th!-->
<th>Date</th>
<th>Heure</th>
<!--th>Type</th!-->
<th>Salle</th>
<th>Statut</th>
<th>Seances</th>
</tr>
</thead>
<tbody >
";
$statement = $conn->query($SqlRecherche);
$publishers = $statement->fetchAll(PDO::FETCH_ASSOC);
if ($publishers) {
foreach ($publishers as $publisher) {
$Resultat .= "<tr>
<td>" . $publisher['IDEmployePointages'] . "</td>
<td><b>" . $publisher['NomCompletEmployes'] . "</b></td>
<td>" . $publisher['LibelleDepartement'] . "</td>
<td>" . $publisher['LibelleService'] . "</td>
<td>" . $publisher['DatePointages'] . "</td>
<td> " . $publisher['HeurPointages'] . "</td>
<td>" . $publisher['salle'] . "</td>
<td class=" . $publisher['statut'] . " ><b>" . $publisher['statut'] . "</b></td>
<td>" . $publisher['seances'] . "</td>
</tr>";
}
}
} else {
if ($IDEmp == 'T') {
$SqlRecherche = "SELECT DISTINCT * FROM Employes,Service,Departement,Affectation,xhistorique WHERE Service.IDService=Affectation.IDService AND Employes.IDEmployes=xhistorique.IDEmployePointages AND Service.IDDepartement=Departement.IDDepartement AND Employes.UIDEmployes=Affectation.IDEmployes AND ActiveEmp!=0 AND Service.IDService=".$IDSer." AND xhistorique.seances=".$TxtIDSea." AND Departement.IDDepartement=".$IDDep." order by NomCompletEmployes; ";
} else {
$SqlRecherche = "SELECT DISTINCT * FROM Employes,Service,Departement,Affectation,xhistorique WHERE Service.IDService=Affectation.IDService AND Employes.IDEmployes=xhistorique.IDEmployePointages AND Service.IDDepartement=Departement.IDDepartement AND Employes.UIDEmployes=Affectation.IDEmployes AND ActiveEmp!=0 AND Service.IDService=".$IDSer." AND Departement.IDDepartement=".$IDDep." AND xhistorique.seances=".$TxtIDSea." AND Employes.IDEmployes=".$IDEmp." order by NomCompletEmployes";
}
// echo $SqlRecherche."<br/>";
echo '<div class="card border-left-success shadow h-50 py-2">
<div class="card-body">
<div class="row no-gutters align-items-center">
<div class="col mr-2">
<div class="text-xs font-weight-bold text-success text-uppercase mb-1">Recherche
</div>
<div class="row no-gutters align-items-center"> Département : <b>' . $TxtIDDep . '</b> - Filière : <b>' . $TxtIDSer . '</b> - Etudiants : <b>' . $TxtIDEmp . '</b> - Séance : <b>' . $TxtIDSea . '</b></div>
</div>
<div class="col-auto">
<i class="fas fa-search fa-2x text-red-300"></i>
</div>
</div>
</div>
</div>';
$Resultat .= "
<table class='table table-bordered' id='customer_data' width='100%' cellspacing='0'>
<thead>
<tr>
<th>Matricule</th>
<th>Nom</th>
<!--th>N° Card</th!-->
<th>Département</th>
<th>Filière</th>
<!--th>Pointage</th!-->
<th>Date</th>
<th>Heure</th>
<!--th>Type</th!-->
<th>Salle</th>
<th>Statut</th>
<th>Seances</th>
</tr>
</thead>
<tbody >";
$statement = $conn->query($SqlRecherche);
$publishers = $statement->fetchAll(PDO::FETCH_ASSOC);
if ($publishers) {
foreach ($publishers as $publisher) {
$Resultat .= "<tr>
<td>" . $publisher['IDEmployePointages'] . "</td>
<td>" . $publisher['NomCompletEmployes'] . "</td>
<td>" . $publisher['LibelleDepartement'] . "</td>
<td>" . $publisher['LibelleService'] . "</td>
<td>" . $publisher['DatePointages'] . "</td>
<td>" . $publisher['HeurPointages'] . "</td>
<td>" . $publisher['salle'] . "</td>
<td>" . $publisher['statut'] . "</td>
<td>" . $publisher['seances'] . "</td>
</tr>";
}
}
}
//echo $SqlRecherche.'<br/>';
$Resultat .= "
</tbody>
</table>";
echo '<input type="text" id="ResInput" value="' . $Resultat . '" hidden/>';
