Pagination prob w/ multiple criteria query

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
rixxe
Forum Newbie
Posts: 4
Joined: Tue Oct 26, 2004 9:05 am
Location: Switzerland

Pagination prob w/ multiple criteria query

Post 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!
timvw
DevNet Master
Posts: 4897
Joined: Mon Jan 19, 2004 11:11 pm
Location: Leuven, Belgium

Post 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
rixxe
Forum Newbie
Posts: 4
Joined: Tue Oct 26, 2004 9:05 am
Location: Switzerland

§

Post 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;
?>
User avatar
CoderGoblin
DevNet Resident
Posts: 1425
Joined: Tue Mar 16, 2004 10:03 am
Location: Aachen, Germany

Post 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.
rixxe
Forum Newbie
Posts: 4
Joined: Tue Oct 26, 2004 9:05 am
Location: Switzerland

Post 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?
timvw
DevNet Master
Posts: 4897
Joined: Mon Jan 19, 2004 11:11 pm
Location: Leuven, Belgium

Post 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'];
rixxe
Forum Newbie
Posts: 4
Joined: Tue Oct 26, 2004 9:05 am
Location: Switzerland

Post 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...
User avatar
Christopher
Site Administrator
Posts: 13596
Joined: Wed Aug 25, 2004 7:54 pm
Location: New York, NY, US

Post 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.
Post Reply