ODBC SQL PHP pagination query

lscruiser

PAGINATION QUESTION
This code displays the same 5 results from the database. How do I get the page to reference the next or previous group of 5 results? I am using ODBC, SQL, and PHP. LIMIT won't work in ODBC. The table name is called plastic with two column names: ename and iname. I am also not sure about line 9. Your help would be greatly appreciated.

CODE:

Expand|Select|Wrap|Line Numbers

 <?php

$db = odbc_connect('','','');  //your connection here

function inv_rows($r1)  {

ob_start(); 

(int)$number=odbc_result_all($r1);

ob_clean(); 

return $number;

}

$page = isset($_GET["page"]) ? $_GET["page"] : 1;  //not sure of "page" text

if(empty($page)){$page = 1; }               

$query = "SELECT * FROM plastic"; // name of table is plastic with columns ename and iname.       

$num_result = odbc_exec($db, $query);

$numrows = inv_rows($num_result);               

echo '<p>There are '.$numrows.' ideas.</p>';           

$limit = 5;

$limitvalue = $page * $limit - ($limit);

$limitnew = $limitvalue + $limit;

$sql = "SELECT * from (SELECT TOP 5 * FROM (SELECT TOP 5 * FROM plastic ORDER BY ename, iname DESC) as table1 ORDER BY ename, iname DESC) as table2 ORDER BY ename, iname ASC"; 

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

while(odbc_fetch_row($result)){               

?>

<table style="width: 600;">    

<tr>

<td style="width: 300; height: 25px;">Name:</td>

<td style="width: 300; height: 25px;">Idea Name:</td>

</tr>                 

<tr>

<td style="width: 300; height: 25px;"><?php echo odbc_result($result, "ename"); ?></td>

<td style="width: 300; height: 25px;"><?php echo odbc_result($result, "iname"); ?></td>

</tr>

<tr>

<td colspan="5" style="height: 25px"><hr/></td>

</tr>

</table>

<?php //PREVIOUS AND NEXT

}

if($page !=1){

$pageprev = $page - 1;

echo "&nbsp;<strong><a href='?page=$pageprev'>PREV</a></strong>&nbsp;"; }

else{ echo "&nbsp;PREV&nbsp;"; }

$numofpages = $numrows / $limit;

for($i = 1; $i <= $numofpages; ++$i){

if($i == $page){ echo "&nbsp;[$i]&nbsp;"; }

else{ echo "&nbsp;<strong><a href='?page=$i'>$i</a></strong>&nbsp;"; }

}

if(($numrows % $limit) != 0){

if($i == $page){ echo "&nbsp;[$i]&nbsp;"; }

else{ echo "&nbsp;<strong><a href='?page=$i'>$i</strong></b>&nbsp;"; }

}

if(($numrows - ($limit * $page)) > 0){

$pagenext = $page + 1;

echo "&nbsp;<strong><a href='?page=$pagenext'>NEXT</a></strong>&nbsp;"; }

else{ echo "&nbsp;NEXT&nbsp;"; }

odbc_free_result($result);            

exit;

?>

code green

Quote:

I am using ODBC, SQL, and PHP. LIMIT won't work in ODBC.

ODBC is a connectivity layer between an application and a DBMS, not a language.
You are rightly using TOP if it is Access or T-SQL.

Your code seems to have lost its way a little, but the trick in pagination is in the query, the PHP and HTML
Your php needs to store the current highest id value (and lowest) in a variable say $highid and pass that as a URL variable via the NEXT or PREV hyperlink to the next page,
The next page runs the query based on the $highid or $lowid value

Expand|Select|Wrap|Line Numbers

 "SELECT TOP 5 * FROM tbl WHERE id> $highid

ORDER BY id"

The next page can of course be the same PHP file but the results will be different.

lscruiser

Thank you for your response.
My table name is plastic, with columns plastic_ID, ename, and iname.
With the code below, the result is seeing all entries (not 5 at a time as desired) and the pagination links do not link to the NEXT or PREV pages.
I appreciate your response and help.
Here is my updated code.

Expand|Select|Wrap|Line Numbers

 <?php 

$db = odbc_connect('','','');  //put your connection here 

function inv_rows($r1)  { 

ob_start(); 

(int)$number=odbc_result_all($r1); 

ob_clean(); 

return $number; 

} 

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

if(empty($page)){$page = 1; } 

$query = "SELECT * FROM plastic"; // name of table is plastic with columns plastic_ID, ename, and iname.            

$num_result = odbc_exec($db, $query); 

$numrows = inv_rows($num_result);                    

echo '<p>There are '.$numrows.' ideas.</p>'; 

$limit = 5; 

$limitvalue = $page * $limit - ($limit); 

$limitnew = $limitvalue + $limit;

if (isset($_POST['submit'])) {

switch ($_POST['direction']) {

case 'next':

$query = "SELECT TOP 5 * FROM plastic WHERE plastic_ID > " . $_SESSION['lastid'] . " ORDER BY plastic_ID";

break;

case 'prev':

$query = "SELECT * FROM (SELECT TOP 5 * FROM plastic WHERE plastic_ID < " . $_SESSION['firstid'] . " ORDER BY plastic_ID DESC) AS t ORDER BY plastic_ID ASC";

break;

default:

$query = "SELECT TOP 5 * FROM plastic ORDER BY plastic_ID";

}     

}

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

while(odbc_fetch_row($result)){                

?> 

<table style="width: 600;">     

<tr> 

<td style="width: 300; height: 25px;">Name:</td> 

<td style="width: 300; height: 25px;">Idea Name:</td> 

</tr>                 

<tr> 

<td style="width: 300; height: 25px;"><?php echo odbc_result($result, "ename"); ?></td> 

<td style="width: 300; height: 25px;"><?php echo odbc_result($result, "iname"); ?></td> 

</tr> 

<tr> 

<td colspan="5" style="height: 25px"><hr/></td> 

</tr> 

</table> 

<?php //PREVIOUS AND NEXT BUTTONS 

} 

if($page !=1){ 

$pageprev = $page - 1; 

echo "&nbsp;<strong><a href='?page=$pageprev'>PREV</a></strong>&nbsp;"; } 

else{ echo "&nbsp;PREV&nbsp;"; } 

$numofpages = $numrows / $limit; 

for($i = 1; $i <= $numofpages; ++$i){ 

if($i == $page){ echo "&nbsp;[$i]&nbsp;"; } 

else{ echo "&nbsp;<strong><a href='?page=$i'>$i</a></strong>&nbsp;"; } 

} 

if(($numrows % $limit) != 0){ 

if($i == $page){ echo "&nbsp;[$i]&nbsp;"; } 

else{ echo "&nbsp;<strong><a href='?page=$i'>$i</strong></b>&nbsp;"; } 

} 

if(($numrows - ($limit * $page)) > 0){ 

$pagenext = $page + 1; 

echo "&nbsp;<strong><a href='?page=$pagenext'>NEXT</a></strong>&nbsp;"; } 

else{ echo "&nbsp;NEXT&nbsp;"; } 

odbc_free_result($result);             

exit; 

?>

dlite922

Please use [code] tags around your code.

code green

I looks like you are not passing the variables via the href.
A bit of debugging code would be helpful (simply echo out variables to see what they contain) But

Expand|Select|Wrap|Line Numbers

 echo "&nbsp;<strong><a href='?page=$pageprev'>PREV</a></strong>&nbsp;"; } 

else{ echo "&nbsp;PREV&nbsp;"; }

It looks like you have wrapped php variables in single quotes so they won't be parsed.
When echoing HTML I find it better to concatenate strings in single quotes and variables.

Expand|Select|Wrap|Line Numbers

 echo '&nbsp;<strong>

<a href="?page='.$pageprev.'">PREV</a>

</strong>&nbsp;'

Looking again there is no URL at all, only a page variable.
You need a page for the link to go.

ODBC SQL PHP pagination query

re: ODBC SQL PHP pagination query

re: ODBC SQL PHP pagination query

re: ODBC SQL PHP pagination query

re: ODBC SQL PHP pagination query

Similar PHP bytes

/bytes/development

/bytes/it

/bytes/about