Page 1 of 1

Problem with displaying the data in several pages....

Posted: Fri Feb 14, 2003 2:48 pm
by Winter
Hello, everyone,
I have struggled this problem for two days. I do really have no idea how to fix it. I tried to retrieve the data from database and display the data 3 rows per table. However, the links, [NEXT] and [Bottom] for going to next page, didn’t work. It always showed first page with 3 rows of data.

I copy my code here, a little long. But… pleaseeeeeeeeee help me out..


------------------ userviewer.php-----------------
<?php
include "common_db.inc";

function list_records() {
global $default_dbname,$user_tablename;
global $default_sort_order, $default_order_by, $records_per_page;
global $sort_order, $order_by, $cur_page;

$link_id=db_connect($default_dbname);
if(!$link_id) echo "cannot link to database";

$query="Select count(*) from $user_tablename";

$result=mysql_query($query);
if(!$result) echo "cannot execute the query";

$query_data=mysql_fetch_row($result);
$total_num_user=$query_data[0];
if(!$total_num_user) echo "no user found";

$page_num=$cur_page+1;
$total_num_page=$last_page_num=ceil($total_num_user/ $records_per_page);

echo "<html><body>";
echo "<center><h3>$total_num_user users found. Displaying the page $page_num out of $last_page_num.";


$limit_str="LIMIT " . $cur_page * $records_per_page . ",$records_per_page";


$query="select ID, District, Project_title from $user_tablename $limit_str";

$result=mysql_query($query);
if(!$result) echo "cannot execute query1";
?>

<DIV align="center">
<table border=1>
<tr>
<th width=25% NOWRAP><a href="<?php echo $_SERVER['PHP_SELF']."?action=list_records"; ?>">

ID</a></th>

<th width=25% NOWRAP><a href="<?php echo $_SERVER['PHP_SELF']."?action=list_records"; ?>">

District</a></th>
<th width=25% NOWRAP><a href="<?php echo $_SERVER['PHP_SELF']."?action=list_records"; ?>">

Project_title</a></th>

<th width=25% NOWRAP>test</th>
</tr>

<?php

while ($query_data=mysql_fetch_array($result)){
$ID=$query_data["ID"];
$District=$query_data["District"];
$Project_title=$query_data["Project_title"];
echo "<tr>\n";
echo "<td>$ID</td>\n";
echo "<td>$District</td>\n";
echo "<td>$Project_title</td>\n";
echo "<td>.. keep for future </td>";
echo "</tr>\n";
}

?>
</table>
</DIV>

<?php

echo "<br>\n";
echo "<strong><center>";
echo "page_num: $page_num <br>";
echo "cur_page: $cur_page <br>";
if($page_num>1){
$prev_page=$cur_page-1;
echo "<a href=\"{$_SERVER['PHP_SELF']}?action=list_records&cur_page=0\">[top]</a>";
echo "<a href=\"{$_SERVER['PHP_SELF']}?action=list_records&cur_page=$prev_page\">[prev]</a>";
}


if($page_num<$total_num_page){
$next_page=$cur_page + 1;
$last_page=$total_num_page - 1;

echo "<a href=\"{$_SERVER['PHP_SELF']}?action=list_records&cur_page=$next_page\">[next]</a>";

echo "<a href=\"{$_SERVER['PHP_SELF']}?action=list_records&cur_page=$last_page_num\">[bottom]</a>";
}

echo "</strong></center>";
html_footer();
}


switch ($_GET['action']){
case "view_record";
break;
default:
list_records();
break;
}

?>

-----------------------
Thank you..

Winter :oops: :cry: :(

Posted: Fri Feb 14, 2003 3:03 pm
by daven
You need to use the LIMIT feature of MySQL. Ex:
$qry="SELECT * FROM Table LIMIT $offset,3"

Then for your [next] and [back] buttons, pass 'offset' in the url. So if you are on page 1 (rows 1-3), the next buttons would have "'offset=3' in the link.
This would make the SQL "SELECT * FROM Table LIMIT 3,3" and would retrieve rows 4-6.

paging is not working propertly

Posted: Sat Feb 15, 2003 6:27 pm
by Winter
Hello,

I tried to revise the code that I posted above. However, the paging still not work. It always shows the first page with a 3-rows-table. The link NEXT or LAST cannot work. The value of $cur_page cannot be passed to next. Did I make some mistakes?

Please help me out... I have spent 3 days to figure this out.... please help..

Thank you.

Winter :oops: :(

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

<?php

include "common_db.inc";

if(!isset($_POST['cur_page'])){
$cur_page=0;
echo "cannot get the cur<br>";
}
else {
$cur_page=$_POST['cur_page'];
}
$link_id=db_connect($default_dbname);
if (!$link_id) echo "cannot link to database";

$query="select count(*) from testdb";
$result=mysql_query($query);
if(!$result) echo "cannot execute the query";

$query_data=mysql_fetch_row($result);
$total_num_user=$query_data[0];
if(!$total_num_user) echo "no user found";
echo $total_num_user;
$page_num=$cur_page+1;
$total_num_page=$last_page_num=ceil($total_num_user/$records_per_page);

echo "<html><body>";
echo "<center><h3>$total_num_user users found. Displaying the page $page_num out of $last_page_num.";

$limit_str="LIMIT ". $cur_page*$records_per_page. ", $records_per_page";
$query="Select ID, District, Project_title from $user_tablename $limit_str";

$result=mysql_query($query);
if(!$result) echo "cannot execute the query1";
?>

<table border=1>
<tr>
<td>ID</td></td><td>District</td><td>Project_title</td>
</tr>


<?php
while ($query_data=mysql_fetch_array($result)){
$ID=$query_data["ID"];
$District=$query_data["District"];
$Project_title=$query_data["Project_title"];
echo "<tr>\n";
echo "<td>$ID</td>\n";
echo "<td>$District</td>\n";
echo "<td>$Project_title</td>\n";
echo "</tr>\n";
}
?>

<?php
if ($cur_page=="0"){
$next_page=$cur_page+1;
$last_page=$total_num_page-1;
echo ("<a href=\"{$_SERVER['PHP_SELF']}?cur_page=".$next_page."\">[NEXT]</a>");
echo "<a href=\"{$_SERVER['PHP_SELF']}?cur_page=$last_page\">[Lastpage]</a>";

}else if($page_num=$last_page_num){
$prev_page=$page_num-1;
echo "<a href=\"{$_SERVER['PHP_SELF']}?cur_page=0\">[top]</a>";
echo "<a href=\"{$_SERVER['PHP_SELF']}?cur_page=$prev_page\">[prev]</a>";
}else{
echo "<a href=\"{$_SERVER['PHP_SELF']}?cur_page=$next_page\">[NEXT]</a>";
echo "<a href=\"{$_SERVER['PHP_SELF']}?cur_page=$last_page\">[Lastpage]</a>";
echo "<a href=\"{$_SERVER['PHP_SELF']}?cur_page=0\">[top]</a>";
echo "<a href=\"{$_SERVER['PHP_SELF']}?cur_page=$prev_page\">[prev]</a>";
}

echo "</strong></center>";
html_footer();

Posted: Sat Feb 15, 2003 7:44 pm
by McGruff
If you use the dataseek function you can do a page X of Y ... 1 | 2 | 3 | type of link which I personally like better than next / previous buttons.

I posted a couple of functions here: http://www.phpbuilder.com/board/showthr ... d=10228391

Often people are doing two queries with the LIMIT method: the LIMIT query and another one to get the total number of rows. With dataseek you just need one query.

I timed the two with the mysql control centre (mysql.com) and all three queries came out at 0.06s in the test case - so you can save a whole 0.06s lol - and some memory I suppose.

Anyway, here's a snippet of code for the dataseek bit. $page_choice and $max (the max items you will allow per page) will have to be fed in to the function as arguments:

Code: Select all

...  (SELECT query and $rows = mysql_num_rows($query) first ...
         
            $pos = $page_choice * $max;
            $pos -= $max;
            // to show 1-20 of the query rows we need pos 0 - 19 ie 
            // $pos < $page_choice * 20
            // 2nd IF term prevents data seek running on past last 
            // query row, eg if only 2 posts on page 1
            while ($pos < ($page_choice * $max) AND $pos < $rows) &#123;
                mysql_data_seek($query, $pos);
                $result = mysql_fetch_array($query);
                $var = $result&#1111;'var'];
                ......etc........
                .... the rest of your code ........
                 $pos++;

Posted: Mon Feb 17, 2003 12:02 pm
by Winter
Thank you so much... you solve my problem...


Winter :lol:

Posted: Mon Feb 17, 2003 6:26 pm
by McGruff
Good to hear :)

I was looking at the method again (I'm kind of new to php) with regard to memory use.

In the LIMIT method, the all-records query (used to get total rows) is done on just one column - an id number - and the actual LIMIT query (which would probably get info from several columns) only picks out a set number of rows.

In the dataseek method, there's just one query but it has ALL the columns (or rather all the one's you need) for ALL records.

So, if the total number of rows in the table is very much greater than the number of rows you put on each page, the dataseek query could become a bit of a monster.

However, you could maybe adapt the method by doing the same two queries in the LIMIT method and then dataseeking through the rows query to pick out row IDs for a particular page. Row IDs should be all you need to create page X of Y links.

Dataseek seems to be fairly quick: I was getting around a hundredth of a second per 200 rows.

like I said, I'm still pretty new: I'd guess there's an 80% probability I might actually be talking sense...

PS: don't forget that the dataseek fn starts counting from 0, so the first row is 0, the second 1, etc. I might not have put that in the code comments.