Je suis débutant en prog PHP. Je cherche à faire une page de recherche multicritere
Pour rendre le visu plus agréable, j'ai ajouté une pagination. Avec une requete "simple" sans critere, le resultat est correct et la pagination fonctionne bien.
Je souhaite ajouter une recherche sur une période, pas de probleme pour la construction de la requete, par contre la pagination ne fonctionne plus.
Est ce qu'il y aurait quelqu'un qui pourrait me donner une piste svp?
Je vous mets le code:
index.php
<?php
include 'db_connect.php';
header('Content-type: text/html; charset=iso-8859-1');
$startdate="";
$enddate="";
$addtoUrl="";
//requete combo client
$sql_clt="select id,name from products where isactive = '1'";
//requete combo sev
$sql_sev="select id, value from bug_severity";
$sql="SELECT
`bugs_activity`.`bug_id` AS `bug_id`,
`products`.`name` AS `client`,
`bugs`.`cf_platforme` AS `plateforme`,
`bugs_activity`.`bug_when` AS `bug_when`,
`bugs`.`product_id` AS `product_id`,
`bug_severity`.`id` AS `severity_id`,
`bugs`.`bug_severity` AS `severité`,
COUNT(*) AS count
FROM
`bugs_activity`,
`bug_severity`,
`bugs`,
`products`
WHERE
`bugs`.`product_id` = `products`.`id`
AND `bug_severity`.`value` = `bugs`.`bug_severity`
AND `bugs`.`bug_id` = `bugs_activity`.`bug_id`
AND `bugs_activity`.`bug_when` IS NOT NULL
AND `bugs_activity`.`added` = 'REOPENED'";
if(isset($_POST['submit'])){
$startdate=date('Y-m-d', strtotime($_POST['from_date']));
$enddate=date('Y-m-d', strtotime($_POST['to_date']));
$addtoUrl='&from_date='& $startdate +'&to_date=' & $enddate;
}
$sql .= " AND `bugs_activity`.`bug_when` BETWEEN '$startdate' AND '$enddate'";
$sql .= " GROUP BY `bugs_activity`.`bug_id`";
$sql .= " ORDER BY BUG_ID ASC";
?>
<!DOCTYPE html>
<html>
<head>
<meta charset="utf-8" />
<link rel="stylesheet" href="css/style.css" type="text/css">
<link rel="stylesheet" href="css/jquery-ui.css" type="text/css">
<script type = "text/javascript" src="js/jquery-3.3.1.js"></script>
<script type = "text/javascript" src="js/jquery-ui.js"></script>
<script>
$(document).ready(function(){
$.datepicker.setDefaults({
dateFormat: 'yy-mm-dd',
changeYear:true,
changeMonth:true
});
$(function(){
$("#from_date").datepicker();
$("#to_date").datepicker();
});
$('#filterbutton').click(function(){
var from_date = $('#from_date').val();
var to_date = $('#to_date').val();
if(from_date != '' && to_date != '')
{
alert(from_date);
alert(to_date);
}
else
{
alert("Please Select Date");
}
});
});
</script>
<title>Tickets Demo</title>
</head>
<body>
<div id="container">
<h1>Liste des tickets</h1><br><br>
<h2></h2>
<form action="index.php" method="POST">
<!-- plage de date -->
<fieldset class="fieldset-auto-width">
<legend>Periode de recherche:</legend>
<label>Du:</label><input id="from_date" type="text" value="<?php echo $startdate ?>" name="from_date">
<label>Au: </label><input id="to_date" type="text" value="<?php echo $enddate ?>" name="to_date">
</fieldset>
<!-- Remplissage de Combo severité -->
<fieldset class="fieldset-auto-width">
<legend>Clients: </legend>
<select id="idclient" name="idclient">
<option value = "" disabled selected>---Select---</option>
<?php
$stmt=$conn->prepare($sql_clt);
$stmt->execute();
$clients=$stmt->fetchAll(PDO::FETCH_ASSOC);
foreach($clients as $client): ?>
<option value="<?php echo $client['name'] ?>"><?php echo $client['name'] ?>
<?php endforeach; ?>
</select>
</fieldset>
<!-- Remplissage de Combo "Clients" -->
<fieldset class="fieldset-auto-width">
<legend>Severite: </legend>
<select id="sevid" name="sevid">
<option value = "" disabled selected>---Select---</option>
<?php
$stmt=$conn->prepare($sql_sev);
$stmt->execute();
$severity=$stmt->fetchAll(PDO::FETCH_ASSOC);
foreach($severity as $sev): ?>
<option value="<?php echo $sev['value'] ?>"><?php echo $sev['value'] ?>
<?php endforeach; ?>
</select><br>
</fieldset> <input id="filterbutton" type="submit" value="Filtrer" name="submit"><br><br>
</form>
<table id="table_data" class="data-table">
<thead>
<tr>
<th>TICKET</th>
<th>CLIENT</th>
<th>PRODUCT_ID</th>
<th>PLATEFORME</th>
<th>ID_SEVERITE</th>
<th>SEVERITE</th>
<th>DATE</th>
<th>NB_REOPENED</th>
</tr>
</thead>
<tbody>
<?php
$record_per_page=10;
$sql_data=$paginate->paging($sql,$record_per_page,$addtoUrl);
$paginate->dataview($sql_data);
?>
</tbody>
</table><br>
</div>
<div class="paginate">
<?php $paginate->pagelink($sql, $record_per_page); ?>
</div>
</body>
</html>
code pagination.php:
<?php
class pagination {
private $db;
public function __construct($db) {
$this->db = $db;
}
/*
public function create($first, $last, $email, $contact) {
try {
$stmt = $this->db->prepare("insert into users(first_name,last_name,email,contact) values(:first,:last,:email,:phone)");
$stmt->bindparam(":first", $first);
$stmt->bindparam(":last", $last);
$stmt->bindparam(":email", $email);
$stmt->bindparam(":phone", $contact);
$stmt->execute();
return true;
} catch (PDOException $ex) {
echo $ex->getMessage();
return false;
}
}
public function delete($id) {
$stmt = $this->db->prepare("Delete from users where id=:id");
$stmt->bindparam(":id", $id);
$stmt->execute();
return true;
}
public function getID($id) {
$stmt = $this->db->prepare("select * from users where id=:id");
$stmt->execute(array(":id" => $id));
$editrow = $stmt->fetch(PDO::FETCH_ASSOC);
return $editrow;
}
public function update($id, $first, $last, $email, $contact) {
try {
$stmt = $this->db->prepare("update users set first_name=:fname,last_name=:last,email=:email,contact=:cont where id=:id");
$stmt->bindparam(":id", $id);
$stmt->bindparam(":fname", $first);
$stmt->bindparam(":last", $last);
$stmt->bindparam(":email", $email);
$stmt->bindparam(":cont", $contact);
$stmt->execute();
return true;
} catch (Exception $ex) {
echo $ex->getMessage();
return false;
}
}
*/
public function fillcbcclient($sql_clt){
$stmt = $this->db->prepare($sql_clt);
$stmt->execute();
$editrow = $stmt->fetch(PDO::FETCH_ASSOC);
}
public function dataview($query) {
$stmt = $this->db->prepare($query);
$stmt->execute();
if ($stmt->rowCount() > 0) {
while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
?>
<tr>
<td><?php
if (isset($row['bug_id'])) {
print($row['bug_id']);
}
?></td>
<td><?php
if (isset($row['client'])) {
print($row['client']);
}
?></td>
<td><?php
if (isset($row['product_id'])) {
print($row['product_id']);
}
?></td>
<td><?php
if (isset($row['plateforme'])) {
print($row['plateforme']);
}
?></td>
<td><?php
if (isset($row['severity_id'])) {
print($row['severity_id']);
}
?></td>
<td><?php
if (isset($row['severité'])) {
print($row['severité']);
}
?></td>
<td><?php
if (isset($row['bug_when'])) {
print($row['bug_when']);
}
?></td>
<td><?php
if (isset($row['count'])) {
print($row['count']);
}
?></td>
</tr>
<?php
}
} else {
?>
<tr>
<td>Pas de données à afficher</td>
</tr>
<?php
}
}
public function paging($query, $record_per_page,$addtoUrl) {
$starting_position = 0;
echo $addtoUrl;
if(isset($_GET['page_no']))
{
$starting_position=($_GET['page_no']-1)*$record_per_page;
}
$query2 = $query . " limit $starting_position,$record_per_page";
return $query2;
}
public function pagelink($query, $record_per_page) {
$self = $_SERVER['PHP_SELF'];
echo $query;
$stmt=$this->db->prepare($query);
$stmt->execute();
$total_no_of_records=$stmt->rowCount();
if($total_no_of_records > 0)
{
?>
<ul class="pagination">
<?php
$total_no_pages=ceil($total_no_of_records/$record_per_page);
$current_page=1;
if(isset($_GET["page_no"]))
{
$current_page=$_GET["page_no"];
}
if($current_page!=1)
{
$previous=$current_page-1;
echo "<li><a href='".$self."?page_no=1'>First</a></li>";
echo "<li><a href='".$self."?page_no=".$previous."'><<</a></li>";
}
for($i=1;$i<=$total_no_pages;$i++)
{
if($i==$current_page)
{
echo "<li><a href='".$self."?page_no=".$i."' style='color:red;'>".$i."</a></li>";
}
else
{
echo "<li><a href='".$self."?page_no=".$i."''>".$i."</a></li>";
}
}
if($current_page!= $total_no_pages)
{
$next=$current_page+1;
echo "<li><a href='".$self."?page_no=".$next."'>>></a></li>";
echo "<li><a href='".$self."?page_no=".$total_no_pages."'>Last</a></li>";
}
?>
</ul>
<?php
}
}
}
?>
SELECT `bugs_activity`.`bug_id` AS `bug_id`,
`products`.`name` AS `client`,
`bugs`.`cf_platforme` AS `plateforme`,
`bugs_activity`.`bug_when` AS `bug_when`,
`bugs`.`product_id` AS `product_id`,
`bug_severity`.`id` AS `severity_id`,
`bugs`.`bug_severity` AS `severité`,
COUNT(*) AS COUNT
FROM `bugs_activity`,
`bug_severity`,
`bugs`,
`products`
WHERE `bugs`.`product_id` = `products`.`id`
AND `bug_severity`.`value` = `bugs`.`bug_severity`
AND `bugs`.`bug_id` = `bugs_activity`.`bug_id`
AND `bugs_activity`.`bug_when` IS NOT NULL
AND `bugs_activity`.`added` = 'REOPENED'
AND `bugs_activity`.`bug_when` BETWEEN '2018-03-01' AND '2018-05-31'
GROUP BY `bugs_activity`.`bug_id`
ORDER BY BUG_ID ASC
Une chose que j'ai oublié de préciser dans mon probleme. L'affichage de la premiere page se fait correctement. Le contenu est correct. le probleme se pose lorsque je passe sur la page 2 où là, rien ne s'affiche.
Je pense que le problème tourne autour de ce qui passe comme variables lors du passage sur la page 2. Je pense que la plage de date n'est pas prise en compte. mais ce n'est qu'une hypothèse. Ce que j'aimerai savoir c'est comment passer mon critere de date sur chaque changement de page.
Ah ok, je comprends mieux. En effet, il faut t'assurer de la portabilité de tes variables. Tu peux les passer par exemple dans les liens en paramètre avec page_no, ou utiliser des variables de session.
J'ai regardé comment utiliser les variables de sessions pour corriger mon problème. J'arrive au resultat que je souhaite sauf lorsque j'initialise les variables de sessions en début de script mes variables ne sont pas correctes lorsque je passe à la page 2. Dans ma requete je perds mes variables, elles reprennent les valeurs comme je les ai initialisées.
Ma question est la suivante: Comment je peux affecter de nouvelles valeurs à mes variables et qu'elles soient persistantes?
mon code:
<?php
session_start();
//initialisation des variables de sessions
$_SESSION['startdate']="";
$_SESSION['enddate']="";
include 'db_connect.php';
header('Content-type: text/html; charset=iso-8859-1');
//requete combo client
$sql_clt="select id,name from products where isactive = '1'";
//requete combo sev
$sql_sev="select id, value from bug_severity";
$sql="SELECT
`bugs_activity`.`bug_id` AS `bug_id`,
`products`.`name` AS `client`,
`bugs`.`cf_platforme` AS `plateforme`,
`bugs_activity`.`bug_when` AS `bug_when`,
`bugs`.`product_id` AS `product_id`,
`bug_severity`.`id` AS `severity_id`,
`bugs`.`bug_severity` AS `severité`,
COUNT(*) AS count
FROM
`bugs_activity`,
`bug_severity`,
`bugs`,
`products`
WHERE
`bugs`.`product_id` = `products`.`id`
AND `bug_severity`.`value` = `bugs`.`bug_severity`
AND `bugs`.`bug_id` = `bugs_activity`.`bug_id`
AND `bugs_activity`.`bug_when` IS NOT NULL
AND `bugs_activity`.`added` = 'REOPENED'";
if(isset($_POST['submit'])){
if(isset($_POST['from_date'])){
$_SESSION['startdate']=$_POST['from_date'];
}
//date de fin
if(isset($_POST['to_date'])){
$_SESSION['enddate']=$_POST['to_date'];
}
}
$sql .= " AND `bugs_activity`.`bug_when` BETWEEN '".$_SESSION['startdate']."' AND '".$_SESSION['enddate']."'";
$sql .= " GROUP BY `bugs_activity`.`bug_id`";
$sql .= " ORDER BY BUG_ID ASC";
echo $sql;
?>
<!DOCTYPE html>
<html>
<head>
<meta charset="utf-8" />
<link rel="stylesheet" href="css/style.css" type="text/css">
<link rel="stylesheet" href="css/jquery-ui.css" type="text/css">
<script type = "text/javascript" src="js/jquery-3.3.1.js"></script>
<script type = "text/javascript" src="js/jquery-ui.js"></script>
<script>
$(document).ready(function(){
$.datepicker.setDefaults({
dateFormat: 'yy-mm-dd',
changeYear:true,
changeMonth:true
});
$(function(){
$("#from_date").datepicker();
$("#to_date").datepicker();
});
$('#filterbutton').click(function(){
var from_date = $('#from_date').val();
var to_date = $('#to_date').val();
if(from_date != '' && to_date != '')
{
}
else
{
alert("Please Select Date");
}
});
});
</script>
<title>Tickets Demo</title>
</head>
<body>
<div id="container">
<h1>Liste des tickets</h1><br><br>
<h2></h2>
<form method="post" action="<?php echo $_SERVER['REQUEST_URI']; ?>">
<!-- plage de date -->
<fieldset class="fieldset-auto-width">
<legend>Periode de recherche:</legend>
<label>Du:</label><input id="from_date" type="text" value="<?php echo $_SESSION['startdate'] ?>" name="from_date">
<label>Au: </label><input id="to_date" type="text" value="<?php echo $_SESSION['enddate']?>" name="to_date">
</fieldset>
<!-- Remplissage de Combo severité -->
<fieldset class="fieldset-auto-width">
<legend>Clients: </legend>
<select id="idclient" name="idclient">
<option value = "" disabled selected>---Select---</option>
<?php
$stmt=$conn->prepare($sql_clt);
$stmt->execute();
$clients=$stmt->fetchAll(PDO::FETCH_ASSOC);
foreach($clients as $client): ?>
<option value="<?php echo $client['name'] ?>"><?php echo $client['name'] ?>
<?php endforeach; ?>
</select>
</fieldset>
<!-- Remplissage de Combo "Clients" -->
<fieldset class="fieldset-auto-width">
<legend>Severite: </legend>
<select id="sevid" name="sevid">
<option value = "" disabled selected>---Select---</option>
<?php
$stmt=$conn->prepare($sql_sev);
$stmt->execute();
$severity=$stmt->fetchAll(PDO::FETCH_ASSOC);
foreach($severity as $sev): ?>
<option value="<?php echo $sev['value'] ?>"><?php echo $sev['value'] ?>
<?php endforeach; ?>
</select><br>
</fieldset> <input id="filterbutton" type="submit" value="Filtrer" name="submit"><br><br>
</form>
<table id="table_data" class="data-table">
<thead>
<tr>
<th>TICKET</th>
<th>CLIENT</th>
<th>PRODUCT_ID</th>
<th>PLATEFORME</th>
<th>ID_SEVERITE</th>
<th>SEVERITE</th>
<th>DATE</th>
<th>NB_REOPENED</th>
</tr>
</thead>
<tbody>
<?php
$record_per_page=10;
$sql_data=$paginate->paging($sql,$record_per_page);
$paginate->dataview($sql_data);
?>
</tbody>
</table><br>
</div>
<div class="paginate">
<?php $paginate->pagelink($sql, $record_per_page); ?>
</div>
</body>
</html>
× Après avoir cliqué sur "Répondre" vous serez invité à vous connecter pour que votre message soit publié.
× Attention, ce sujet est très ancien. Le déterrer n'est pas forcément approprié. Nous te conseillons de créer un nouveau sujet pour poser ta question.
N'oubliez pas d'activer les erreurs PDO.
N'oubliez pas d'activer les erreurs PDO.
N'oubliez pas d'activer les erreurs PDO.
N'oubliez pas d'activer les erreurs PDO.
N'oubliez pas d'activer les erreurs PDO.
N'oubliez pas d'activer les erreurs PDO.