PHP Pagination : Prev and Next button work but table won't show up

Question

I'm following some example of PHP paging with ODBC on the internet and change them according to my own use.

I've managed to make to NEXT and PREVIOUS worked. The URL of the page change when the NEXT or PREVIOUS is clicked. For example when I clicked NEXT this is the URL localhost/mypage.php?page=2.

The issue here is the table of the data won't show up.

This is my code :

function inv_rows($r1)  
{
ob_start(); 
(int)$number = odbc_result_all($r1);
ob_clean(); 
return $number;
}

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

if(empty($page)){$page = 1; 
}           
$query = "SELECT UserId, UserNm FROM User"; 
$num_result = odbc_exec($db, $query);
$numrows = inv_rows($num_result);   

$limit = 10;
$offset = ($page - 1) * $limit;

$sql = odbc_exec($conn,"SELECT UserId, UserNm FROM User LIMIT $limit OFFSET $offset"); 


$result = odbc_exec($db, $sql);

echo "<table style='width: 600;'>";

while(odbc_fetch_row($result))
{
echo "<tr>
<td style='width: 300; height: 15px;>";
echo odbc_result_all($result, "UserId");
echo "</td>
<td style='width: 300; height: 15px;'>";
echo odbc_result_all($result, "UserNm");
echo "</td>
</tr>";
}

echo "</table>";
if($page !=1)
{
$pageprev = $page - 1;
echo "<a href='paging2.php?page=$pageprev' style='text-decoration: none'>&nbsp; PREVIOUS&nbsp;</a>";
}
else
{ 
echo "&nbsp; PREVIOUS &nbsp;"; 
}

if(($numrows - ($limit * $page)) > 0)
{
$pagenext = $page + 1;
echo "<a href='paging2.php?page=$pagenext' style='text-decoration: none'>NEXT</a>"; }
else
{ 
echo "&nbsp; NEXT &nbsp;"; 
}

odbc_free_result($result);            
exit;
?>

I'm sure I miss something. Please guide me. Thank you.

Possible duplicate of [this](http://stackoverflow.com/questions/3325515/sqlite-limit-offset-query) SO question. — Jeroen Heier, Feb 17 '17 at 05:22
Based on [this](http://stackoverflow.com/questions/3325515/sqlite-limit-offset-query) and [this](http://stackoverflow.com/a/28860492/5016333) I change my code to `$sql = "SELECT UserId, UserNm FROM User LIMIT 10 OFFSET (SELECT COUNT(*) FROM User)-10";` . The table still won't show up. @JeroenHeier — Iman Yasmin, Feb 17 '17 at 06:54

Chava Geldzahler · Answer 1 · 2017-02-20T05:02:39.130

0

The code in your while function has raw HTML, and opens <?php script tags for PHP code. Since you are already in PHP context, it should be the opposite way around - No need for opening and closing <?php tags, and HTML needs to be printed with PHP's echo command.
The <table> tag should be outside your while function. You don't want to print a table for each row.

Update:

Is $conn properly defined? Is $db properly defined?
Does $result actually contain the rows you are interested in?

This should print a header row for your table. If row data isn't printed, check your query result.

echo "<table style='width: 600;'>";
echo "<thead><tr><th>User ID</th><th>User Name</th></tr></thead><tbody>";

while(odbc_fetch_row($result))
{
    echo "<tr><td style='width: 300; height: 15px';>".odbc_result($result, 'UserId')."</td><td style='width: 300; height: 15px;'>".odbc_result($result, 'UserNm')."</td></tr>";
}

echo "</tbody></table>";

edited Feb 20 '17 at 05:02

answered Feb 17 '17 at 03:06

Chava Geldzahler

3,605
1
18
32

You mean `echo $result;` ? @user5855223 – Iman Yasmin Feb 17 '17 at 03:17
Well, you can do that. It will print your result, but it might not look as beautiful as you would've wished... You would need to print some html along with it, and print out the attributes from `$result` where they belong. – Chava Geldzahler Feb 17 '17 at 03:25
Sorry @user5855223 , I did echo them. See my updated code :) – Iman Yasmin Feb 17 '17 at 03:28
I get it. I changed my code but the table still won't show up. I wonder if I wrongly implement the `$offset` and `$limit` . Any idea @ user5855223 ? – Iman Yasmin Feb 17 '17 at 03:54
Do `echo $result;` as in your comment above. What does that give you? – Chava Geldzahler Feb 17 '17 at 03:56
The table doesn't show up. The only thing printed are the `next` and `previous` . @user5855223 – Iman Yasmin Feb 17 '17 at 04:00
Any idea @user5855223 ? – Iman Yasmin Feb 20 '17 at 03:48

score 0 · Answer 2 · answered Feb 17 '17 at 07:13

You must try and use the $offset variable in your query.

<?php
function inv_rows($r1)  
{
ob_start(); 
(int)$number = odbc_result_all($r1);
ob_clean(); 
return $number;
}

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

if(empty($page)){$page = 1; 
}           
$query = "SELECT UserId, UserNm FROM User"; 
$num_result = odbc_exec($db, $query);
$numrows = inv_rows($num_result);   

$limit = 10;
$offset = ($page - 1) * $limit;

$sql = "SELECT UserId, UserNm FROM User LIMIT 10 OFFSET $offset"; 


$result = odbc_exec($db, $sql);

echo "<table style='width: 600;'>";

while(odbc_fetch_row($result))
{
echo "<tr>
<td style='width: 300; height: 15px;>";
echo odbc_result_all($result, "UserId");
echo "</td>
<td style='width: 300; height: 15px;'>";
echo odbc_result_all($result, "UserNm");
echo "</td>
</tr>";
}

echo "</table>";
if($page !=1)
{
$pageprev = $page - 1;
echo "<a href='paging2.php?page=$pageprev' style='text-decoration: none'>&nbsp; PREVIOUS&nbsp;</a>";
}
else
{ 
echo "&nbsp; PREVIOUS &nbsp;"; 
}

if(($numrows - ($limit * $page)) > 0)
{
$pagenext = $page + 1;
echo "<a href='paging2.php?page=$pagenext' style='text-decoration: none'>NEXT</a>"; }
else
{ 
echo "&nbsp; NEXT &nbsp;"; 
}

odbc_free_result($result);            
exit;
?>

I try `$sql = "SELECT UserId, UserNm FROM User ORDER BY UserId ASC LIMIT " . $limit . " OFFSET " . $offset;" " ;` and `$sql = "SELECT UserId, UserNm FROM User LIMIT 10 OFFSET $offset"` . Yet the table still doesn't show up. @SaurabhMore — Iman Yasmin, Feb 17 '17 at 07:25

PHP Pagination : Prev and Next button work but table won't show up

2 Answers2

Update: