Pagination prob w/ multiple criteria query
Posted: Tue Oct 26, 2004 9:44 am
Hi everybody!
I have a page which searches a DB using mySQL queries. Since there are several criterias the user might use I had to write several queries for each case. Then through 'if' instructions a query is chosen.
It works well, until I introduce a pagination function (the first results are displayed properly, but when I click on 'page 2', it seems that the browser is unable to pass the right query).
Here's my code:
Just to make it clear: if I have only one query, the pagination function works properly. So is there a way to get rid of the 'if' instructions while still being able to query the DB with multiple criteria, if not, how to pass the right query to the next page?
Thanks in advance!
I have a page which searches a DB using mySQL queries. Since there are several criterias the user might use I had to write several queries for each case. Then through 'if' instructions a query is chosen.
It works well, until I introduce a pagination function (the first results are displayed properly, but when I click on 'page 2', it seems that the browser is unable to pass the right query).
Here's my code:
Code: Select all
<HTML>
<HEAD>
<TITLE>Untitled</TITLE>
<LINK rel="stylesheet" href="include/style.css">
</HEAD>
<BODY>
<table align="center" width="500">
<tr>
<td align="center"><a href="insert.php">Insert</a>
</td>
<td align="center"><a href="search.php">Search</a>
</td>
</tr>
<tr>
<td colspan="2"><hr color="black" width="500"></td>
</tr>
</table>
<table align="center" width="500" cellspacing="0" cellpadding="0">
<form action="search6.php" method="post">
<tr>
<td width="150" height="30">Lieu: </td>
<td><input type="text" name="lieu"></td>
</tr>
<td width="150" height="30">Pièces:</td>
<td><select name="pieces" size="1">
<option value="none" selected="selected">Indifférent
<option value="0">Studio
<option value="1">1
<option value="1.5">1.5
<option value="2">2
<option value="2.5">2.5
<option value="3">3
<option value="3.5">3.5
<option value="4">4
<option value="4.5">4.5
<option value="5">5
<option value="5.5">5.5
<option value="6">6
<option value="6.5">6.5
<option value="7">7
<option value="7.5">7.5
</select>
</td>
<tr>
<td width="150" height="30">Prix:</td>
<td><select name="prix" size="1">
<option value="none" selected="selected">Indifférent
<option value="100000">100'000.--
<option value="200000">200'000.--
<option value="300000">300'000.--
<option value="400000">400'000.--
<option value="500000">500'000.--
<option value="750000">750'000.--
<option value="1000000">1'000'000.--
</select>
</td>
</tr>
<tr>
<td colspan="2"><input type="Submit"></td>
<tr>
<td colspan="2"><hr color="black" width="500"></td>
</tr>
</table>
</form>
<?php
include 'include/paging.inc.php';
$user_view_limit = "5";
if ((empty($page)) || ($page <= 0)){
$page = 1;
}
$limitvalue = $page*$user_view_limit-($user_view_limit);
include 'include/dbopen.inc.php';
if ((($lieu == '') && ($prix != none)) && ($pieces != none)){
$sql = "SELECT * FROM tblvente WHERE tblvente.prix <= '$prix' AND tblvente.pieces = '$pieces' LIMIT $limitvalue, $user_view_limit";
}
if ((($lieu != '') && ($prix == none)) && ($pieces != none)){
$sql = "SELECT * FROM tblvente WHERE tblvente.lieu = '$lieu' AND tblvente.pieces = '$pieces' LIMIT $limitvalue, $user_view_limit";
}
if ((($lieu != '') && ($prix != none)) && ($pieces == none)){
$sql = "SELECT * FROM tblvente WHERE tblvente.lieu = '$lieu' AND tblvente.prix <= '$prix' LIMIT $limitvalue, $user_view_limit";
}
if ((($lieu == '') && ($prix != none)) && ($pieces == none)){
$sql = "SELECT * FROM tblvente WHERE tblvente.prix <= '$prix' LIMIT $limitvalue, $user_view_limit";
}
if ((($lieu != '') && ($prix == none)) && ($pieces == none)){
$sql = "SELECT * FROM tblvente WHERE tblvente.lieu = '$lieu' LIMIT $limitvalue, $user_view_limit";
}
if ((($lieu == '') && ($prix == none)) && ($pieces != none)){
$sql = "SELECT * FROM tblvente WHERE tblvente.pieces = '$pieces' LIMIT $limitvalue, $user_view_limit";
}
if ((($lieu == '') && ($prix == none)) && ($pieces == none)){
$sql = "SELECT * FROM tblvente LIMIT $limitvalue, $user_view_limit";
}
$sqlcount = "SELECT count(*) FROM tblvente";
$print_query ="?";
$sql_countresult = mysql_query($sqlcount);
list($totalrows) = mysql_fetch_row($sql_countresult);
if ($get_items = mysql_query($sql)) {
$num_items = mysql_num_rows($get_items);
if ($num_items > 0) {
if ($user_view_limit < $totalrows) {
make_user_page_nums($totalrows, $print_query, "$PHP_SELF");
}
echo "<table>";
while (list($id,$ref,$adresse,$lieu,$pieces,$prix) = mysql_fetch_row($get_items) ) {
echo "<tr><td>";
echo $id;
echo "</tr></td>";
echo "<tr><td>";
echo $ref;
echo "</tr></td>";
echo "<tr><td>";
echo $adresse;
echo "</tr></td>";
}
echo "</table>";
} else {
echo "No items listed<br>";
}
} else {
echo "An error has occurred: <br>";
echo mysql_error();
}
?>Thanks in advance!