Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations derfloh on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Multi-page query output 1

Status
Not open for further replies.
ChrisRChamberlain

ChrisRChamberlain

Programmer
Mar 23, 2000
3,392
GB
Hi all

PHP newbie so please be gentle. [wink]

The following script retrieves data and displays the results from a Visual Foxpro table.
Code: 
<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

FAQ184-2483 - answering getting answered.​
Chris [pc2]
PDFcommander.com
PDFcommander.co.uk
 
Sort by date Sort by votes
jpadie

I've had to replace '.$.' with $ to avoid
Code: 
Parse error: parse error, unexpected '.', expecting T_VARIABLE or '$' in C:\Server\htdocs\pdfcommander\test4.php on line 15
Otherwise, the code is
Code: 
<?php

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

	//find out how many records are in the table
	$selectQuery = '
			SELECT
				company,
				street,
				town
			FROM
				contacts
			WHERE
				"A" $ company
				OR
				"M" $ company';

	$countQuery = '
			SELECT
				count(*)
			FROM
				contacts
			WHERE
				"A" $ company
				OR
				"M" $ company';

	$result = odbc_exec($countQuery);
	odbc_fetch_row($result,0);
	$numRecords = odbc_result($result, 0);

	$result = null;

	//params
	$rowsPerPage = 10;
	$page = isset($_GET['page']) ? $_GET['page'] : 1;
	$offset = ($page-1) * $rowsPerPage;
	$numPages = ceil($numRecords/$rowsPerPage);
	//end params

	//execute query
	$queryexe = odbc_do($connectionstring, $selectQuery);
	echo <<<EOL
	<style>
	 .currentPageLink{color:red;}
	 .pageLink{color:blue;}
	</style>
	EOL;

	$pagelinks = "";
	for ($i=1; $i<=$numPages; $i++){
		$style = ($i===$page) ? "currentPageLink" : "pageLink";
		$pagelinks .= <<<EOL
		<a href="{$_SERVER['PHP_SELF']}?page=$i" class="$linkstyle">$i</a>
	EOL;

	//query database

	echo "<table>";
	echo "<tr> <td colspan=\"3\">$pagelinks</td></tr>";
	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 result
		print ("<tr>");
		print ("<td>$company</td>");
		print ("<td>$street</td>");
		print ("<td>$town</td>");
		print ("</tr>");
	}
	echo "<tr> <td colspan=\"3\">$pagelinks</td></tr>";
	echo "</table>";

    //disconnect from database
    odbc_close($connectionstring);

?>
The page returns error
Code: 
Parse error: parse error, unexpected $end in C:\Server\htdocs\pdfcommander\test4.php on line 84
line 84 being the last line.


FAQ184-2483 - answering getting answered.​
Chris [pc2]
PDFcommander.com
PDFcommander.co.uk
 
Upvote 0 Downvote
please add a close curly braces "}" before the comment
Code: 
[red]}[/red]
//query database
 
Upvote 0 Downvote
indenting was wrong so skewing the heredoc syntax.
Code: 
<?php

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

//find out how many records are in the table
$selectQuery = "
		SELECT
			company,
			street,
			town
		FROM
			contacts
		WHERE
			\"A\" \$ company
			OR
			\"M\" \$ company";

$countQuery = "
		SELECT
			count(*)
		FROM
			contacts
		WHERE
			\"A\" \$ company
			OR
			\"M\" \$ company";

echo $selectQuery;
$result = odbc_exec($countQuery);
odbc_fetch_row($result,0);
$numRecords = odbc_result($result, 0);

$result = null;

//params
$rowsPerPage = 10;
$page = isset($_GET['page']) ? $_GET['page'] : 1;
$offset = ($page-1) * $rowsPerPage;
$numPages = ceil($numRecords/$rowsPerPage);
//end params

//execute query
$queryexe = odbc_do($connectionstring, $selectQuery);
echo <<<EOL
<style>
 .currentPageLink{color:red;}
 .pageLink{color:blue;}
</style>
EOL;

$pagelinks = "";
for ($i=1; $i<=$numPages; $i++){
	$style = ($i===$page) ? "currentPageLink" : "pageLink";
	$pagelinks .= <<<EOL
	<a href="{$_SERVER['PHP_SELF']}?page=$i" class="$linkstyle">$i</a>

EOL;
}

//query database

echo "<table>";
echo "<tr> <td colspan=\"3\">$pagelinks</td></tr>";
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 result
	print ("<tr>");
	print ("<td>$company</td>");
	print ("<td>$street</td>");
	print ("<td>$town</td>");
	print ("</tr>");
}
echo "<tr> <td colspan=\"3\">$pagelinks</td></tr>";
echo "</table>";

//disconnect from database
odbc_close($connectionstring);

?>
 
Upvote 0 Downvote
Error now
Code: 
SELECT company, street, town FROM contacts WHERE "A" $ company OR "M" $ company
Warning: Wrong parameter count for odbc_exec() in C:\Server\htdocs\pdfcommander\test4.php on line 30

Warning: odbc_fetch_row(): supplied argument is not a valid ODBC result resource in C:\Server\htdocs\pdfcommander\test4.php on line 31

Warning: odbc_result(): supplied argument is not a valid ODBC result resource in C:\Server\htdocs\pdfcommander\test4.php on line 32


FAQ184-2483 - answering getting answered.​
Chris [pc2]
PDFcommander.com
PDFcommander.co.uk
 
Upvote 0 Downvote
my fault. I am not familiar with the odbc calls

change this line
Code: 
$result = odbc_exec($countQuery);
to
Code: 
$result = odbc_exec($connectionstring, $countQuery);
 
Upvote 0 Downvote
Error now
Code: 
SELECT company, street, town FROM contacts WHERE "A" $ company OR "M" $ company
Warning: odbc_result() [function.odbc-result]: Field index is larger than the number of fields in C:\Server\htdocs\pdfcommander\test5.php on line 32
Line 32 being
Code: 
$numRecords = odbc_result($result, 0);

FAQ184-2483 - answering getting answered.​
Chris [pc2]
PDFcommander.com
PDFcommander.co.uk
 
Upvote 0 Downvote
then there is something wrong with your query. I can't help you there as it is taken from your posts.
 
Upvote 0 Downvote
jpadie

Thanks for your patience.

To summarise, there is no LIMIT condition available in a VFP query and the VFP alternative would be a query with subqueries.

This fails under ODBC but should work with the Visual FoxPro OLE DB Provider That would allow you to retrieve n records at any designated offset and move towards being able to code the retrieval of records on a page by page basis.

In considering queries using ODBC, it seems you can use any mix of PHP variables and VFP code within the SELECT-SQL query, excluding subqueries.

The following code and various other derivatives all work
Code: 
<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
	$Start = 100;
	$End = 200;
    $Total = "SELECT COUNT(*) FROM contacts ".
    	"where RECNO() > $Start AND RECNO() < $End ".
		"AND [t] $ company ".
		"AND [p] $ street";
	$Query = "SELECT company, street, town FROM contacts ".
		"where RECNO() > $Start AND RECNO() < $End ".
		"AND [t] $ company ".
		"AND [p] $ street ".
		"ORDER BY town DESC";

    //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>
The $Total variable is there just to test that it works.

The syntax for the VFP OLE DB Provider is different so will post again when the mist clears a bit. [wink]

FAQ184-2483 - answering getting answered.​
Chris [pc2]
PDFcommander.com
PDFcommander.co.uk
 
Upvote 0 Downvote
i understand that limits don't work in vfp. that is why i posted the code that i did. it does not rely on limits or offsets but rather uses php to handle it.

the error message that you got is because your query did not return any rows.

i notice you have changed the syntax of the where clause from using double quotes to square brackets. why not try that syntax in the code i posted?
 
Upvote 0 Downvote
Unfortunately no change

Error now
Code: 
SELECT company, street, town FROM contacts WHERE [A] $ company OR [M] $ company
Warning: odbc_result() [function.odbc-result]: Field index is larger than the number of fields in C:\Server\htdocs\pdfcommander\test4.php on line 32
line 32 being
Code: 
$numRecords = odbc_result($result, 0);
Using the Visual FoxPro OLE DB Provider I believe the code should be
Code: 
$conn = new COM("ADODB.Connection") or die("Cannot start ADO");
$conn->Open("Provider=vfpoledb.1;Data Source=C:\server\tables\contact.dbf;Collating Sequence=machine");
$query = "Select * from contacts";
$result = $conn->Execute($query);
where $result is an ADODB.Recordset.

$query would be structured with subqueries to return n rows at any offset, FI, page 5 would be 20 rows starting at 81.

FAQ184-2483 - answering getting answered.​
Chris [pc2]
PDFcommander.com
PDFcommander.co.uk
 
Upvote 0 Downvote
the error is the same. you are not returning any rows therefore your query is wrong or you have no data matching the criteria.
 
Upvote 0 Downvote
to capture the error, try the following code instead
Code: 
<?php

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

//find out how many records are in the table
$selectQuery = "
        SELECT
            company,
            street,
            town
        FROM
            contacts
        WHERE
            \"A\" $ company
            OR
            \"M\" $ company";

$countQuery = "
        SELECT
            count(*)
        FROM
            contacts
        WHERE
            \"A\" $ company
            OR
            \"M\" $ company";

echo $selectQuery;
$result = odbc_exec($connectionstring, $countQuery);
odbc_fetch_row($result,0);
$numRecords = odbc_result($result, 0);

if ($numRecords === 0) {die ("you have no records that match the query criteria");}

$result = null;

//params
$rowsPerPage = 10;
$page = isset($_GET['page']) ? $_GET['page'] : 1;
$offset = ($page-1) * $rowsPerPage;
$numPages = ceil($numRecords/$rowsPerPage);
//end params

//execute query
$queryexe = odbc_do($connectionstring, $selectQuery);
echo <<<EOL
<style>
 .currentPageLink{color:red;}
 .pageLink{color:blue;}
</style>
EOL;

$pagelinks = "";
for ($i=1; $i<=$numPages; $i++){
    $style = ($i===$page) ? "currentPageLink" : "pageLink";
    $pagelinks .= <<<EOL
    <a href="{$_SERVER['PHP_SELF']}?page=$i" class="$linkstyle">$i</a>

EOL;
}

//query database

echo "<table>";
echo "<tr> <td colspan=\"3\">$pagelinks</td></tr>";
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 result
    print ("<tr>");
    print ("<td>$company</td>");
    print ("<td>$street</td>");
    print ("<td>$town</td>");
    print ("</tr>");
}
echo "<tr> <td colspan=\"3\">$pagelinks</td></tr>";
echo "</table>";

//disconnect from database
odbc_close($connectionstring);

?>
 
Upvote 0 Downvote
Copied and pasted code posted into new .php file.

Error is
Code: 
SELECT company, street, town FROM contacts WHERE "A" $ company OR "M" $ company
Warning: odbc_result() [function.odbc-result]: Field index is larger than the number of fields in C:\Server\htdocs\pdfcommander\test8.php on line 32
where line 32 is
Code: 
$numRecords = odbc_result($result, 0);


FAQ184-2483 - answering getting answered.​
Chris [pc2]
PDFcommander.com
PDFcommander.co.uk
 
Upvote 0 Downvote
mea culpa. i'm being thick. change the 0 to a 1. odbc uses a 1-based numbering rather than a zero based.

 
Upvote 0 Downvote
I assume you mean
Code: 
$numRecords = odbc_result($result, [COLOR=red]1[/color]);
If so, page displayed is
Code: 
SELECT company, street, town FROM contacts WHERE "A" $ company OR "M" $ company
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 etc, etc
down to a total of 1350, presumably the total records found?


FAQ184-2483 - answering getting answered.​
Chris [pc2]
PDFcommander.com
PDFcommander.co.uk
 
Upvote 0 Downvote
no. this is the number of pages. you can adjust how many records display on each page (and thus how many pages there are) in the $rowsperpage variable.
 
Upvote 0 Downvote
Understand now, (did not realise they were links).

If you then click on a page number 266 as a link, the URL becomes
Code: 
[URL unfurl="true"]http://www.pdfcommander.com/test8.php?page=266[/URL]
and the links are repeated under the first lot.

Is that what you are expecting?

FAQ184-2483 - answering getting answered.​
Chris [pc2]
PDFcommander.com
PDFcommander.co.uk
 
Upvote 0 Downvote
yup. the links are repeated above and below the content. easy to change by deleting/commenting this line
Code: 
echo "<tr> <td colspan=\"3\">$pagelinks</td></tr>";
in either or both the locations.

not sure why your query is echoing. comment or delete the echo $selectQuery line to get rid of it. i must have included it for debugging.

 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

c0deM0nK424
  • Locked
  • Question
Gutenberg Block Editor - page edits and updates not working since wp site migration
Replies
1
Views
668
spamjim
spamjim
chrisjchrisj
  • Locked
  • Question
Can you tell me if this file outputs a subcategory page?
Replies
3
Views
204
spamjim
spamjim
Rutsagig
  • Locked
  • Question
mysql_insert_id returns 0 after SELECT query
Replies
0
Views
166
Rutsagig
Rutsagig
dball63
  • Locked
  • Question
Noob Needs some help making HTML edits on PHP page
Replies
6
Views
269
spamjim
spamjim
Wand850
  • Locked
  • Question
Parsing XML with multiple namespaces using PHP
Replies
3
Views
243
spamjim
spamjim

Part and Inventory Search

Sponsor

Back
Top