Multi-page query output

ChrisRCh

Hi all

PHP newbie so please be gentle 🙂

The following script retrieves data and displays the results from a Visual Foxpro table

<html>
<head>
<title>Untitled Document</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
</head>

<body bgcolor="#FFFFFF">
<table width="75%" border="1" cellspacing="1" cellpadding="1" bgcolor="#FFFFFF">
  <tr bgcolor="#CCFFFF">
    <td height="22"><b>Company</b></td>
    <td height="22"><b>Street</b></td>
    <td height="22"><b>Town</b></td>
  </tr>

<?php

//connect to the database
$connectionstring = odbc_connect("vfptable", "", "");

//SQL query
$Query = "SELECT company, street, town FROM contacts where 'A' $ company OR 'M' $ company";

//execute query
$queryexe = odbc_do($connectionstring, $Query);

//query database
while(odbc_fetch_row($queryexe))
{
$company = odbc_result($queryexe, 1);
$street = odbc_result($queryexe, 2);
$town = odbc_result($queryexe, 3);

//format results
print ("<tr>");
print ("<td>$company</td>");
print ("<td>$street</td>");
print ("<td>$town</td>");
print ("</tr>");
}

//disconnect from database
odbc_close($connectionstring);

?>

</table>
</body>
</html>

What modifications are needed to display n records per page and allow navigation to each page?

TIA

Chris

timus

Hey chris

Not sure how much use this will be to you as it doesn't foxpro but you might want to have a look at this

Regards

Tim
http://www.devshed.com/c/a/PHP/Paginating-MySQL-Data-with-PHP/

ChrisRCh

Tim

Thanks for your reply and the link.

Still working on it :queasy:

Chris

ChrisRCh

The LIMIT clause is not supported in the SELECT SQL subset of the VFP language.

As an alternative using the following code snippit, you can determine the offset, 0 or other integer, and the number of rows, 10 or other integer

$offset = isset($_GET['offset']) ? $_GET['offset'] : 0;
$numrows = isset($_GET['numrows']) ? $_GET['numrows'] : 10;

The following code returns the records detemined by the offset and number of rows

$Query = "SELECT company, street, town FROM contacts";

//execute query
$queryexe = odbc_do($connectionstring, $Query);

//query database
for ($cnt=0; $cnt<$numrows; $cnt++){
    if ($cnt ==0) {
     odbc_fetch_row($queryexe,$offset);
    } else {
     odbc_fetch_row($queryexe);
    } 
    $company = odbc_result($queryexe, 1);
    $street = odbc_result($queryexe, 2);
    $town = odbc_result($queryexe, 3);

//format results
print ("<tr>");
print ("<td>$company</td>");
print ("<td>$street</td>");
print ("<td>$town</td>");
print ("</tr>");
}

In order to return the relevant records for a specific page, presumably, you need to create a function where you pass the offset and number of rows as parameters?

All contributions gratefully received. 🙂

Chris