Page 1 of 1

Pagination prob w/ multiple criteria query

Posted: Tue Oct 26, 2004 9:44 am
by rixxe
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:

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();
}

?>
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!

Posted: Tue Oct 26, 2004 10:41 am
by timvw
this problem has been issued before this week...... eventually the OP decided to pass the $query in his URL, but i prefer to pass it by using sessions

§

Posted: Wed Oct 27, 2004 7:05 am
by rixxe
ok, so I tried to create a session variable, but it still doesn't work. The $sql variable changes its value when I browse the results. What's wrong?? Here's the code with the modifications. This time, the function is included aswell.

Code: Select all

<?
session_start();
$_SESSION['sql'] = $sql;
?>
<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>

<?
function make_user_page_nums($totalrows, $print_query, $page_name) {

global $user_view_limit;
global $page;
global $limitvalue;

echo "Pages: ";

if($page != 1) { 
$pageprev = $page - 1; 
echo "<a href="".$page_name.$print_query."page=".$pageprev.""><Prev</a> "; 
} 

$numofpages = $totalrows/$user_view_limit; 

for($i= 0; $i < $numofpages; $i++) { 
$real_page = $i + 1;
if ($real_page!=$page){
echo " <a href="".$page_name.$print_query."page=".$real_page."">".$real_page."</a> "; 

} else {
echo "<b>".$real_page."</b>";
}
} 

if(($totalrows-($user_view_limit*$page)) > 0){ 
$pagenext = $page + 1; 
echo " <a href="".$page_name.$print_query."page=".$pagenext."">Next ></a> "; 
} 

}

$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();
}

echo "<br>";
echo $sql;
?>

Posted: Wed Oct 27, 2004 8:09 am
by CoderGoblin
OK three possibilities to assist:

1) Why don't you just pass the user requested variables instead of the SELECT. Will be shorter.
2) make the buttons (next/previous) submit a form (POST variables can be larger than GET
3) Place the search parameters into the database as a "Last Search By User". Side effect is user could have a backup of previous searches.

Posted: Wed Oct 27, 2004 9:30 am
by rixxe
CoderGoblin wrote:OK three possibilities to assist:

1) Why don't you just pass the user requested variables instead of the SELECT. Will be shorter.
2) make the buttons (next/previous) submit a form (POST variables can be larger than GET
3) Place the search parameters into the database as a "Last Search By User". Side effect is user could have a backup of previous searches.
I think I'll go crazy over this damn issue!
I tried to pass the requested variables, but it doesn't work. The DB returns only one result.
Your #3 is not an option as the users are anonymously searching this DB.
I think I going to use a variation of the #2 by forming the query in a first page, then passing it to another one which will do the searching. What do you think?

Posted: Wed Oct 27, 2004 10:01 am
by timvw
meaby lookup how sessions work.... and then try to use them... because from the code you posted, it's obvious you don't have a clue what you are doing...


"store" ->>>> $_SESSION['foo'] = $bar;


"retrieve" ->>>> session_start(); $bar = $_SESSION['foo'];

Posted: Wed Oct 27, 2004 11:39 am
by rixxe
ok, I corrected this and splitted the code into 2 pages. First page builds the query, based on which field is used, second one shows the results. Variables are being passed using sessions. Same <span style='color:blue' title='I&#39;m naughty, are you naughty?'>smurf</span>; it doesn't work, same results as when it was all on 1 page...

Posted: Wed Oct 27, 2004 1:30 pm
by Christopher
With all those different query types maybe you could organize them like:

Code: Select all

$select[] = "SELECT * FROM tblvente WHERE tblvente.prix <= '$prix' AND tblvente.pieces =         $select = "SELECT * FROM tblvente WHERE tblvente.lieu = '$lieu' AND tblvente.pieces = '$pieces' LIMIT $limitvalue, $user_view_limit";
'$pieces' LIMIT $limitvalue, $user_view_limit";

if ($REQUEST['conditioni'] == '') {
    if ((($lieu == '') && ($prix != none)) && ($pieces != none)){
        $condition = 0;
    }
    if ((($lieu != '') && ($prix == none)) && ($pieces != none)){
        $condition = 1;
    }
} else {
    $condition = intval($REQUEST['conditioni'])
}
$sql = $select[$condition];
Then pass $condition to make_user_page_nums and add $url . "&condition=$condition" to your URL.