Pagination woes....

sgtpepper

Sorry for such a simple question, but I am a beginner and catch on to some stuff fast...and others not so fast.

I am trying to build a simple pagination. I have spend two days on it. At this stage, I have the next and previous buttons working, but no results of my query are displayed. Can someone take a peek at my code and let me know what I am doing wrong? I would be very appreciative.

<?
if(!isset($start)) $start = 0;
$username="xxx";
$password="xxx";
$database="xxx";

mysql_connect(localhost,$username,$password);
@mysql_select_db($database) or die( "Unable to select database");

$query="SELECT * ,IF (lister_office_name like 'Century 21', 1, 0)
AS featured
FROM residential
WHERE town like '%$selecttown'
AND list_price like '%$selectprice'
AND bedrooms like '%$bedroomsel'
AND baths like '%$bathselect'
ORDER BY featured
DESC LIMIT " . $start . ", 10";

$result=mysql_query($query) or die (mysql_error());
$num=mysql_numrows($result);
?>

<?
$query = "SELECT count(*) as count FROM residential WHERE town like '%$selecttown' AND list_price like '%$selectprice' AND bedrooms like '%$bedroomsel' AND baths like '%$bathselect'";

$result = mysql_query($query);
$row = mysql_fetch_array($result);
$numrows = $row['count'];
if($start > 0)
echo "<a href=\"" . $PHP_SELF . "?start=" . ($start - 10) .
"\">Previous</a> \n";
if($numrows > ($start + 10))
echo "<a href=\"" . $PHP_SELF . "?start=" . ($start + 10) .
"\">Next</a> \n";
?>

<?
if(mysql_numrows($result) == '0') {
echo "Sorry, no results were found...";
} else {
// display results here
}
?>

<?
while ($row = mysql_fetch_array($result)) // this will grab the results from query
{
?>

</td>
<td width="12%" align="center" bgcolor="#F3F3E0"><a href="test2.php?Id=<? echo $row['Id']; ?>"><? echo $row['Id']; ?></a>

</td>
<td width="12%" align="center" bgcolor="#F3F3E0"><? echo $row['town']; ?></td>
<td width="12%" align="center" bgcolor="#F3F3E0"><? echo $row['rooms']; ?></td>
<td width="13%" align="center" bgcolor="#F3F3E0"><? echo $row['bedrooms']; ?></td>
<td width="13%" align="center" bgcolor="#F3F3E0"><? echo $row['baths']; ?></td>
<td width="13%" align="center" bgcolor="#F3F3E0"><? echo $row['list_price']; ?></td>
<td width="13%" align="center" bgcolor="#F3F3E0">

<a href="test2.php?Id=<? echo $row['Id']; ?>">Click to view more</a>

</td>
</tr>
</table>

<?
}
?>

tsinka

Hi,

the second query destroys the result set of your first query since you named both result variables $result.

kevinsequeira

try to debug the code.
1. print out the query so that you know what the actual query is.
2. then try running the printed out query in the database directly.
3. see what the link is when ou click on the next and previous links
4. print out the variables that you use in the code

reg
kevin

BeastRider

Originally posted by sgtpepper
Sorry for such a simple question, but I am a beginner and catch on to some stuff fast...and others not so fast.

I am trying to build a simple pagination. I have spend two days on it. At this stage, I have the next and previous buttons working, but no results of my query are displayed. Can someone take a peek at my code and let me know what I am doing wrong? I would be very appreciative.
<?
if(!isset($start)) $start = 0;
$username="xxx";
$password="xxx";
$database="xxx";

mysql_connect(localhost,$username,$password);
@mysql_select_db($database) or die( "Unable to select database");


$query="SELECT * ,IF (lister_office_name like 'Century 21', 1, 0) 
AS featured 
FROM residential 
WHERE town like '%$selecttown' 
AND list_price like '%$selectprice' 
AND bedrooms like '%$bedroomsel' 
AND baths like '%$bathselect'
ORDER BY featured
DESC LIMIT " . $start . ", 10";

$result=mysql_query($query) or die (mysql_error());
$num=mysql_numrows($result);
?>
[/B]

OK... this I understand. Getting ten records max from a query.
But where do you get a value for start? Obviously, you want it
to be 0 for the 1st page, 10 for the 2nd page, etc., but I see no
calculation.

<?
$query = "SELECT count(*) as count FROM residential WHERE town like '%$selecttown' AND list_price like '%$selectprice' AND bedrooms like '%$bedroomsel' AND baths like '%$bathselect'";

$result = mysql_query($query);
$row = mysql_fetch_array($result);
$numrows = $row['count'];
if($start > 0)
   echo "<a href=\"" . $PHP_SELF . "?start=" . ($start - 10) .
        "\">Previous</a><BR>\n";
if($numrows > ($start + 10))
   echo "<a href=\"" . $PHP_SELF . "?start=" . ($start + 10) .
        "\">Next</a><BR>\n";
?>

[/B]

Now, this makes no sense to me at all. You have just done a
query for 10 records, have not used a single record, now you are
counting all the records that meet the select criteria. Why? I
know why... to be able to decide if you should put out the "Next"
link. OK, you can do it this way... its very wasteful though, and if
you are going to do it this way, then at least use the records in
the query you made before you run the count. But again, as I
pointd out in my other pagination posts, you can accomplish the
same thing by reading one more record per page with your initial
query.

<?
if(mysql_numrows($result) == '0') {
   echo "Sorry, no results were found...";
} else {
   // display results here
}
?>

<?
while ($row = mysql_fetch_array($result)) // this will grab the results from query 
{ 
?>

[/B]

This truly makes no sense at several levels.

a) you already have a count statement and yet you find the
number of rows in the result. Of course, the select count
statement is extraneous, so this would be OK if the second select
wasn't there, but

b) all the code from the "while" on down needs to be in the place
where it says "// display results here

In short, don't quit your day job. Beyond syntax problems, there
are logic problems, and you do not seem to be clear as to what
you want to accomplish. Before writing a single line of code, I'd
recommend that you pseudo-code the logoc of what you are
trying to accomplish.

Note also that if statements like

if(mysql_numrows($result) == '0') {

are exception control statements. When first fleshing out a new
script or piece of logic, it always makes it easier to assume that
there will be no errors... make your script run for the best case
scenario. Then, once you know it works, go back and figure out
how to handle error/end of file conditions.

Right now, it looks like you have cobbled together code from me
and several other sources. You've created a Frankenstein son,
and its gonna come back and haunt you.

sgtpepper

Beastrider,

Thank you for all your help. I am just beginning PHP and want to avoid the Frankenstein as much as possible 🙂

You are correct though, I have been peingin bits of code together to get this thing off the ground while trying to learn.

I have taken you posts and have attempted to clean up my code a little.

My project is displaying real estate listings from a database of about 10,000 records.

Even after cleaning things up a little....it is still running slow. If you have a second can you take a peek at my cleaned up code and give me your thoughts?

<html>
<head>
<title> test </title>
<head>
<body>
<?php
// Connect to the database server
$dbcnx = @mysql_connect("xxx", "xxx", "xxx");
if (!$dbcnx) {
echo( "Unable to connect to the " ."database server at this time." );
exit();
}
// Select the jokes database
if (! @mysql_select_db("new") ) {
echo( "Unable to locate the database at this time." );
exit();
}
?>
Header Here
search here
<?php
if(!isset($start)) $start = 0;
// Request the text of all the jokes
$result = @("SELECT town, list_price, bedrooms, baths, rooms, Id ,IF (lister_office_name like 'Realty', 1, 0)
AS featured
FROM residential
WHERE town like '%$selecttown'
AND list_price like '%$selectprice'
AND bedrooms like '%$bedroomsel'
AND baths like '%$bathselect'
ORDER BY featured
DESC LIMIT " . $start . ", 10");
if (!$result) {
echo("Error performing query: " . mysql_error() . "");
exit();
}
// Display the text of each in a paragraph
while ( $row = mysql_fetch_array($result) ) {
echo("" . $row["Id"] . "");
}
?>
footer here
</body>
</html>