Code Examples -> A template script to provide the ability to get the next or previous n records from a MySQL database.

	Join us on FaceBook
	Submit Your Code
	Poker Tournaments
	Poker Guide for Developers
	SEO Monitor
	UpTime Monitor
	My Favorite Examples
	My Favorite Articles
	Update Your Profile

More Weber Sites
	PHP Code Search
	Web Development Forums
	Learn MySQL Playing Trivia
	PHPBB2 Templates
	Web Development Resources
	Web Development Content
	Internet Security Software

Recommended Links
	PHPClasses
	PHP Editor
	PHP Jobs
	Vision.To Design
	Ajax Tutorials
	PHP Programming Help
	PHP/MySQL Programming
	Webmaster Resources
	Webmaster Forum
	XML meta language
	website builder

Recommended
	פרייסז - הכח לקנות עובר לידיים שלך
	Texas Holdem Poker Evangelists


BACK		ADD A COMMENT		SEND TO A FRIEND		ADD TO MY FAVORITES		ADD CODE EXAMPLES		PRINT

PHP Code Examples

Title :	A template script to provide the ability to get the next or previous n records from a MySQL database.
Categories :	Databases, PHP, MySQL	David Robley
Date :	Jan 17th 1999
Grade :	3 of 5 (graded 10 times)
Viewed :	19801
File :	No file for this code example.
Images :	No Images for this code example.

Search :	More code by David Robley
Action :	Grade This Code Example
Tools :	My Examples List

Code

<?php
require( "mysqlcheck.inc"); /* Check the mysql daemon */
/*
This is a template for a script that will return the next n records from a MySQL
database query. It uses the LIMIT option in the MySQL SELECT syntax

Written by David Robley July 1998
Comments, improvements etc to webmaster@www.nisu.flinders.edu.au

Data Dictionary
* $fgcolour Foreground colour of document
* $bgcolour Background colour of document
* $leftarr Image to use for Previous image
* $rightarr Image to use for Next image
* $database Database to query
* $table Table to query
* $lower Lowest number of the records currently on display
* $upper Highest number of the records currently on display
* $NUM_RECS Number of records to be diplayed per page
* $offset Use for OFFSET value in 'SELECT' statement
* $searchon Field to be searched on
* $searchfor Value to be searched for
*/

/* formDropDown - create an HTML <SELECT> - By John Bryant
* vars: $name - the form variable NAME
* $value - the SELECTED option
* $labels - assoc. array, list of values=>labels
* returns: string, HTML (i.e. for use in echo or print statement)
*/

function formDropDown($name,$value,$labels) {
$html = "<SELECT NAME=\"$name\">\n";
$key = key($labels);
while($key != "") {
if ($key == $value) {
$selected = "SELECTED";
} else {
$selected = "";
}
$html .= "<OPTION VALUE=\"$key\" $selected>$labels[$key]\n";
next($labels);
$key = key($labels);
}
$html .= "</SELECT>\n";
return $html;
}
/* Some attributes that can be changed */
/* Set fore and background colour values */
$fgcolour = "#009999";
$bgcolour = "#000033";
/* And the images to use for arrows. */
$leftarr = "/gifs/cyan_left.gif";
$rightarr = "/gifs/cyan_right.gif";

/* Name of the database and table to use for the query */
$database = "database";
$table = "table";
?>
<!DOCTYPE HTML PUBLIC "-//AdvaSoft//DTD HTML 3.2 experimental 961018//EN">
<HTML>
<HEAD><TITLE>Title</TITLE></HEAD>

<BODY BGCOLOR=" <?php echo $bgcolour?>" TEXT=" <?php echo $fgcolour?>">
<H1>Search</H1>
Search for references.
<P>
<HR>
<FORM ACTION=" <?php echo $PHP_SELF?>" METHOD="POST">
<CENTER>
<TABLE ALIGN=center CELLSPACING="15" BORDER=0>



<TR><TD ALIGN="CENTER">View the results
<?php
/* This builds a dropdown list to provide the choices for number
of records to display per page
*/

$labels = array( "5"=> "5", "10"=> "10", "15"=> "15", "20"=> "20");
$match = (empty($NUM_RECS) ? "5" : $NUM_RECS);
$box = formDropDown( "NUM_RECS",$match,$labels);
echo $box;
?>
per page
<TD ALIGN=center><INPUT TYPE=submit NAME="search" VALUE="Search">
<TD ALIGN=center><INPUT TYPE=reset></TD>
</TABLE></CENTER>
</FORM>

<HR>
<?php

/* First time through on a new search $offset should be zero,
$lower should be one and $upper should be $NUM_RECS
*/
if(empty($lower)):
$lower = 1;
endif;
if(empty($upper)):
$upper = $NUM_RECS;
endif;
if(empty($offset)):
$offset = 0;
endif;

if(!empty($next_x)): /* The Next button has been selected */
$lower += $NUM_RECS;
$upper += $NUM_RECS;
$offset += $NUM_RECS;
endif;

if(!empty($prev_x)): /* The Previous button has been selected */
$lower -= $NUM_RECS;
$upper -= $NUM_RECS;
$offset -= $NUM_RECS;
endif;

/* echo "Offset: " . $offset . ": Lower: " . $lower . ": Upper: " . $upper . "<BR>"; */

switch($search):
case "Search":
/* Build the query string from the data passed in. */

$NUM_RECS = intval($NUM_RECS);
$query = "SELECT * FROM " . $table . " WHERE " . $searchon;
$query .= " LIKE '%" . $searchfor . "%' LIMIT " . ($offset != 0 ? "$offset, " : "$NUM_RECS");
/* Countquery gets the number of records that are be retuned by the 'real' query */

$countquery = "SELECT count(*) AS numrows FROM " . $table. " WHERE " . $searchon;
$countquery .= " LIKE '%" . $searchfor . "%'";

$countresult = mysql_db_query($database, $countquery);
$countrow = mysql_fetch_array($countresult);
$result = mysql_db_query($database, $query);
$numrows = intval($countrow[0]);
?>
<IMG SRC="/gifs/searchresults.gif" ALT="Search Results" WIDTH="480" HEIGHT="15"><P>

<?php
switch($numrows):
case 0:
echo "There were no matches to your search for <FONT COLOR=\"#FF0000\">" . $searchfor;
echo "</FONT> in " . $searchon . ". You may wish to try again with a different search phrase.";
break;

default:
/* (!empty($offset) ? $lower = ($offset - $NUM_RECS) + 1 : $lower = 1);
$upper = $offset + $NUM_RECS; */
echo "<FONT FACE=\"LucidaTypewriter, Arial, Helvetica\">";

if($numrows <= $NUM_RECS):
echo "Displaying all ". $numrows;
else:
echo "Displaying matches " . $lower . " to " . ($upper > $numrows ? $numrows : $upper). "
of " . $numrows;
endif;
echo " matches for '<FONT COLOR=\"#FFFFFF\">" . $searchfor . "</FONT>'</FONT>";
while($row = mysql_fetch_array($result)):
?>
<P>

<! Lay out your returned values here however you like -->

<HR>

<?php
endwhile;
break;
endswitch; /* Numrows */

break;
default: /* No search - throw up the search entry form */
endswitch; /* Search */

if($numrows > $NUM_RECS):
?>
<CENTER>
<FORM ACTION=" <?php echo $PHP_SELF?>" METHOD="POST">
<TABLE ALIGN="CENTER"><TR VALIGN=MIDDLE CELLSPACING="10">
<TD WIDTH="31">

<?php if($lower != 1): /* Show the left arrow and text */ ?>
<INPUT NAME="prev" TYPE=image SRC=" <?php echo $leftarr?>" BORDER="0" WIDTH="31" HEIGHT="31" ALT="<<=">
<TD><FONT COLOR=" <?php echo $fgcolour?>">
<?php else:?>
<TD><FONT COLOR=" <?php echo $bgcolour?>">
<?php endif;?>

Previous <?php echo $NUM_RECS?> matches</FONT>
��
<TD>��

<?php if($upper < $numrows): /* Show the right arrow and text */ ?>

<FONT COLOR=" <?php echo $fgcolour?>">Next <?php echo $NUM_RECS?> matches</FONT><TD WIDTH="31">
<INPUT NAME="next" TYPE=image SRC=" <?php echo $rightarr?>" BORDER="0" WIDTH="31" HEIGHT="31" ALT="=>>">
<?php else:?>
<FONT COLOR=" <?php echo $bgcolour?>">Next <?php echo $NUM_RECS?> matches</FONT><TD WIDTH="31">
<?php endif;?>

</TABLE>


<INPUT TYPE="HIDDEN" NAME="searchon" VALUE=" <?php echo $searchon?>">
<INPUT TYPE="HIDDEN" NAME="searchfor" VALUE=" <?php echo $searchfor?>">
<INPUT TYPE="HIDDEN" NAME="NUM_RECS" VALUE=" <?php echo $NUM_RECS?>">
<INPUT TYPE="HIDDEN" NAME="search" VALUE=" <?php echo $search?>">
<INPUT TYPE="HIDDEN" NAME="offset" VALUE=" <?php echo $offset?>">
<INPUT TYPE="HIDDEN" NAME="upper" VALUE=" <?php echo $upper?>">
<INPUT TYPE="HIDDEN" NAME="lower" VALUE=" <?php echo $lower?>">
</FORM>
</CENTER>

<?php
endif;
?>

</BODY>
</HTML>

Related Code Examples

	bookmarker - PHP, PHPLIB, MySQL WWW based bookmark manager Categories : MySQL, PHP, MySQL, Complete Programs, Databases
	This program allows you to upload an ODBC ressource - i.e. an MS-Access database to a MySQL server. Categories : Databases, MySQL, Complete Programs, PHP, Databases
	Identify and log search engine access (spiders, robots, etc.) to a page. Categories : HTTP, Environment Variables, PHP, MySQL, Databases
	MyOrgBook - Online Organizer written in PHP & MySql. Includes online contacts, todo, calendar, update, delete, insert, multi-user interface. Categories : PHP, MySQL, Calendar, PDAs and Organizers, Databases
	Simple usersOnline class - keep track of how many users are online on your site Categories : PHP, PHP Classes, Databases, MySQL
	A simple script to count and report hits and the last modification time of an HTML page. Requires MySQL support (other DBs should work too, except possibly mSQL). Categories : HTTP, MySQL, PHP, Databases
	MySQL Web Interface Categories : MySQL, Databases, PHP
	A PHP useradmin for MySQL. Uploading is easy. The only thing you need is PHP and MySQL installed! Categories : MySQL, Databases, PHP, HTML and PHP
	PostGreSQL and MySQL 2 in 1 db Manager Categories : PHP, PHP Classes, Databases, PostgreSQL, MySQL
	AJAX Data Grid System using php and mysql. A complete login system with the ability to display data in a grid using ajax. Add , update and delete the records without reloading the page. Categories : PHP, AJAX, Databases, MySQL, Java Script
	Loading Images to/from MySQL Categories : Databases, MySQL, PHP, Graphics
	Pull Down Surfing - Surf on Change Categories : Java Script, MySQL, HTML and PHP, PHP, Databases
	Solution to those 'tell-a-friend' type email issues Categories : PHP, Email, Databases, MySQL
	PHP Object Example of the Perl DBI with MySQL Categories : PHP, PHP Classes, MySQL, Databases, Perl
	[PHP5] aDB PDO LIKE Database Abstraction. Switch easily from one db server to another, strong errors management, manage transactions, queries preparation and more. Categories : PHP, PHP Classes, Databases, MS SQL Server, MySQL

User Comments

	Erwin Penders wrote : 7 `Hi, great script, but it doesn't work 100% ! Can you add the mysqlcheck.inc so i can find the fault. (when i search for the result, and i press the next button i get a MySQL error.) Thanks!`
	David Robley wrote : 13 `BUG The line $query .= "LIKE '%" . $searchfor . "%' LIMIT " . ($offset != 0 ? "$offset, ": "$NUM_RECS"); should be: $query .= "LIKE '%" . $searchfor . "%' LIMIT " . ($offset != 0 ? "$offset,$NUM_RECS":"$NUM_RECS"); This will cause the error ") is not a Mysql index" in second searches.`
	Sharaaz Khan wrote : 21 `I tried the bug fix, but the same error persists. Any idea why? Following is the error: Warning: 0 is not a MySQL result index in ./../next.php3 on line 145`
	Helge Schacht wrote : 23 `This one is fine but- ist there a way to do this in one query? --> select * from table where something LIMIT 0,10; and read the number of affected rows? This would speed up the query if there is a large number of records!`
	Byungjin Chang wrote : 48 About the error that Khan Sharaaz`s getting, it might be the missing space between the query field. Try adding a space in front of `LIMIT`. Also, try echoing out the query and see if the query is correct.
	Boaz Yahav wrote : 49 `Hi Many times, if you copy/past from a browser window you get some wierd chars instead of spaces. I usually paste into notepad (fixed width font editor) and then copy paste that into my file`
	Tony Colclough wrote : 359 `Great useful little tool, just one problem I spent ages trying to work out... The hidden field for Search that is passed to the next page when the next button is clicked passes Search with a space in front of it, so I had to remove the space before the PHP code for it to work. Thanks`
	Edward Apostol wrote :558 `Can anyone tell me where I can find a copy of the include file mysqlcheck.inc that is mentioned in this script? I would like to try it out but I do not have an idea where the file is found. Your assistance is appreciated.`