PHP Mysql pagination problem

maximus83 · August 2, 2011

I'm a novice with php and mysql so please be kind. The problem is I'm calling a database which has various states open, closed, pending etc, all this works fine.

The problem is when I click next on my paging links the page just reloads to the start page with all states visible and the first 6 rows visible.

My code seems to work fine as the url of next, prev page has a different start number for the start of the database query eg (http:/wordpress/?page_id=1072?s=6&np=3).

If I change the start number to 6 for example in the code instead of 0 I get the second page to display.

I have a feeling a another variable may need to go into the URL but not sure what?

help much appreciated.

$dbc = mysql_connect (DB_HOST, DB_USER, DB_PASSWORD) ;
mysql_select_db (DB_NAME) ;

/*state control buttons */

echo "<div id=\"shownew\"><table class=\"showhide\"><tr><td><p><form method=\"post\" action=\"".htmlentities($_SERVER['REQUEST_URI'])."\"><INPUT TYPE=\"submit\" name=\"showopen\" VALUE=\"\" class=\"show_open\"></form></p></td>";
echo "<td><p><form method=\"post\" action=\"".htmlentities($_SERVER['REQUEST_URI'])."\"><INPUT TYPE=\"submit\" name=\"showpending\" VALUE=\"\" class=\"show_pending\"></form></p></td>";
echo "<td><p><form method=\"post\" action=\"".htmlentities($_SERVER['REQUEST_URI'])."\"><INPUT TYPE=\"submit\" name=\"showclosed\" VALUE=\"\" class=\"show_closed\"></form></p></td>";
echo "<td><p><form method=\"post\" action=\"".htmlentities($_SERVER['REQUEST_URI'])."\"><INPUT TYPE=\"submit\" name=\"show_all\" VALUE=\"\" class=\"show_all\"></form></p></td></tr></table></div>";


$self = 'http://' . $_SERVER['HTTP_HOST'] . $_SERVER['PHP_SELF'] ;
/* Status set code */

if (isset($_POST['close'])) {
    $close_row = $_POST['close_row'];
    $close = "UPDATE support_users SET status = 'Closed', date_altered = NOW() WHERE ticket_number=$close_row";
    $closeresult = @mysql_query ($close);
    $viewstate = $_POST['viewstate'];
}

if (isset($_POST['open'])) {
    $close_row = $_POST['close_row'];
    $close = "UPDATE support_users SET status = 'Open', date_altered = NOW() WHERE ticket_number=$close_row";
    $closeresult = @mysql_query ($close);
    $viewstate = $_POST['viewstate'];
}

if (isset($_POST['pending'])) {
    $close_row = $_POST['close_row'];
    $close = "UPDATE support_users SET status = 'Pending', date_altered = NOW() WHERE ticket_number=$close_row";
    $closeresult = @mysql_query ($close);
    $viewstate = $_POST['viewstate'];
}

if (isset($_POST['pending_ami'])) {
    $close_row = $_POST['close_row'];
    $close = "UPDATE support_users SET status = 'Pending AMI', date_altered = NOW() WHERE ticket_number=$close_row";
    $closeresult = @mysql_query ($close);
    $viewstate = $_POST['viewstate'];
}

if (isset($_POST['pending_arp'])) {
    $close_row = $_POST['close_row'];
    $close = "UPDATE support_users SET status = 'Pending ARP', date_altered = NOW() WHERE ticket_number=$close_row";
    $closeresult = @mysql_query ($close);
    $viewstate = $_POST['viewstate'];
}

/* View state set code */

if (isset($_POST['showopen'])) {
    $viewstate='open';
}

if (isset($_POST['showpending'])) {
    $viewstate='pending';
}


if (isset($_POST['showclosed'])) {
    $viewstate='closed';
}

if (isset($_POST['show_all'])) {
    $viewstate='all';
}


$display = 6;

if (isset($_GET['np'])) {
    $num_pages = $_GET['np'];

} else {

        if ($viewstate=='open') {   
        $query = "SELECT ticket_number, first_name, surname, email, product, retailer, DATE_FORMAT(dop, '%d %M %Y') AS dop, message, address, DATE_FORMAT(created, '%d %M %Y %r') AS created, status FROM support_dev WHERE status='Open' ORDER BY ticket_number ASC";
        $query_result = mysql_query ($query); 
        $num_records = @mysql_num_rows ($query_result);

        if ($num_records > $display) {
    $num_pages = ceil ($num_records/$display);
} else {
    $num_pages = 1;
    }
}


    elseif ($viewstate=='pending') {
        $query = "SELECT ticket_number, first_name, surname, email, product, retailer, DATE_FORMAT(dop, '%d %M %Y') AS dop, message, address, DATE_FORMAT(created, '%d %M %Y %r') AS created, status FROM support_dev WHERE status LIKE 'Pending%' ORDER BY ticket_number ASC";
        $query_result = mysql_query ($query); 
        $num_records = @mysql_num_rows ($query_result);

        if ($num_records > $display) {
    $num_pages = ceil ($num_records/$display);
} else {
    $num_pages = 1;
}
    }

elseif ($viewstate=='closed') {
        $query = "SELECT ticket_number, first_name, surname, email, product, retailer, DATE_FORMAT(dop, '%d %M %Y') AS dop, message, address, DATE_FORMAT(created, '%d %M %Y %r') AS created, status FROM support_dev WHERE status='Closed' ORDER BY ticket_number ASC";
        $query_result = mysql_query ($query); 
        $num_records = @mysql_num_rows ($query_result);

        if ($num_records > $display) {
    $num_pages = ceil ($num_records/$display);
} else {
    $num_pages = 1;
    }

}
else {
        $query = "SELECT ticket_number, first_name, surname, email, product, retailer, DATE_FORMAT(dop, '%d %M %Y') AS dop, message, address, DATE_FORMAT(created, '%d %M %Y %r') AS created, status FROM support_dev ORDER BY ticket_number ASC";
        $query_result = mysql_query ($query); 
        $num_records = @mysql_num_rows ($query_result);

        if ($num_records > $display) {
    $num_pages = ceil ($num_records/$display);
} else {
    $num_pages = 1;
    }
}
}


if (isset($_GET['s'])) {
    $start = $_GET['s'];
} else {
    $start = 0;
}

    $query = "SELECT ticket_number, first_name, surname, email, product, retailer, DATE_FORMAT(dop, '%d %M %Y') AS dop, message, address, DATE_FORMAT(created, '%d %M %Y %r') AS created, status FROM support_dev ORDER BY ticket_number ASC LIMIT $start, $display";
    if ($viewstate=='open') {

        $query = "SELECT ticket_number, first_name, surname, email, product, retailer, DATE_FORMAT(dop, '%d %M %Y') AS dop, message, address, DATE_FORMAT(created, '%d %M %Y %r') AS created, status FROM support_dev WHERE status='Open' ORDER BY ticket_number ASC LIMIT $start, $display";
        }
            elseif ($viewstate=='pending') {
        $query = "SELECT ticket_number, first_name, surname, email, product, retailer, DATE_FORMAT(dop, '%d %M %Y') AS dop, message, address, DATE_FORMAT(created, '%d %M %Y %r') AS created, status FROM support_dev WHERE status LIKE 'Pending%' ORDER BY ticket_number ASC LIMIT $start, $display";
        }
        elseif ($viewstate=='closed') {
        $query = "SELECT ticket_number, first_name, surname, email, product, retailer, DATE_FORMAT(dop, '%d %M %Y') AS dop, message, address, DATE_FORMAT(created, '%d %M %Y %r') AS created, status FROM support_dev WHERE status='Closed' ORDER BY ticket_number ASC LIMIT $start, $display";}
        $result = @mysql_query ($query);
        $num = mysql_num_rows ($result);

    if ($num > 0) {

    if ($num_pages > 1) {
        echo '<p>';
        $current_page = ($start/$display) + 1;

        if ($current_page != 1) {
            echo '<a href="?page_id=1072?s=' . ($start - $display) . '&np=' . $num_pages . '">Previous</a> ';
        }
        for ($i = 1; $i <= $num_pages; $i++) {
            if ($i != $current_page) {
                echo '<a href="?page_id=1072?s=' . (($display * ($i - 1))) . '&np=' . $num_pages . '">' .$i .'</a> ';
            } else {
                echo $i . ' ';
            }
        }
        if ($current_page !=$num_pages) {
            echo '<a href="' . $query_result . '?s=' . ($start + $display) . '&np=' . $num_pages . '">Next</a> ';
        }
        echo '</p><br />'; 

    }


     if ($result) {
         echo '
    <div id="supviewwrapperheader">
    <div id="supviewticket"><p>Ticket</p></div> 
    <div id="supviewfirst"><p>First Name</p></div>
    <div id="supviewlast"><p>Last Name</p></div>
    <div id="supviewemail"><p>Email</p></div>
    <div id="supviewproduct"><p>Product</p></div>
    <div id="supviewretailer"><p>Retailer</p></div>
    <div id="supviewdop"><p>D.O.P.</p></div>
    <div id="supviewmessage"><p>Message</p></div>
    <div id="supviewaddress"><p>Address</p></div>
    <div id="supviewcreated"><p>Date created</p></div>
    <div id="supviewstatus"><p>Status</p></div>
    <div id="supviewbuttons"><p></p></div>
    </div>
    ';

    while ($row = mysql_fetch_array($result, MYSQL_NUM)) {
        echo "
        <div id=\"supviewwrapper\" class=\"".($row[10])."\">
        <div id=\"supviewticket\"><p><a name=\"a".$row[0]."\"></a>$row[0]</p></div>
        <div id=\"supviewfirst\"><p>$row[1]</p></div>
        <div id=\"supviewlast\"><p>$row[2]</p></div>
        <div id=\"supviewemail\"><p>$row[3]</p></div>
        <div id=\"supviewproduct\"><p>$row[4]</p></div>
        <div id=\"supviewretailer\"><p>$row[5]</p></div>
        <div id=\"supviewdop\"><p>$row[6]</p></div>
        <div id=\"supviewmessage\"><p>$row[7]</p></div>
        <div id=\"supviewaddress\"><p>$row[8]</p></div>
        <div id=\"supviewcreated\"><p>$row[9]</p></div>
        <div id=\"supviewstatus\"><p>$row[10]</p></div>
        <div id=\"supviewbuttons\"><p><form method=\"post\" action=\"".htmlentities($_SERVER['REQUEST_URI'])."#a".$row[0]."\"><input type=\"hidden\" name=\"close_row\" value=\"".($row[0])."\" /><input type=\"hidden\" name=\"viewstate\" value=\"".$viewstate."\" /><INPUT TYPE=\"submit\" name=\"open\" VALUE=\"\" class=\"submit_open\"><br /><INPUT TYPE=\"submit\" name=\"pending\" VALUE=\"\" class=\"submit_pending\"><br /><INPUT TYPE=\"submit\" name=\"pending_ami\" VALUE=\"\" class=\"submit_pendingami\"><br /><INPUT TYPE=\"submit\" name=\"pending_arp\" VALUE=\"\" class=\"submit_pendingarp\"><br /><INPUT TYPE=\"submit\" name=\"close\" VALUE=\"\" class=\"submit_closed\"></form></p></div></div>";
    }
     }
    mysql_free_result ($result);

     }  


if (is_null($viewstate)) {
    $viewstate='all';
}



?>

voip03 · August 2, 2011

When you go to page 6, need to display number of result.

eg.

Page1 display only 1-5 results

Page 6 -> 30-35

In query u need to limit 30 to 35;

maximus83 · August 2, 2011

Thanks for the reply, I'm not sure your understanding me correctly, Sorry if I wasn't clear. I have successfully limited my results to 6 per page, and my code correctly works out that the correct number of pages with 6 rows per page, the problem is when I click on the next link or a page number link it just reloads the first page of results again. for example page number 2 should be rows 7-12 but I'm still just seeing rows 1-6.

Any suggestions?

maximus83 · August 2, 2011

I saw this question and solution and i thought it could be something to do with what I need to do, i'm not entirely sure what I could add into the URL for my pagination to work correctly though?

http://stackoverflow.com/questions/6398269/pagination-only-showing-first-page-of-products-after-page-1-getting-warning-m

voip03 · August 2, 2011

Your problem is when you go to page 2, result is still 1- 6 not 7-12.

maximus83 · August 2, 2011

yes that's right, so how do i get this to work with my code.

voip03 · August 2, 2011

SELECT * FROM `your_table` LIMIT 7, 12;

maximus83 · August 2, 2011

Sorry if if i'm not getting this, but i already have a limit in my query...

$query = "SELECT ticket_number, first_name, surname, email, product, retailer, DATE_FORMAT(dop, '%d %M %Y') AS dop, message, address, DATE_FORMAT(created, '%d %M %Y %r') AS created, status FROM support_dev WHERE status='Closed' ORDER BY ticket_number ASC LIMIT $start, $display";}

voip03 · August 2, 2011

check the values -> $start, $display

voip03 · August 2, 2011

$start= $_GET['s'];

$display= $start+6;

maximus83 · August 2, 2011

start = 0 and display = 6

if ($current_page != $num_pages) {
		echo '<a href="?page_id=1072?currentpage=1&s=' . ($start + $display) . '&np=' . $num_pages . '">Next</a> ';
	}

the above code is for the next page url , this is processed and returned as : http://www.xxxxxxxxxxxxxxxxx.com/wordpress/?page_id=1072?s=6&np=3

as you can see this calculates there are 3 pages and the start in this case is row 6 (page 2) but page two isn't actually loaded? so my values are correct, is it something else i am missing from the url code?

I did try $display= $start+6; too.

voip03 · August 2, 2011

$query = "SELECT ticket_number, first_name, surname, email, product, retailer, DATE_FORMAT(dop, '%d %M %Y') AS dop, message, address, DATE_FORMAT(created, '%d %M %Y %r') AS created, status FROM support_dev WHERE status='Closed' ORDER BY ticket_number ASC LIMIT 7, 12";}

do it manually

voip03 · August 2, 2011

echo '<a href="?page_id=1072?currentpage=1&np=' . $num_pages . '">Next</a>';

$start = $_GET['$num_pages']*6;

$display = $start+6;

maximus83 · August 2, 2011

i feel we're on the wrong track here, any other ideas? anyone?

PFMaBiSmAd · August 2, 2011

Sorry to jump in, but you would calculate the LIMIT based on the page and the number of results you want per page. Something like -

$offset = ($currentpage - 1) * $rowsperpage;

Where $rowsperpage is your $display variable and $currentpage looks like it is (should be) your $start variable.

See this link for a pagination tutorial - http://www.phpfreaks.com/tutorial/basic-pagination

You also have too much repetitive code that is probably making it harder to see what your actual code is doing.

Your first block of if(){} elseif{} statements that is building the query to find now many total rows can be reduced to just -

<?php
if (isset($_GET['np'])) {
    $num_pages = $_GET['np'];
} else {
$where = "1"; // default
switch ($viewstate) {
	case "open": $where = "status='Open'"; break;
	case "pending": $where = "status LIKE 'Pending%'"; break;
	case "closed": $where = "status='Closed'"; break;
}
$query = "SELECT ticket_number, first_name, surname, email, product, retailer, DATE_FORMAT(dop, '%d %M %Y') AS dop,
	message, address, DATE_FORMAT(created, '%d %M %Y %r') AS created, status FROM support_dev WHERE $where ORDER BY ticket_number ASC";
$query_result = mysql_query ($query); 
$num_records = mysql_num_rows ($query_result);
if ($num_records > $display) {
	$num_pages = ceil ($num_records/$display);
} else {
	$num_pages = 1;
}
}

Since the above code is already building the base query, you don't need to repeat all the logic again to just add the LIMIT clause on the end. Just use the following where you are building the query to get the actual rows -

<?php
$query .= " LIMIT $offset, $display";
$result = mysql_query ($query);
$num = mysql_num_rows ($result);

maximus83 · August 2, 2011

Thank you, that simplified my code. But the bottom query simplification caused something to go wrong as the nav links totally removed the table from the page. My initial problem of only getting the first page of results still exists, please see my current code and any more help to get subsequent pages results to display would be great.

 $display = $start+6;
$offset = ($current_page - 1) * $rowsperpage;

	if (isset($_GET['np'])) {
    $num_pages = $_GET['np'];
} else {
$where = "1"; // default
switch ($viewstate) {
	case "open": $where = "status='Open'"; break;
	case "pending": $where = "status LIKE 'Pending%'"; break;
	case "closed": $where = "status='Closed'"; break;
}
$query = "SELECT ticket_number, first_name, surname, email, product, retailer, DATE_FORMAT(dop, '%d %M %Y') AS dop,
	message, address, DATE_FORMAT(created, '%d %M %Y %r') AS created, status FROM support_dev WHERE $where ORDER BY ticket_number ASC";
$query_result = mysql_query ($query); 
$num_records = @mysql_num_rows ($query_result);
if ($num_records > $display) {
	$num_pages = ceil ($num_records/$display);
} else {
	$num_pages = 1;
}
}


if (isset($_GET['s'])) {
$start = $_GET['s'];
} else {
$start = $_GET['$num_pages']*6;

}

$query = "SELECT ticket_number, first_name, surname, email, product, retailer, DATE_FORMAT(dop, '%d %M %Y') AS dop, message, address, DATE_FORMAT(created, '%d %M %Y %r') AS created, status FROM support_dev ORDER BY ticket_number ASC LIMIT $offset, $display";
if ($viewstate=='open') {

	$query = "SELECT ticket_number, first_name, surname, email, product, retailer, DATE_FORMAT(dop, '%d %M %Y') AS dop, message, address, DATE_FORMAT(created, '%d %M %Y %r') AS created, status FROM support_dev WHERE status='Open' ORDER BY ticket_number ASC LIMIT $offset, $display";
	}
		elseif ($viewstate=='pending') {
	$query = "SELECT ticket_number, first_name, surname, email, product, retailer, DATE_FORMAT(dop, '%d %M %Y') AS dop, message, address, DATE_FORMAT(created, '%d %M %Y %r') AS created, status FROM support_dev WHERE status LIKE 'Pending%' ORDER BY ticket_number ASC LIMIT $offset, $display";
	}
	elseif ($viewstate=='closed') {
	$query = "SELECT ticket_number, first_name, surname, email, product, retailer, DATE_FORMAT(dop, '%d %M %Y') AS dop, message, address, DATE_FORMAT(created, '%d %M %Y %r') AS created, status FROM support_dev WHERE status='Closed' ORDER BY ticket_number ASC LIMIT $offset, $display";}
	$result = @mysql_query ($query);
	$num = mysql_num_rows ($result);

if ($num > 0) {

if ($num_pages > 1) {
	echo '<p>';
	$current_page = ($start/$display) + 1;

	if ($current_page != 1) {
		echo '<a href="?page_id=1072?s=' . ($start - $display) . '&np=' . $num_pages . '">Previous</a> ';
	}
	for ($i = 1; $i <= $num_pages; $i++) {
		if ($i != $current_page) {
			echo '<a href="?page_id=1072?s=' . (($display * ($i - 1))) . '&np=' . $num_pages . '">' .$i .'</a> ';
		} else {
			echo $i . ' ';
		}
	}
	if ($current_page != $num_pages) {
		echo '<a href="?page_id=1072?s=' . ($start + $display) . '&np=' . $num_pages . '">Next</a> ';
	}
	echo '</p><br />'; 

}


 if ($result) {
	 echo '
<div id="supviewwrapperheader">
    <div id="supviewticket"><p>Ticket</p></div> 
    <div id="supviewfirst"><p>First Name</p></div>
<div id="supviewlast"><p>Last Name</p></div>
<div id="supviewemail"><p>Email</p></div>
<div id="supviewproduct"><p>Product</p></div>
<div id="supviewretailer"><p>Retailer</p></div>
<div id="supviewdop"><p>D.O.P.</p></div>
<div id="supviewmessage"><p>Message</p></div>
<div id="supviewaddress"><p>Address</p></div>
<div id="supviewcreated"><p>Date created</p></div>
<div id="supviewstatus"><p>Status</p></div>
<div id="supviewbuttons"><p></p></div>
</div>
';

while ($row = mysql_fetch_array($result, MYSQL_NUM)) {
	echo "
	<div id=\"supviewwrapper\" class=\"".($row[10])."\">
	<div id=\"supviewticket\"><p><a name=\"a".$row[0]."\"></a>$row[0]</p></div>
	<div id=\"supviewfirst\"><p>$row[1]</p></div>
	<div id=\"supviewlast\"><p>$row[2]</p></div>
	<div id=\"supviewemail\"><p>$row[3]</p></div>
	<div id=\"supviewproduct\"><p>$row[4]</p></div>
	<div id=\"supviewretailer\"><p>$row[5]</p></div>
	<div id=\"supviewdop\"><p>$row[6]</p></div>
	<div id=\"supviewmessage\"><p>$row[7]</p></div>
	<div id=\"supviewaddress\"><p>$row[8]</p></div>
	<div id=\"supviewcreated\"><p>$row[9]</p></div>
	<div id=\"supviewstatus\"><p>$row[10]</p></div>
	<div id=\"supviewbuttons\"><p><form method=\"post\" action=\"".htmlentities($_SERVER['REQUEST_URI'])."#a".$row[0]."\"><input type=\"hidden\" name=\"close_row\" value=\"".($row[0])."\" /><input type=\"hidden\" name=\"viewstate\" value=\"".$viewstate."\" /><INPUT TYPE=\"submit\" name=\"open\" VALUE=\"\" class=\"submit_open\"><br /><INPUT TYPE=\"submit\" name=\"pending\" VALUE=\"\" class=\"submit_pending\"><br /><INPUT TYPE=\"submit\" name=\"pending_ami\" VALUE=\"\" class=\"submit_pendingami\"><br /><INPUT TYPE=\"submit\" name=\"pending_arp\" VALUE=\"\" class=\"submit_pendingarp\"><br /><INPUT TYPE=\"submit\" name=\"close\" VALUE=\"\" class=\"submit_closed\"></form></p></div></div>";
}
 }

mysql_free_result ($result);

 }	


if (is_null($viewstate)) {
$viewstate='all';
}



?>

PFMaBiSmAd · August 2, 2011

You cannot just randomly change parts of your code and expect it to work. You have added an $offset variable in all those queries but you are not setting a variable by that name. I suggest that you read the pagination tutorial that I linked to.

maximus83 · August 2, 2011

like i said i'm no expert i was simply following your instructions.

I followed a pagination system to the letter from a php book. i don't think this is the issue , I'm sure it as somethingto do with adding something else to the next previous URLs

genix · August 2, 2011

Hi,

changed your code a bit, this should work:

<?php
$display = 6;

$where = "1"; // default
switch ($viewstate) {
    case "open":
        $where = "status='Open'";
        break;
    case "pending":
        $where = "status LIKE 'Pending%'";
        break;
    case "closed":
        $where = "status='Closed'";
        break;
}
$query = "SELECT COUNT(*) as total FROM support_dev WHERE $where";

$query_result = mysql_query($query);
$row = mysql_fetch_row($query_result);
$num_records = $row['total'];

if ($num_records > $display) {
    $num_pages = ceil($num_records / $display);
} else {
    $num_pages = 1;
}


if (isset($_GET['s'])) {
    $start = $_GET['s'] * $display;
} else {
    $start = 0;
}
$query .= "SELECT ticket_number, first_name, surname, email, product, retailer, DATE_FORMAT(dop, '%d %M %Y') AS dop,
			message, address, DATE_FORMAT(created, '%d %M %Y %r') AS created, status FROM support_dev WHERE $where ORDER BY ticket_number ASC LIMIT $start, $display";

$result = @mysql_query($query);
$num = mysql_num_rows($result);

if ($num > 0) {

    if ($num_pages > 1) {
        echo '<p>';
        $current_page = ($start >= $display) ? ceil($start / $display) + 1 : 1);

        if ($current_page != 1) {
            echo '<a href="?page_id=1072?s=' . ($start - $display) .  '">Previous</a> ';
        }
        for ($i = 1; $i <= $num_pages; $i++) {
            if ($i != $current_page) {
                echo '<a href="?page_id=1072?s=' . (($display * ($i - 1))) . '">' . $i . '</a> ';
            } else {
                echo $i . ' ';
            }
        }
        if ($current_page != $num_pages) {
            echo '<a href="?page_id=1072?s=' . ($start + $display) . '">Next</a> ';
        }
        echo '</p><br />';

    }


    if ($result) {
        echo '
        <div id="supviewwrapperheader">
        <div id="supviewticket"><p>Ticket</p></div>
        <div id="supviewfirst"><p>First Name</p></div>
        <div id="supviewlast"><p>Last Name</p></div>
        <div id="supviewemail"><p>Email</p></div>
        <div id="supviewproduct"><p>Product</p></div>
        <div id="supviewretailer"><p>Retailer</p></div>
        <div id="supviewdop"><p>D.O.P.</p></div>
        <div id="supviewmessage"><p>Message</p></div>
        <div id="supviewaddress"><p>Address</p></div>
        <div id="supviewcreated"><p>Date created</p></div>
        <div id="supviewstatus"><p>Status</p></div>
        <div id="supviewbuttons"><p></p></div>
        </div>
        ';

        while ($row = mysql_fetch_array($result, MYSQL_NUM)) {
            echo "
            <div id=\"supviewwrapper\" class=\"" . ($row[10]) . "\">
            <div id=\"supviewticket\"><p><a name=\"a" . $row[0] . "\"></a>$row[0]</p></div>
            <div id=\"supviewfirst\"><p>$row[1]</p></div>
            <div id=\"supviewlast\"><p>$row[2]</p></div>
            <div id=\"supviewemail\"><p>$row[3]</p></div>
            <div id=\"supviewproduct\"><p>$row[4]</p></div>
            <div id=\"supviewretailer\"><p>$row[5]</p></div>
            <div id=\"supviewdop\"><p>$row[6]</p></div>
            <div id=\"supviewmessage\"><p>$row[7]</p></div>
            <div id=\"supviewaddress\"><p>$row[8]</p></div>
            <div id=\"supviewcreated\"><p>$row[9]</p></div>
            <div id=\"supviewstatus\"><p>$row[10]</p></div>
            <div id=\"supviewbuttons\"><p>
                <form method=\"post\" action=\"" . htmlentities($_SERVER['REQUEST_URI']) . "#a" . $row[0] . "\">
                    <input type=\"hidden\" name=\"close_row\" value=\"" . ($row[0]) . "\" />
                    <input type=\"hidden\" name=\"viewstate\" value=\"" . $viewstate . "\" />
                    <INPUT TYPE=\"submit\" name=\"open\" VALUE=\"\" class=\"submit_open\"><br />
                    <INPUT TYPE=\"submit\" name=\"pending\" VALUE=\"\" class=\"submit_pending\"><br />
                    <INPUT TYPE=\"submit\" name=\"pending_ami\" VALUE=\"\" class=\"submit_pendingami\"><br />
                    <INPUT TYPE=\"submit\" name=\"pending_arp\" VALUE=\"\" class=\"submit_pendingarp\"><br />
                    <INPUT TYPE=\"submit\" name=\"close\" VALUE=\"\" class=\"submit_closed\">
                </form></p>
            </div></div>";
        }
    }
    mysql_free_result($result);

}
?>

Greets.

maximus83 · August 2, 2011

thanks for the attempt, but using your code i got no results at all.

genix · August 2, 2011

Oh sorry, had some syntax error

Here without the syntax errors...

<?php
$display = 6;

$where = "1=1"; // default
switch ($viewstate) {
    case "open":
        $where = "status='Open'";
        break;
    case "pending":
        $where = "status LIKE 'Pending%'";
        break;
    case "closed":
        $where = "status='Closed'";
        break;
}

$query = "SELECT COUNT(*) as total FROM support_dev WHERE $where";

$query_result = mysql_query($query);
$row = mysql_fetch_row($query_result);
$num_records = $row[0];

if ($num_records > $display) {
    $num_pages = ceil($num_records / $display);
} else {
    $num_pages = 1;
}

if (isset($_GET['s'])) {
    $start = $_GET['s'] * $display;
} else {
    $start = 0;
}
$querystr = "SELECT ticket_number, first_name, surname, email, product, retailer, DATE_FORMAT(dop, '%d %M %Y') AS dop,
			message, address, DATE_FORMAT(created, '%d %M %Y %r') AS created, status FROM support_dev WHERE $where ORDER BY ticket_number ASC LIMIT $start, $display";

$result = mysql_query($querystr);
$num = mysql_num_rows($result);

if ($num > 0) {

    if ($num_pages > 1) {
        echo '<p>';
        $current_page = ($start >= $display) ? ceil($start / $display) + 1 : 1;

        if ($current_page != 1) {
            echo '<a href="?page_id=1072&s=' . ($start - $display) .  '">Previous</a> ';
        }
        for ($i = 1; $i <= $num_pages; $i++) {
            if ($i != $current_page) {
                echo '<a href="?page_id=1072&s=' . (($display * ($i - 1))) . '">' . $i . '</a> ';
            } else {
                echo $i . ' ';
            }
        }
        if ($current_page != $num_pages) {
            echo '<a href="?page_id=1072&s=' . ($start + $display) . '">Next</a> ';
        }
        echo '</p><br />';

    }


    if ($result) {
        echo '
        <div id="supviewwrapperheader">
        <div id="supviewticket"><p>Ticket</p></div>
        <div id="supviewfirst"><p>First Name</p></div>
        <div id="supviewlast"><p>Last Name</p></div>
        <div id="supviewemail"><p>Email</p></div>
        <div id="supviewproduct"><p>Product</p></div>
        <div id="supviewretailer"><p>Retailer</p></div>
        <div id="supviewdop"><p>D.O.P.</p></div>
        <div id="supviewmessage"><p>Message</p></div>
        <div id="supviewaddress"><p>Address</p></div>
        <div id="supviewcreated"><p>Date created</p></div>
        <div id="supviewstatus"><p>Status</p></div>
        <div id="supviewbuttons"><p></p></div>
        </div>
        ';

        while ($row = mysql_fetch_array($result, MYSQL_NUM)) {
            echo "
            <div id=\"supviewwrapper\" class=\"" . ($row[10]) . "\">
            <div id=\"supviewticket\"><p><a name=\"a" . $row[0] . "\"></a>$row[0]</p></div>
            <div id=\"supviewfirst\"><p>$row[1]</p></div>
            <div id=\"supviewlast\"><p>$row[2]</p></div>
            <div id=\"supviewemail\"><p>$row[3]</p></div>
            <div id=\"supviewproduct\"><p>$row[4]</p></div>
            <div id=\"supviewretailer\"><p>$row[5]</p></div>
            <div id=\"supviewdop\"><p>$row[6]</p></div>
            <div id=\"supviewmessage\"><p>$row[7]</p></div>
            <div id=\"supviewaddress\"><p>$row[8]</p></div>
            <div id=\"supviewcreated\"><p>$row[9]</p></div>
            <div id=\"supviewstatus\"><p>$row[10]</p></div>
            <div id=\"supviewbuttons\"><p>
                <form method=\"post\" action=\"" . htmlentities($_SERVER['REQUEST_URI']) . "#a" . $row[0] . "\">
                    <input type=\"hidden\" name=\"close_row\" value=\"" . ($row[0]) . "\" />
                    <input type=\"hidden\" name=\"viewstate\" value=\"" . $viewstate . "\" />
                    <INPUT TYPE=\"submit\" name=\"open\" VALUE=\"\" class=\"submit_open\"><br />
                    <INPUT TYPE=\"submit\" name=\"pending\" VALUE=\"\" class=\"submit_pending\"><br />
                    <INPUT TYPE=\"submit\" name=\"pending_ami\" VALUE=\"\" class=\"submit_pendingami\"><br />
                    <INPUT TYPE=\"submit\" name=\"pending_arp\" VALUE=\"\" class=\"submit_pendingarp\"><br />
                    <INPUT TYPE=\"submit\" name=\"close\" VALUE=\"\" class=\"submit_closed\">
                </form></p>
            </div></div>";
        }
    }
    mysql_free_result($result);

}
?>

maximus83 · August 4, 2011

I've found that if i set start to row 6 or any other number in the middle of the rows record and then click on the link for page 1 and start becomes 0 the link works correctly.

http://www.xxxxxx.com/wordpress/?page_id=1072&s=0&np=3 - this works

this doesn't work I just get a 404 page

http://www.xxxxxx.com/wordpress/?page_id=1072&s=6&np=3 can't understand why only s=0 works in the url string any ideas?

Sign In

PHP Mysql pagination problem

Recommended Posts

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Join the conversation

Important Information