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
You need a LIMIT condition in your SQL query. This code
Code: 
$rowsPerPage = 20;

// by default we show first page
$pageNum = 1;

// if $_GET['page'] defined, use it as page number
if(isset($_GET['page']))
{
    $pageNum = $_GET['page'];
}

// counting the offset
$offset = ($pageNum - 1) * $rowsPerPage;

$query = " SELECT val FROM randoms " .
         " LIMIT $offset, $rowsPerPage";
$result = mysql_query($query) or die('Error, query failed');

... copied from
... should give you the basic idea ;-)
 
Upvote 0 Downvote
dkdude, jpadie

Thanks for your replies.

Code: 
"LIMIT $offset, $rowsPerPage";
is not supported in the SELECT SQL subset of the VFP language.

As both the code posted and the content of the FAQ rely on the LIMIT clause, what alternatives might there be?

FAQ184-2483 - answering getting answered.​
Chris [pc2]
PDFcommander.com
PDFcommander.co.uk
 
Upvote 0 Downvote
Okay -I must have missed the VFP part. Sorry.

You could fetch the entire row array (every time) and pass an ofset var to go with each page view?
 
Upvote 0 Downvote
i'd be interested to hear the solution. My belief is that with languages that do not accept LIMIT clauses you have no choice but to return the whole recordset. having said that you do not need to read the recordset into an array, you can seek straight to the right offset.

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

// i do not understand this query.  it does not look well formed.

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

    //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>");
}

    //disconnect from database
    odbc_close($connectionstring);
[/close]
 
Upvote 0 Downvote
jpadie

I have part of a solution using VFP SELECT - SQL
Code: 
[COLOR=blue]lnRows = 20
lnOffset = 200

SELECT TOP lnRows company;
    FROM C:\tables\contacts;
    WHERE company IN;
    (SELECT TOP lnOffset company FROM C:\tables\contacts ORDER BY company ASC);
    ORDER BY company DESC;
    INTO CURSOR temp[/color][COLOR=green] && Correct records in reverse order[/color]
';' means continue on next line
'INTO CURSOR temp' would be removed for PHP
lnOffset would become $offset
lnRows would become $numrows

The code selects rows 180 to 200 but currently in reverse order which is yet to be resolved.
Code: 
[COLOR=blue]SELECT company, street, town FROM contacts where 'A' $ company OR 'M' $ company[/color]
is correct, the '$' operator meaning that the preceding string is to be found anywhere in the following field.

Where this is probably going to fail is in the inclusion of '$variable' in SELECT -SQL statement.

Is there any other means of declaring a variable in PHP without using '$'?

Thanks for posting your code - it looks interesting and will get to it ASAP.

FAQ184-2483 - answering getting answered.​
Chris [pc2]
PDFcommander.com
PDFcommander.co.uk
 
Upvote 0 Downvote
variables must use the $ sign. however you can use the dollar with impunity within single quotes.

so you could reformulate your sql thus (spaced for easier parsing)

Code: 
$Query =  '
	SELECT 
		company, 
		street, 
		town 
	FROM 
		contacts 
	WHERE 
		"A" $ company 
		OR 
		"M"' $ company 
	';
 
Upvote 0 Downvote
This is what I had in mind after looking at the previously posted code and FAQ which would enable you to 'step through' the query, $numrows per page.
Code: 
$Query = 'SELECT TOP [COLOR=red]$numrows[/color] company;
    FROM C:\tables\contacts;
    WHERE company IN;
    (SELECT TOP [COLOR=red]$offset[/color] company FROM contacts ORDER BY company ASC);
    ORDER BY company DESC'
VFP will undoubtedly choke on the variables highlighted because of the use of '$' as part of the variable. [sad]

FAQ184-2483 - answering getting answered.​
Chris [pc2]
PDFcommander.com
PDFcommander.co.uk
 
Upvote 0 Downvote
it's alright. you can work around this

Code: 
$Query = 'SELECT TOP '.$numrows.' company;
    FROM C:\tables\contacts;
    WHERE company IN;
    (SELECT TOP '.$offset.' company FROM contacts ORDER BY company ASC);
    ORDER BY company DESC'
 
Upvote 0 Downvote
Surprise, surprise!

The following code does work
Code: 
$numrows = 20;
$Query = "SELECT TOP $numrows company, street, town " .
[tab]"FROM contacts where 'A' $ company OR 'M' $ company " .
[tab]"ORDER BY company ASC";
So hopefully it should work with the extended SELECT-SQL statement.

FAQ184-2483 - answering getting answered.​
Chris [pc2]
PDFcommander.com
PDFcommander.co.uk
 
Upvote 0 Downvote
i am very surprised that the dollar signs in the string translate to dollar signs in the output.

anyway => good luck!
 
Upvote 0 Downvote
jpadie

Thanks for your help so far.

Have yet to resolve the SELECT-SQL and any remaining PHP issues but it's looking hopeful. [smile]

Will post the final code when it's complete.

FAQ184-2483 - answering getting answered.​
Chris [pc2]
PDFcommander.com
PDFcommander.co.uk
 
Upvote 0 Downvote
In order to determine the total records, the following code is used
Code: 
//SQL query
$gettotal = "SELECT COUNT(*) FROM contacts where 'A' $ company OR 'M' $ company";

//execute query
$recdtotal = odbc_do($connectionstring, $gettotal);

echo $recdtotal;
The value of '$recdtotal' is 'Resource id#3' as opposed to the record total.

What's missing, please?

FAQ184-2483 - answering getting answered.​
Chris [pc2]
PDFcommander.com
PDFcommander.co.uk
 
Upvote 0 Downvote
the odbc_do command is an alias for odbc_exec. the result is just a resource handle.

once you have the resource handle you then need to read the records pointed to by the handle.

you need to use odbc_fetch_row on the resource
 
Upvote 0 Downvote
Sadly the subquery, not the use of '$' as expected, in the SELECT-SQL statement is proving too much.
Code: 
Warning: odbc_do() [function.odbc-do]: SQL error: [Microsoft][ODBC Visual FoxPro Driver]Command is missing required clause., SQL state 37000 in SQLExecDirect in C:\Server\htdocs\pdfcommander\test3.php on line 37
To get the records in correct order it requires a second subquery nested within the first just to compound the problem.

In the code posted earlier
Code: 
$offset = isset($_GET['offset']) ? $_GET['offset'] : 0;
$numrows = isset($_GET['numrows']) ? $_GET['numrows'] : 10;
the first line determines the offset to be the value of '0' or any other integer substituted.

How can this be used in a loop to step through the pages?

FAQ184-2483 - answering getting answered.​
Chris [pc2]
PDFcommander.com
PDFcommander.co.uk
 
Upvote 0 Downvote
does the code that i posted above not seek to the right offset?

if i have misunderstood, what do you mean?
 
Upvote 0 Downvote
I am misunderstanding the use of your code.

The following example uses code from the FAQ plus the code snippit posted.

By way of example, the code has been modified to retrieve 20 rows beginning at row 4000 in the filtered query, otherwise it would return 10 rows starting at row one.
Code: 
//find out how many records are in the table
$count_query = "SELECT company, street, town FROM contacts where 'A' $ company OR 'M' $ company";
$rh = mysql_query ($count_query);
list ($record_count) = mysql_fetch_array($rh);

//calculate the maximum "page" that can be displayed.
$max_pages = floor($record_count / $records_per_page);

//This logic takes care of reacting to input.
if (isset($_GET['page']))
{
if ($_GET['page'] > 1)
{
	if ($_GET['page'] > $max_pages)
	{
		$current_page = $max_pages;
	}
	else
	{
		$current_page = $_GET['page'];
	}
}
else
{
	$current_page = 1;
}
}
else
{
$current_page = 1;
}

$offset = isset($_GET['offset']) ? $_GET['offset'] : 4000;
$numrows = isset($_GET['numrows']) ? $_GET['numrows'] : 20;

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

//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>");
}
So how do you combine your snippit with the FAQ or is this going in the wrong direction?

FAQ184-2483 - answering getting answered.​
Chris [pc2]
PDFcommander.com
PDFcommander.co.uk
 
Upvote 0 Downvote
i'm not sure how you got to the above - it seems your combining odbc and mysql in the same script. are you using both mysql and foxpro?

assuming you are just using foxpro (and assuming your connection works ok), try the following code
Code: 
<?php
//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>";
?>
 
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