Paging Problem

ColinHughes · August 4, 2010, 7:56pm

I have the following code to add paged results to my page. First page works fine bringing back " Showing “page 1 of 5” for example. Clicking on the next link does not result in “page 2 of 5” it results in “page 2” of the total entries in my DB table.

I also use mod_rewrite on the results thus:

RewriteRule listings-page(.*)\\.html$ index.html?page=$1


<?php include ("includes/conn.php"); 

function paging3($rowsPerPage,$numrows,$pageNum=1,$param="")
{
    global $db; // BY DEFAULT SHOW FIRST PAGE
   
   
   $maxPage = ceil($numrows/$rowsPerPage);// TOTAL OF PAGES FOR PAGING
   $self = "property/listings-page"; // PAGE WHERE RESULTS ARE SHOWN
  
   

   if ($pageNum > 1)  // PAGE ONE 
   {
      $page = $pageNum - 1;
      $previous = "<a href=\\"" . $self . "" . $page . ".html\\">[Prev]</a>";
        $first = "<a href=\\"" . $self . "1" . ".html\\">[First Page]</a>";
   }
   else
   {
      $previous  = "";  // WE ARE ON PAGE OME SO DO NOT SHOW PREVIOUS LINK
      $first = ""; // OR THE LINK TO THE FIRST PAGE
   }
   
   if($pageNum < $maxPage)// SHOW NEXT KINK ONLY IF WE ARE NOT ON THE LAST PAGE
   {
      $page = $pageNum + 1;
      $next = " <a href=\\"" . $self . "" . $page . ".html\\">[Next]</a> ";
      $last = " <a href=\\"" . $self . "" . $maxPage . ".html\\">[Last Page]</a> ";
   }
   else
   {
      $next = '  ';      // WE ARE ON THE LAST PAGE SO DO NOT SHOW THE NEXT LINK
      $last = '  '; // OR THE LINK TO THE LAST PAGE
   }
   
   // ECHO THE PAGE NAVIGATION LINKS COUNTING THE OFFSET
   return  $first . " " .  $previous . " Showing page <strong>" . $pageNum . "</strong> of <strong>" . $maxPage . "</strong> pages   " . $next . " " .  $last;
}


?>


<?php


/*================= Build Search Query ====================================*/

 
$query = array();


if(!empty($_POST['ref']))
{
     
    // Allow only alphanumeric characters
    
    $result = preg_replace('/[^a-zA-Z0-9 ]/', '', $_POST['ref']);  
    $query[] = "" . $prev . "property.propertyref = '$result'";  
}


if(!empty($_POST['city']))
{

    // Allow only alphabetic characters
    
    $result = preg_replace('/[^a-zA-Z ]/', '', $_POST['city']);  
    $query[] = "" . $prev . "property.city = '$result'"; 
}


if(!empty($_POST['property_type']))
{

    // Allow only alphabetic characters
    
    $result = preg_replace('/[^a-zA-Z ]/', '', $_POST['property_type']);  

    $query[] = "" . $prev . "property.property_type = '$result'";
}


if(!empty($_POST['bedrooms']))
{

    // Allow only numeric characters
    
    $result = preg_replace('/[^0-9 ]/', '', $_POST['bedrooms']); 
    $query[] = "" . $prev . "property.bedrooms = '$result'";
}


    $query[] = "" . $prev . "property.status ='Y'";
if(!empty($query))

{
    $MyQuery = implode(" AND ", $query);
    $MyQuery = "WHERE ".$MyQuery;
    
    //echo $MyQuery;
}

    // Paging for search results
    
    if('!$limit')
    
    {
    
    $limit=0;$lmt="limit " . $limit . ",7";
    
    }
    
    else
    
    {
    
    $lmt="limit " . $limit . ",8";
    
    }
    
    $r2=mysql_query("select count(*) as total from " . $prev . "property " . $MyQuery . " order by price asc");

    $total=@mysql_result($r2,0,"total");
    $rowsPerPage = 3;
    $pageNum = 1;
if(isset($_GET['page']))
{
       $pageNum = $_GET['page'];
}
    $offset = ($pageNum - 1) * $rowsPerPage;

?>


<?php 
   echo "<p class='paging'>";
   echo  paging3($rowsPerPage,$total,$pageNum,"$_SERVER[QUERY_STRING]");
   echo "</p>";

?>

As always, guidance most appreciated. I really need to get my head around this.

Thanks

Colin

rpkamp · August 7, 2010, 7:04pm

Excellent explanation RNEL, kudos :tup:

Two minor suggestions if I may: the first line of the code can emit an E_NOTICE warning (if enabled in error_reporting) because you’re using the value of $_GET[‘page’] which may have not actually been set.

You can easily solve this by combining lines 1 and 2 into one:


$page = isset($_GET['page']) ? (int)$_GET['page'] : 1;

also,


if(!$offset){
  $limit = " LIMIT 0," . $rowsPerPage;
} else {
  $limit = " LIMIT " . $offset . "," . $rowsPerPage;
}

can be simplified to


$limit = " LIMIT " . $offset . "," . $rowsPerPage;

Think about it

RNEL · August 7, 2010, 4:53pm

ok thanks for trying out my suggestions… but please don’t be afraid

I’ll try to explain a little about your code and hopefully it could enlighten you a bit more on how your code should work.

I rewrite your code and this is how I break it down.

first you need to get the current page

$pageNum = (int) $_GET['page'];

set default page to 1 if current page is equal to zero

$pageNum = (!$pageNum ? 1 : $pageNum);

set to how many rows per page you like

$rowsPerPage = 3;

calculate the offset value

$offset = ($pageNum-1) * $rowsPerPage;

set the limit statement based on your $offset calculation


	if(!$offset){
		$limit = " LIMIT 0," . $rowsPerPage;
	}
	else{
		$limit = " LIMIT " . $offset . "," . $rowsPerPage;
	}

build your sql statement


$sql = "SELECT COUNT(*) AS total FROM " . $prev . "property " . $MyQuery . " ORDER BY price ASC" . $limit;

query your sql statement


$r2 = mysql_query($sql);

get the total records


$total = @mysql_result($r2,0,"total");

call your pagination function


echo "<p class='paging'>";
echo  paging3($rowsPerPage,$total,$pageNum);
echo "</p>";

Those are the small parts of your code that are needed to show the right pagination format you want.

On the last code you posted, replace everything after

// Paging for search results

with this


// Paging for search results
	$pageNum = (int) $_GET['page']; //get page
	$pageNum = (!$pageNum ? 1 : $pageNum); //set default to 1 if page is not set
	$rowsPerPage = 3; //declaire rows per page
	$offset = ($pageNum-1) * $rowsPerPage; //calculate offset

	if(!$offset){
		$limit = " LIMIT 0," . $rowsPerPage;
	}
	else{
		$limit = " LIMIT " . $offset . "," . $rowsPerPage;
	}
	
	$sql = "SELECT COUNT(*) AS total FROM " . $prev . "property " . $MyQuery . " ORDER BY price ASC" . $limit;
	$r2 = mysql_query($sql);
	$total = @mysql_result($r2,0,"total");

   //Show paging
   echo "<p class='paging'>";
   echo  paging3($rowsPerPage,$total,$pageNum);
   echo "</p>";

Though It still has some things to look up to like comparing total number of pages against the given page, but at least for now that should give you your desired result.

ColinHughes · August 12, 2010, 10:25pm

Joseph

How would you like me to provide you with my Dataset?

Colin

sutabi · August 12, 2010, 8:10pm

Unless I have your dataset, cant really help.

However I did notice you have:


//-- Get Total

$where");
$result = mysql_query("SELECT COUNT(*) AS total FROM test $where");

Whats ‘$where");’ doing on the line above?

ColinHughes · August 12, 2010, 8:06pm

Hi Joseph

No errors, it just returns the wrong results.

For example a search for Apartments will return 3 pages of results. Click on the link for further pages and you are presented with all the Apartments AND the Villas in the table.

???

Colin

ColinHughes · August 12, 2010, 1:26pm

Sorry Joseph but that does not work for me!!

I have even gone to the stage where I have created a test just for this. My coding and DB for this here:

Database “form”:

-- phpMyAdmin SQL Dump
-- version 3.3.1
-- http://www.phpmyadmin.net
--
-- Host: localhost
-- Generation Time: Aug 12, 2010 at 03:17 PM
-- Server version: 5.1.47
-- PHP Version: 5.3.2

SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";


/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;

--
-- Database: `form`
--

-- --------------------------------------------------------

--
-- Table structure for table `test`
--

CREATE TABLE IF NOT EXISTS `test` (
  `city` varchar(50) NOT NULL,
  `property_type` varchar(50) NOT NULL,
  `id` int(10) NOT NULL AUTO_INCREMENT,
  `summary` varchar(100) NOT NULL,
  `status` text NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=14 ;

--
-- Dumping data for table `test`
--

INSERT INTO `test` (`city`, `property_type`, `id`, `summary`, `status`) VALUES
('Ashford', 'Apartment', 1, 'Apartment Ashford', 'Y'),
('Dover', 'Apartment', 2, 'Apartment Dover', 'Y'),
('Canterbury', 'Apartment', 3, 'Apartment Canterbury', 'Y'),
('Felixstowe', 'Apartment', 4, 'Apartment Felixstowe', 'Y'),
('Burnley', 'Apartment', 5, 'Apartment Burnley', 'Y'),
('Bristol', 'Apartment', 6, 'Apartment Bristol', 'Y'),
('Cowdenbeath', 'Apartment', 7, 'Apartment Cowdenbeath', 'Y'),
('Bristol', 'Villa', 8, 'Villa Bristol', 'Y'),
('Canterbury', 'Villa', 9, 'Villa Canterbury', 'Y'),
('New York', 'Villa', 10, 'New York Villa', 'Y'),
('Madrid', 'Villa', 11, 'Madrid Villa', 'Y'),
('Sydney', 'Villa', 12, 'Sydney Villa', 'Y'),
('London', 'Villa', 13, 'London Villa', 'Y');

Submit Page:

&lt;!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"&gt;
&lt;html xmlns="http://www.w3.org/1999/xhtml"&gt;
&lt;head&gt;
&lt;meta http-equiv="Content-Type" content="text/html; charset=utf-8" /&gt;
&lt;title&gt;Form Test&lt;/title&gt;



&lt;link href="css/main.css" rel="stylesheet" type="text/css" /&gt;
&lt;/head&gt;

&lt;body&gt;
&lt;/body&gt;

&lt;!-- Start Search Bar --&gt;    
    
       &lt;div id="search-bar"&gt;
       &lt;form method="post" action="property/index.html"&gt;
       
        &lt;span class="search-text"&gt;Property Search:&lt;/span&gt;
       
        Location:
        
        
&lt;select name="city"&gt;
&lt;option value=""&gt;No Preference&lt;/option&gt;
&lt;option value="Ashford"&gt;Ashford&lt;/option&gt;
&lt;option value="Bristol"&gt;Bristol&lt;/option&gt;
&lt;option value="Burnley"&gt;Burnley&lt;/option&gt;
&lt;option value="Canterbury"&gt;Canterbury&lt;/option&gt;
&lt;option value="Cowdenbeath"&gt;Cowdenbeath&lt;/option&gt;
&lt;option value="Dover"&gt;Dover&lt;/option&gt;
&lt;option value="Felixstowe"&gt;Felixstowe&lt;/option&gt;
&lt;option value="London"&gt;London&lt;/option&gt;
&lt;option value="Madrid"&gt;Madrid&lt;/option&gt;
&lt;option value="New York"&gt;New York&lt;/option&gt;
&lt;option value="Sydney"&gt;Sydney&lt;/option&gt;
&lt;/select&gt;
        
        Property Type:
        
        
&lt;select name="property_type"&gt;
&lt;option value=""&gt;No Preference&lt;/option&gt;        
&lt;option value="Apartment"&gt;Apartment&lt;/option&gt;
&lt;option value="Villa"&gt;Villa&lt;/option&gt;
&lt;/select&gt;
        &lt;input type="image" name="submit" class="btn" src="images/go_button_orange.gif" alt="Search Properties" /&gt;
        
         &lt;/form&gt;
       
       &lt;/div&gt;
    
    
  

&lt;/html&gt;

Results page:

&lt;?php

include("../includes/conn.php");



//------------------------------------------------------------------------------

//-- Pagination Class

//------------------------------------------------------------------------------

class paginate 

{

    static function render($params, $current_page, $max_pages, $pages, $url = '')

    {

        if(!empty($url))

        {

            $url = explode('?', $url);

            $url = $url[0];

        }

        unset($params['p']);

        $query = http_build_query($params);

        $query = strlen($query) ? '&' . $query : '';

        $paginated = '';

        //----------------------------------------------------------------------

        //-- Previous Pages

        //----------------------------------------------------------------------

        if ($current_page &gt; 0)

        {

            $paginated .= '&lt;a class="pagenumber" href="' . $url . '?p=0' . $query . '"&gt;First Page&lt;/a&gt;';

            $pagi_limit = $current_page - $max_pages;

            $pagi_limit = ($pagi_limit &gt; 0) ? $pagi_limit : 0;

            foreach(range($pagi_limit, ($current_page - 1)) as $i)

            {

                $paginated .= ' &lt;a class="pagenumber" href="' . $url . '?p=' . $i . $query . '"&gt;' . ($i + 1) . '&lt;/a&gt; ';

            }

        }

        //----------------------------------------------------------------------

        //-- Current Pages

        //----------------------------------------------------------------------

        $paginated .= ' &lt;strong class="pagenumberhighlight"&gt;' . ($current_page + 1) . '&lt;/strong&gt; ';

        //----------------------------------------------------------------------

        //-- Next Pages

        //----------------------------------------------------------------------

        if ($current_page &lt; $pages)

        {

            $pagi_limit = $current_page + $max_pages;

            $pagi_limit = ($pagi_limit &lt; $pages) ? $pagi_limit : $pages;

            for($i = $current_page + 1; $i &lt; $pagi_limit; $i++)

            {

                $paginated .= ' &lt;a class="pagenumber" href="' . $url . '?p=' . $i . $query . '"&gt;' . ($i + 1) . '&lt;/a&gt; ';

            }

            if($current_page + 1 != $pagi_limit) 

            {

                $paginated .= ' &lt;a class="pagenumber" href="' . $url . '?p=' . ($pages - 1) . $query . '"&gt;Last Page&lt;/a&gt; ';

            }

        }

        return $paginated;

    }

}



//------------------------------------------------------------------------------

//-- Pagination Settings

//------------------------------------------------------------------------------

$current_page = (isset($_REQUEST['p'])) ? intval($_REQUEST['p']) : 0;

$per_page = (isset($_REQUEST['perpg'])) ? intval($_REQUEST['perpg']) : 3;

$max_pages = 5; // only should 5 pages left or right



//------------------------------------------------------------------------------

//-- Wheres

//------------------------------------------------------------------------------

$where = array();



if(!empty($_POST['city']))

{

    // Allow only alphabetic characters

    $param = preg_replace('/[^a-zA-Z ]/', '', $_POST['city']);  

    $param = mysql_real_escape_string($param);

    $where []= "city = '$param'"; 

}



if(!empty($_POST['property_type']))

{

    // Allow only alphabetic characters

    $param = preg_replace('/[^a-zA-Z ]/', '', $_POST['property_type']);  

    $param = mysql_real_escape_string($param);

    $where []= "property_type = '$param'";

}



$where = (!empty($where)) ? ' WHERE ' . implode(' AND ', $where) : '';



//------------------------------------------------------------------------------

//-- Orders

//------------------------------------------------------------------------------

$by = ($_GET['by'] == 'asc') ? 'ASC' : 'DESC';

switch($_GET['order']) {

   case 'city':

        $order = 'city';

        break;

    default:

       
        $order = 'id';
        $by = 'DESC';

        break;

}



if($order and $by) {

    $orderby = 'ORDER BY ' . $order . ' ' . $by;

}



//------------------------------------------------------------------------------

//-- Get Properties

//------------------------------------------------------------------------------

$limit_start = ($current_page * $per_page);

$limit_end = $per_page;


$result = mysql_query("SELECT * FROM test
    $where 

    $orderby 

    LIMIT $limit_start, $limit_end"

);

if (!$result) {

    echo 'Invalid query: ', mysql_error(), ' &lt;br /&gt;';

}

    

$properties = array();

while ($property = mysql_fetch_assoc($result))

{

    $properties []= $property;

}



//-- Get Total

 $where");
$result = mysql_query("SELECT COUNT(*) AS total FROM test $where");
    

$total = array();

while($row = mysql_fetch_assoc($result))

{

    $total = $row;

}

$total = $total['total'];

$pages = ceil($total / $per_page);



//------------------------------------------------------------------------------

//-- Get Pagination

//------------------------------------------------------------------------------

$pages = paginate::render($_GET, $current_page, $max_pages, $pages);



?&gt;



&lt;!-- Pagination --&gt;

&lt;?php echo $pages ?&gt;



&lt;?php foreach($properties as $property) { ?&gt;

    &lt;p&gt;&lt;?php echo htmlentities($property['summary']) ?&gt;&lt;/p&gt;

&lt;?php } ?&gt;



&lt;!-- Pagination --&gt;

&lt;?php echo $pages ?&gt;

Regards

Colin

sutabi · August 11, 2010, 8:29pm

Colin,

Here is the updated script, pagination works just fine for me. If you are getting errors let me know.

<?php
include("includes/conn.php");

//------------------------------------------------------------------------------
//-- Pagination Class
//------------------------------------------------------------------------------
class paginate 
{
    static function render($params, $current_page, $max_pages, $pages, $url = '')
    {
        if(!empty($url))
        {
            $url = explode('?', $url);
            $url = $url[0];
        }
        unset($params['p']);
        $query = http_build_query($params);
        $query = strlen($query) ? '&' . $query : '';
        $paginated = '';
        //----------------------------------------------------------------------
        //-- Previous Pages
        //----------------------------------------------------------------------
        if ($current_page > 0)
        {
            $paginated .= '<a class="pagenumber" href="' . $url . '?p=0' . $query . '">First Page</a>';
            $pagi_limit = $current_page - $max_pages;
            $pagi_limit = ($pagi_limit > 0) ? $pagi_limit : 0;
            foreach(range($pagi_limit, ($current_page - 1)) as $i)
            {
                $paginated .= ' <a class="pagenumber" href="' . $url . '?p=' . $i . $query . '">' . ($i + 1) . '</a> ';
            }
        }
        //----------------------------------------------------------------------
        //-- Current Pages
        //----------------------------------------------------------------------
        $paginated .= ' <strong class="pagenumberhighlight">' . ($current_page + 1) . '</strong> ';
        //----------------------------------------------------------------------
        //-- Next Pages
        //----------------------------------------------------------------------
        if ($current_page < $pages)
        {
            $pagi_limit = $current_page + $max_pages;
            $pagi_limit = ($pagi_limit < $pages) ? $pagi_limit : $pages;
            for($i = $current_page + 1; $i < $pagi_limit; $i++)
            {
                $paginated .= ' <a class="pagenumber" href="' . $url . '?p=' . $i . $query . '">' . ($i + 1) . '</a> ';
            }
            if($current_page + 1 != $pagi_limit) 
            {
                $paginated .= ' <a class="pagenumber" href="' . $url . '?p=' . ($pages - 1) . $query . '">Last Page</a> ';
            }
        }
        return $paginated;
    }
}

//------------------------------------------------------------------------------
//-- Pagination Settings
//------------------------------------------------------------------------------
$current_page = (isset($_REQUEST['p'])) ? intval($_REQUEST['p']) : 0;
$per_page = (isset($_REQUEST['perpg'])) ? intval($_REQUEST['perpg']) : 10;
$max_pages = 5; // only should 5 pages left or right

//------------------------------------------------------------------------------
//-- Wheres
//------------------------------------------------------------------------------
$where = array();

if(!empty($_POST['city']))
{
    // Allow only alphabetic characters
    $param = preg_replace('/[^a-zA-Z ]/', '', $_POST['city']);  
    $param = mysql_real_escape_string($param);
    $where []= "city = '$param'"; 
}

if(!empty($_POST['property_type']))
{
    // Allow only alphabetic characters
    $param = preg_replace('/[^a-zA-Z ]/', '', $_POST['property_type']);  
    $param = mysql_real_escape_string($param);
    $where []= "property_type = '$param'";
}

$where = (!empty($where)) ? ' WHERE ' . implode(' AND ', $where) : '';

//------------------------------------------------------------------------------
//-- Orders
//------------------------------------------------------------------------------
$by = ($_GET['by'] == 'asc') ? 'ASC' : 'DESC';
switch($_GET['order']) {
   case 'city':
        $order = 'city';
        break;
    default:
        $order = 'propertyref';
        $by = 'DESC';
        break;
}

if($order and $by) {
    $orderby = 'ORDER BY ' . $order . ' ' . $by;
}

//------------------------------------------------------------------------------
//-- Get Properties
//------------------------------------------------------------------------------
$limit_start = ($current_page * $per_page);
$limit_end = $per_page;
$result = mysql_query("SELECT * FROM agent_property
    $where 
    $orderby 
    LIMIT $limit_start, $limit_end"
);
if (!$result) {
    echo 'Invalid query: ', mysql_error(), ' <br />';
}
    
$properties = array();
while ($property = mysql_fetch_assoc($result))
{
    $properties []= $property;
}

//-- Get Total
$result = mysql_query("SELECT COUNT(*) AS total FROM agent_property $where");
    
$total = array();
while($row = mysql_fetch_assoc($result))
{
    $total = $row;
}
$total = $total['total'];
$pages = ceil($total / $per_page);

//------------------------------------------------------------------------------
//-- Get Pagination
//------------------------------------------------------------------------------
$pages = paginate::render($_GET, $current_page, $max_pages, $pages);

?>

<!-- Pagination -->
<?php echo $pages ?>

<?php foreach($properties as $property) { ?>
    <p><?php echo htmlentities($property['summary']) ?></p>
<?php } ?>

<!-- Pagination -->
<?php echo $pages ?>

ColinHughes · August 11, 2010, 5:26pm

Thanks for the coding. I now think the problem actually lies with the DB Schema. (I did not design this) because the results are the same

&lt;pma:table name="agent_property"&gt;
                CREATE TABLE `agent_property` (
                  `id` int(10) NOT NULL AUTO_INCREMENT,
                  `propertyref` varchar(30) CHARACTER SET utf8 NOT NULL,
                  `location` varchar(150) CHARACTER SET utf8 DEFAULT NULL,
                  `summary` mediumtext CHARACTER SET utf8,
                  `description` mediumtext CHARACTER SET utf8,
                  `parking` varchar(200) CHARACTER SET utf8 DEFAULT NULL,
                  `bedrooms` int(15) DEFAULT NULL,
                  `bathrooms` int(15) DEFAULT NULL,
                  `price` int(40) DEFAULT NULL,
                  `reduced` int(40) DEFAULT NULL,
                  `pricefrom` enum('1','0') CHARACTER SET utf8 DEFAULT '0',
                  `newdev` enum('1','0') CHARACTER SET utf8 DEFAULT '0',
                  `featured` enum('Y','N') CHARACTER SET utf8 DEFAULT 'N',
                  `date_add` date DEFAULT NULL,
                  `status` enum('Y','N') CHARACTER SET utf8 DEFAULT 'Y',
                  `cat_id` int(4) NOT NULL DEFAULT '1',
                  `country` varchar(100) CHARACTER SET utf8 DEFAULT NULL,
                  `remark` text CHARACTER SET utf8 NOT NULL,
                  `p_type` enum('F','U') CHARACTER SET utf8 NOT NULL DEFAULT 'F',
                  `city` varchar(50) CHARACTER SET utf8 NOT NULL,
                  `geo` varchar(20) CHARACTER SET utf8 DEFAULT NULL,
                  `property_type` varchar(200) CHARACTER SET utf8 NOT NULL,
                  `orientation` varchar(50) CHARACTER SET utf8 NOT NULL,
                  `plot` int(45) DEFAULT NULL,
                  `terrace` int(45) NOT NULL,
                  `build` int(45) DEFAULT NULL,
                  `pool` enum('1','0') CHARACTER SET utf8 DEFAULT '0',
                  `name` varchar(30) CHARACTER SET utf8 NOT NULL,
                  `address` varchar(120) CHARACTER SET utf8 NOT NULL,
                  `tel` varchar(15) CHARACTER SET utf8 NOT NULL,
                  `mobile` varchar(20) CHARACTER SET utf8 DEFAULT NULL,
                  `email` varchar(25) CHARACTER SET utf8 NOT NULL,
                  `agency_status` enum('Y','N') CHARACTER SET utf8 NOT NULL DEFAULT 'Y',
                  `joint_name` varchar(30) CHARACTER SET utf8 DEFAULT NULL,
                  `occ_status` enum('Y','N','U') CHARACTER SET utf8 NOT NULL,
                  `mort_status` enum('Y','N') CHARACTER SET utf8 DEFAULT 'N',
                  `key_status` enum('Y','N') CHARACTER SET utf8 DEFAULT 'N',
                  `date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
                  PRIMARY KEY (`id`),
                  UNIQUE KEY `key1` (`propertyref`),
                  FULLTEXT KEY `propertyref` (`propertyref`)
                ) ENGINE=MyISAM AUTO_INCREMENT=168 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
            &lt;/pma:table&gt;

I hope the above gives an indication of where things are wrong.

Thanks for your patience

Colin

ColinHughes · August 11, 2010, 2:26pm

Well it is reassuring to know that I am not alone with this problem that is causing such grief.

Hopefully somebody will find the cause and solution to this.

Colin

johnargon · August 11, 2010, 1:03pm

anyone can solve this problem? I also have the same problem with the paging

sutabi · August 12, 2010, 10:32pm

You can email the dataset, check your PM

ColinHughes · August 9, 2010, 2:06pm

OK so I have created a few simple pages independant of my site to work on this. Code below:

Page with search form:



<?php

/********************************** Location Menu ***********************************/

$locationmenu = "<select name='city' id='city' class='selectbox-location'>\
\	
                 <option value=\\"\\">No Preference</option>\
\	";

$q1 = "select distinct city from location order by id";
$r1 = mysql_query($q1) or die(mysql_error());
if(mysql_num_rows($r1) > '0')
{
    while($a1 = mysql_fetch_array($r1))
    {
        $locationmenu .= "<option value=\\"$a1[city]\\">$a1[city]</option>\
\	";
    }
}
$locationmenu .= "</select>\
";


/********************************** County Menu ***********************************/

$countymenu = "<select name='county' id='county' class='selectbox-location'>\
\	
                 <option value=\\"\\">No Preference</option>\
\	";

$q1 = "select distinct county from location order by id";
$r1 = mysql_query($q1) or die(mysql_error());
if(mysql_num_rows($r1) > '0')
{
    while($a1 = mysql_fetch_array($r1))
    {
        $countymenu .= "<option value=\\"$a1[county]\\">$a1[county]</option>\
\	";
    }
}
$countymenu .= "</select>\
";
  
?>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Form Test</title>



<link href="css/main.css" rel="stylesheet" type="text/css" />
</head>

<body>
</body>

<!-- Start Search Bar -->    
    
       <div id="search-bar">
       <form method="post" action="property/index.html">
       
        <span class="search-text">Property Search:</span>
       
        Location:
        <?php echo $locationmenu?>
        
        County:
        <?php echo $countymenu?>
        
        
        <input type="image" name="submit" class="btn" src="images/go_button_orange.gif" alt="Search Properties" />
        
         </form>
       
       </div>
    
    
    <!-- End Header -->

</html>

Result page:

/

<?php

**********************************  Paging Function  ***********************************/
 
function paging($rowsPerPage,$numrows,$pageNum=1,$param="")
{
    global $db; 
   // by default we show first page
   
   // how many pages we have when using paging?
   $maxPage = ceil($numrows/$rowsPerPage);
   $self = "index.html";
  
   
  // on page one 
   if ($pageNum > 1)
   {
      $page = $pageNum - 1;
      $previous = "<a href='" . $self . "?page" . $page . "'>[Prev]</a>";
        $first = "<a href='" . $self . "1" . "'>[First Page]</a>";
   }
   else
   {
      $previous  = "";  // we're on page one, don't enable 'previous' link
      $first = ""; // nor 'first page' link
   }
   // print 'next' link only if we're not
   // on the last page
   if($pageNum < $maxPage)
   {
      $page = $pageNum + 1;
      $next = " <a href='" . $self . "?page" . $page . "'>[Next]</a> ";
      $last = " <a href='" . $self . "?page" . $maxPage . "'>[Last Page]</a> ";
   }
   else
   {
      $next = '  ';      // we're on the last page, don't enable 'next' link
      $last = '  '; // nor 'last page' link
   }
   // print the page navigation link
   // counting the offset
   return  $first . " " .  $previous . " Showing page <strong>" . $pageNum . "</strong> of <strong>" . $maxPage . "</strong> pages   " . $next . " " .  $last;
}




/*================= Build Search Query From Form Submission ====================================*/

 
$query = array();


if(!empty($_POST['county']))
{
     
    // Allow only alphabetic characters
    
    $result = preg_replace('/[^a-zA-Z ]/', '', $_POST['county']);  
    $query[] = "county = '$result'";  
}


if(!empty($_POST['city']))
{

    // Allow only alphabetic characters
    
    $result = preg_replace('/[^a-zA-Z ]/', '', $_POST['city']);  
    $query[] = "city = '$result'"; 
}

if(!empty($query))

{
    $MyQuery = implode(" AND ", $query);
    $MyQuery = "WHERE ".$MyQuery; 

}



// Paging for search results

    $pageNum = (int) $_GET['page']; //get page

    $pageNum = (!$pageNum ? 1 : $pageNum); //set default to 1 if page is not set

    $rowsPerPage = 3; //declare rows per page

    $offset = ($pageNum-1) * $rowsPerPage; //calculate offset



    if(!$offset){

        $limit = " LIMIT 0," . $rowsPerPage;

    }

    else{

        $limit = " LIMIT " . $offset . "," . $rowsPerPage;

    }

    

    $sql = "SELECT COUNT(*) AS total FROM test.location " . $MyQuery . " ORDER BY id ASC" . $limit;

    $r2 = mysql_query($sql);

    $total = @mysql_result($r2,0,"total");

?>


<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Form Test</title>



<link href="../css/main.css" rel="stylesheet" type="text/css" />
</head>

<body>
</body>

    
    
    
    <?php
  
  

     $r = mysql_query("SELECT *  FROM location " . $MyQuery . " ORDER BY id ASC limit " . $offset . "," .  $rowsPerPage);
    
    while ( $row = mysql_fetch_array($r) ) {
    
    
      echo $row["city"] ; 
      echo "<br />";
      
     
  }
 
  
   //Show paging

   echo "<p class='paging'>";

   echo  paging($rowsPerPage,$total,$pageNum);

   echo "</p>";



?>

</html>

Returns result EG 1 of 3 pages click on page 2 all results in table returned.

thanks

Colin

ColinHughes · August 14, 2010, 8:28pm

This is driving me nuts!! Thanks to those who have added valuable input so far.

Has anybody got the time to run this on their test box and see if you get the same problem.

[LEFT][COLOR=#808080][I]-- phpMyAdmin SQL Dump[/I][/COLOR]
[COLOR=#808080][I]-- version 3.3.1[/I][/COLOR]
[COLOR=#808080][I]-- http://www.phpmyadmin.net[/I][/COLOR]
[COLOR=#808080][I]--[/I][/COLOR]
[COLOR=#808080][I]-- Host: localhost[/I][/COLOR]
[COLOR=#808080][I]-- Generation Time: Aug 12, 2010 at 03:17 PM[/I][/COLOR]
[COLOR=#808080][I]-- Server version: 5.1.47[/I][/COLOR]
[COLOR=#808080][I]-- PHP Version: 5.3.2[/I][/COLOR]
 

[COLOR=#993333][B]SET[/B][/COLOR] SQL_MODE=[COLOR=#FF0000]"NO_AUTO_VALUE_ON_ZERO"[/COLOR];
 

 

[COLOR=#808080][I]/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */[/I][/COLOR];
[COLOR=#808080][I]/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */[/I][/COLOR];
[COLOR=#808080][I]/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */[/I][/COLOR];
[COLOR=#808080][I]/*!40101 SET NAMES utf8 */[/I][/COLOR];
 

[COLOR=#808080][I]--[/I][/COLOR]
[COLOR=#808080][I]-- Database: `form`[/I][/COLOR]
[COLOR=#808080][I]--[/I][/COLOR]
 

[COLOR=#808080][I]-- --------------------------------------------------------[/I][/COLOR]
 

[COLOR=#808080][I]--[/I][/COLOR]
[COLOR=#808080][I]-- Table structure for table `test`[/I][/COLOR]
[COLOR=#808080][I]--[/I][/COLOR]
 

[COLOR=#993333][B]CREATE TABLE[/B][/COLOR] IF [COLOR=#AA3399][B]NOT[/B][/COLOR] [COLOR=#993333][B]EXISTS[/B][/COLOR] `test` [COLOR=#66CC66]([/COLOR]
  `city` [COLOR=#AA9933][B]VARCHAR[/B][/COLOR][COLOR=#66CC66]([/COLOR][COLOR=#CC66CC]50[/COLOR][COLOR=#66CC66])[/COLOR] [COLOR=#AA3399][B]NOT NULL[/B][/COLOR],
  `property_type` [COLOR=#AA9933][B]VARCHAR[/B][/COLOR][COLOR=#66CC66]([/COLOR][COLOR=#CC66CC]50[/COLOR][COLOR=#66CC66])[/COLOR] [COLOR=#AA3399][B]NOT NULL[/B][/COLOR],
  `id` [COLOR=#AA9933][B]INT[/B][/COLOR][COLOR=#66CC66]([/COLOR][COLOR=#CC66CC]10[/COLOR][COLOR=#66CC66])[/COLOR] [COLOR=#AA3399][B]NOT NULL[/B][/COLOR] [COLOR=#AA3399][B]AUTO_INCREMENT[/B][/COLOR],
  `summary` [COLOR=#AA9933][B]VARCHAR[/B][/COLOR][COLOR=#66CC66]([/COLOR][COLOR=#CC66CC]100[/COLOR][COLOR=#66CC66])[/COLOR] [COLOR=#AA3399][B]NOT NULL[/B][/COLOR],
  `status` [COLOR=#AA9933][B]TEXT[/B][/COLOR] [COLOR=#AA3399][B]NOT NULL[/B][/COLOR],
  [COLOR=#993333][B]PRIMARY KEY[/B][/COLOR] [COLOR=#66CC66]([/COLOR]`id`[COLOR=#66CC66])[/COLOR]
[COLOR=#66CC66])[/COLOR] ENGINE=[COLOR=#993333][B]INNODB[/B][/COLOR]  [COLOR=#AA3399][B]DEFAULT[/B][/COLOR] [COLOR=#AA3399][B]CHARSET[/B][/COLOR]=latin1 [COLOR=#AA3399][B]AUTO_INCREMENT[/B][/COLOR]=[COLOR=#CC66CC]14[/COLOR] ;
 

[COLOR=#808080][I]--[/I][/COLOR]
[COLOR=#808080][I]-- Dumping data for table `test`[/I][/COLOR]
[COLOR=#808080][I]--[/I][/COLOR]
 

[COLOR=#993333][B]INSERT[/B][/COLOR] [COLOR=#993333][B]INTO[/B][/COLOR] `test` [COLOR=#66CC66]([/COLOR]`city`, `property_type`, `id`, `summary`, `status`[COLOR=#66CC66])[/COLOR] [COLOR=#993333][B]VALUES[/B][/COLOR]
[COLOR=#66CC66]([/COLOR][COLOR=#FF0000]'Ashford'[/COLOR], [COLOR=#FF0000]'Apartment'[/COLOR], [COLOR=#CC66CC]1[/COLOR], [COLOR=#FF0000]'Apartment Ashford'[/COLOR], [COLOR=#FF0000]'Y'[/COLOR][COLOR=#66CC66])[/COLOR],
[COLOR=#66CC66]([/COLOR][COLOR=#FF0000]'Dover'[/COLOR], [COLOR=#FF0000]'Apartment'[/COLOR], [COLOR=#CC66CC]2[/COLOR], [COLOR=#FF0000]'Apartment Dover'[/COLOR], [COLOR=#FF0000]'Y'[/COLOR][COLOR=#66CC66])[/COLOR],
[COLOR=#66CC66]([/COLOR][COLOR=#FF0000]'Canterbury'[/COLOR], [COLOR=#FF0000]'Apartment'[/COLOR], [COLOR=#CC66CC]3[/COLOR], [COLOR=#FF0000]'Apartment Canterbury'[/COLOR], [COLOR=#FF0000]'Y'[/COLOR][COLOR=#66CC66])[/COLOR],
[COLOR=#66CC66]([/COLOR][COLOR=#FF0000]'Felixstowe'[/COLOR], [COLOR=#FF0000]'Apartment'[/COLOR], [COLOR=#CC66CC]4[/COLOR], [COLOR=#FF0000]'Apartment Felixstowe'[/COLOR], [COLOR=#FF0000]'Y'[/COLOR][COLOR=#66CC66])[/COLOR],
[COLOR=#66CC66]([/COLOR][COLOR=#FF0000]'Burnley'[/COLOR], [COLOR=#FF0000]'Apartment'[/COLOR], [COLOR=#CC66CC]5[/COLOR], [COLOR=#FF0000]'Apartment Burnley'[/COLOR], [COLOR=#FF0000]'Y'[/COLOR][COLOR=#66CC66])[/COLOR],
[COLOR=#66CC66]([/COLOR][COLOR=#FF0000]'Bristol'[/COLOR], [COLOR=#FF0000]'Apartment'[/COLOR], [COLOR=#CC66CC]6[/COLOR], [COLOR=#FF0000]'Apartment Bristol'[/COLOR], [COLOR=#FF0000]'Y'[/COLOR][COLOR=#66CC66])[/COLOR],
[COLOR=#66CC66]([/COLOR][COLOR=#FF0000]'Cowdenbeath'[/COLOR], [COLOR=#FF0000]'Apartment'[/COLOR], [COLOR=#CC66CC]7[/COLOR], [COLOR=#FF0000]'Apartment Cowdenbeath'[/COLOR], [COLOR=#FF0000]'Y'[/COLOR][COLOR=#66CC66])[/COLOR],
[COLOR=#66CC66]([/COLOR][COLOR=#FF0000]'Bristol'[/COLOR], [COLOR=#FF0000]'Villa'[/COLOR], [COLOR=#CC66CC]8[/COLOR], [COLOR=#FF0000]'Villa Bristol'[/COLOR], [COLOR=#FF0000]'Y'[/COLOR][COLOR=#66CC66])[/COLOR],
[COLOR=#66CC66]([/COLOR][COLOR=#FF0000]'Canterbury'[/COLOR], [COLOR=#FF0000]'Villa'[/COLOR], [COLOR=#CC66CC]9[/COLOR], [COLOR=#FF0000]'Villa Canterbury'[/COLOR], [COLOR=#FF0000]'Y'[/COLOR][COLOR=#66CC66])[/COLOR],
[COLOR=#66CC66]([/COLOR][COLOR=#FF0000]'New York'[/COLOR], [COLOR=#FF0000]'Villa'[/COLOR], [COLOR=#CC66CC]10[/COLOR], [COLOR=#FF0000]'New York Villa'[/COLOR], [COLOR=#FF0000]'Y'[/COLOR][COLOR=#66CC66])[/COLOR],
[COLOR=#66CC66]([/COLOR][COLOR=#FF0000]'Madrid'[/COLOR], [COLOR=#FF0000]'Villa'[/COLOR], [COLOR=#CC66CC]11[/COLOR], [COLOR=#FF0000]'Madrid Villa'[/COLOR], [COLOR=#FF0000]'Y'[/COLOR][COLOR=#66CC66])[/COLOR],
[COLOR=#66CC66]([/COLOR][COLOR=#FF0000]'Sydney'[/COLOR], [COLOR=#FF0000]'Villa'[/COLOR], [COLOR=#CC66CC]12[/COLOR], [COLOR=#FF0000]'Sydney Villa'[/COLOR], [COLOR=#FF0000]'Y'[/COLOR][COLOR=#66CC66])[/COLOR],
[COLOR=#66CC66]([/COLOR][COLOR=#FF0000]'London'[/COLOR], [COLOR=#FF0000]'Villa'[/COLOR], [COLOR=#CC66CC]13[/COLOR], [COLOR=#FF0000]'London Villa'[/COLOR], [COLOR=#FF0000]'Y'[/COLOR][COLOR=#66CC66])[/COLOR];
[/LEFT]

Submit.php

&lt;?php include("includes/conn.php");
?&gt;


&lt;!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"&gt;
&lt;html xmlns="http://www.w3.org/1999/xhtml"&gt;
&lt;head&gt;
&lt;meta http-equiv="Content-Type" content="text/html; charset=utf-8" /&gt;
&lt;title&gt;Form Test&lt;/title&gt;



&lt;link href="css/main.css" rel="stylesheet" type="text/css" /&gt;
&lt;/head&gt;

&lt;body&gt;
&lt;/body&gt;

&lt;!-- Start Search Bar --&gt;    
    
       &lt;div id="search-bar"&gt;
       &lt;form method="post" action="results.php"&gt;
       
        &lt;span class="search-text"&gt;Property Search:&lt;/span&gt;-
       
        Location:
        
        
&lt;select name="city"&gt;
&lt;option value=""&gt;No Preference&lt;/option&gt;
&lt;option value="Ashford"&gt;Ashford&lt;/option&gt;
&lt;option value="Bristol"&gt;Bristol&lt;/option&gt;
&lt;option value="Burnley"&gt;Burnley&lt;/option&gt;
&lt;option value="Canterbury"&gt;Canterbury&lt;/option&gt;
&lt;option value="Cowdenbeath"&gt;Cowdenbeath&lt;/option&gt;
&lt;option value="Dover"&gt;Dover&lt;/option&gt;
&lt;option value="Felixstowe"&gt;Felixstowe&lt;/option&gt;
&lt;option value="London"&gt;London&lt;/option&gt;
&lt;option value="Madrid"&gt;Madrid&lt;/option&gt;
&lt;option value="New York"&gt;New York&lt;/option&gt;
&lt;option value="Sydney"&gt;Sydney&lt;/option&gt;
&lt;/select&gt;
        
        Property Type:
       
        
&lt;select name="property_type"&gt;
&lt;option value=""&gt;No Preference&lt;/option&gt;        
&lt;option value="Apartment"&gt;Apartment&lt;/option&gt;
&lt;option value="Villa"&gt;Villa&lt;/option&gt;
&lt;/select&gt;
        &lt;input type="image" name="submit" class="btn" src="images/go_button_orange.gif" alt="Search Properties" /&gt;
        
         &lt;/form&gt;
       
       &lt;/div&gt;
    
    
    &lt;!-- End Header --&gt;

&lt;/html&gt;

Results.php

&lt;?php

include("includes/conn.php");



//------------------------------------------------------------------------------

//-- Pagination Class

//------------------------------------------------------------------------------

class paginate 

{

    static function render($params, $current_page, $max_pages, $pages, $url = '')

    {

        if(!empty($url))

        {

            $url = explode('?', $url);

            $url = $url[0];

        }

        unset($params['p']);

        $query = http_build_query($params);

        $query = strlen($query) ? '&' . $query : '';

        $paginated = '';

        //----------------------------------------------------------------------

        //-- Previous Pages

        //----------------------------------------------------------------------

        if ($current_page &gt; 0)

        {

            $paginated .= '&lt;a class="pagenumber" href="' . $url . '?p=0' . $query . '"&gt;First Page&lt;/a&gt;';

            $pagi_limit = $current_page - $max_pages;

            $pagi_limit = ($pagi_limit &gt; 0) ? $pagi_limit : 0;

            foreach(range($pagi_limit, ($current_page - 1)) as $i)

            {

                $paginated .= ' &lt;a class="pagenumber" href="' . $url . '?p=' . $i . $query . '"&gt;' . ($i + 1) . '&lt;/a&gt; ';

            }

        }

        //----------------------------------------------------------------------

        //-- Current Pages

        //----------------------------------------------------------------------

        $paginated .= ' &lt;strong class="pagenumberhighlight"&gt;' . ($current_page + 1) . '&lt;/strong&gt; ';

        //----------------------------------------------------------------------

        //-- Next Pages

        //----------------------------------------------------------------------

        if ($current_page &lt; $pages)

        {

            $pagi_limit = $current_page + $max_pages;

            $pagi_limit = ($pagi_limit &lt; $pages) ? $pagi_limit : $pages;

            for($i = $current_page + 1; $i &lt; $pagi_limit; $i++)

            {

                $paginated .= ' &lt;a class="pagenumber" href="' . $url . '?p=' . $i . $query . '"&gt;' . ($i + 1) . '&lt;/a&gt; ';

            }

            if($current_page + 1 != $pagi_limit) 

            {

                $paginated .= ' &lt;a class="pagenumber" href="' . $url . '?p=' . ($pages - 1) . $query . '"&gt;Last Page&lt;/a&gt; ';

            }

        }

        return $paginated;

    }

}



//------------------------------------------------------------------------------

//-- Pagination Settings

//------------------------------------------------------------------------------

$current_page = (isset($_REQUEST['p'])) ? intval($_REQUEST['p']) : 0;

$per_page = (isset($_REQUEST['perpg'])) ? intval($_REQUEST['perpg']) : 3;

$max_pages = 5; // only should 5 pages left or right



//------------------------------------------------------------------------------

//-- Wheres

//------------------------------------------------------------------------------

$where = array();



if(!empty($_POST['city']))

{

    // Allow only alphabetic characters

    $param = preg_replace('/[^a-zA-Z ]/', '', $_POST['city']);  

    $param = mysql_real_escape_string($param);

    $where []= "city = '$param'"; 

}



if(!empty($_POST['property_type']))

{

    // Allow only alphabetic characters

    $param = preg_replace('/[^a-zA-Z ]/', '', $_POST['property_type']); 


    $param = mysql_real_escape_string($param);

    $where []= "property_type = '$param'";

}



$where = (!empty($where)) ? ' WHERE ' . implode(' AND ', $where) : '';



//------------------------------------------------------------------------------

//-- Orders

//------------------------------------------------------------------------------

$by = ($_GET['by'] == 'asc') ? 'ASC' : 'DESC';

switch($_GET['order']) {

   case 'city':

        $order = 'city';

        break;

    default:


        $order = 'id';
        $by = 'DESC';

        break;

}



if($order and $by) {

    $orderby = 'ORDER BY ' . $order . ' ' . $by;

}



//------------------------------------------------------------------------------

//-- Get Properties

//------------------------------------------------------------------------------

$limit_start = ($current_page * $per_page);

$limit_end = $per_page;


$result = mysql_query("SELECT * FROM test
    $where 

    $orderby 

    LIMIT $limit_start, $limit_end"

);



if (!$result) {

    echo 'Invalid query: ', mysql_error(), ' &lt;br /&gt;';

}

    

$properties = array();



    
    
while ($property = mysql_fetch_assoc($result))

{

    $properties []= $property;

}


//-- Get Total


$result = mysql_query("SELECT COUNT(*) AS total FROM test $where");
    

$total = array();

while($row = mysql_fetch_assoc($result))

{

    $total = $row;

}

$total = $total['total'];

$pages = ceil($total / $per_page);



//------------------------------------------------------------------------------

//-- Get Pagination

//------------------------------------------------------------------------------

$pages = paginate::render($_GET, $current_page, $max_pages, $pages);



?&gt;



&lt;!-- Pagination --&gt;

&lt;?php echo $pages ?&gt;



&lt;?php foreach($properties as $property) { ?&gt;

    &lt;p&gt;&lt;?php echo htmlentities($property['summary']) ?&gt;&lt;/p&gt;

&lt;?php } ?&gt;



&lt;!-- Pagination --&gt;

&lt;?php echo $pages ?&gt;

In desperation and frustration
Colin

RNEL · August 8, 2010, 4:53pm

hi ScallioXTX,
I wasn’t really aware about the first code, I will always consider that from now on. How could I’ve missed the second one, thank you !

hi Colin,
Thank you for giving more informations.

I was thinking now that the parameters needed are not being transmitted to the succeeding pages which resulted of having different SQL statements from page 1 and page 2 .

Please show off you HTML code and let’s see how it goes there. Can you also please use $_GET method, it’s much more constructive to carry out for search pages.

ColinHughes · August 12, 2010, 8:44pm

sutabi:

Unless I have your dataset, cant really help.

However I did notice you have:
//-- Get Total

$where");
$result = mysql_query("SELECT COUNT(*) AS total FROM test $where");
Whats ‘$where");’ doing on the line above?

Sorry that was a “left over” from cut and paste of your paging code for use with the new test site.

Contents of ARRAY on page 1:

“”Array ( [0] => Array ( [city] => Cowdenbeath [property_type] => Apartment [id] => 7 [summary] => Apartment Cowdenbeath [status] => Y ) [1] => Array ( [city] => Bristol [property_type] => Apartment [id] => 6 [summary] => Apartment Bristol [status] => Y ) [2] => Array ( [city] => Burnley [property_type] => Apartment [id] => 5 [summary] => Apartment Burnley [status] => Y ) ) “” All apartments CORRECT

Contents of ARRAY on page 2:

“”Array ( [0] => Array ( [city] => New York [property_type] => Villa [id] => 10 [summary] => New York Villa [status] => Y ) [1] => Array ( [city] => Canterbury [property_type] => Villa [id] => 9 [summary] => Villa Canterbury [status] => Y ) [2] => Array ( [city] => Bristol [property_type] => Villa [id] => 8 [summary] => Villa Bristol [status] => Y ) ) “” All Villas INCORRECT.Does this help in any way?

Colin

sutabi · August 12, 2010, 7:37pm

Again, whats not working? Are you getting errors?

RNEL · August 6, 2010, 8:45pm

ok thanks for the feedback…

I think you forgot to include the “LIMIT” command code on your sql statement


$r2=mysql_query("select count(*) as total from " . $prev . "property " . $MyQuery . " order by price asc "[b]. $lmt[/b]);

however I still noticed some discrepancies but please try that first and we’ll check other things if it doesn’t solved it

ColinHughes · August 6, 2010, 9:27pm

RNEL

Tried that. No success I’m afraid to say.

Colin

RNEL · August 6, 2010, 6:39pm

ok thanks for the feedback…

is this a typo error or do you actually have this on your htaccess?


index.html?page=$1

should it be


index.[b]php[/b]?page=$1

I tried your code using my own query and it works just fine, it returns something like this when not on the first page.

[First Page] [Prev] Showing page 3 of 719 pages [Next] [Last Page]

Total records found on my query is 2155 divided by 3 (rowsPerPage) is 718.3 which makes your code correct on my perspective.

Let’s try to check your if else statements if that doesn’t solve your problem.