Home arrow PHP arrow Page 4 - Paginating MySQL Data with PHP

Limiting the Dataset - PHP

Have a large MySQL dataset that you would like to display on your website, but don't want to bog down your users' connections with large HTML renderings? Try paginating your data.

  1. Paginating MySQL Data with PHP
  2. Getting Started
  3. Making a Connection
  4. Limiting the Dataset
  5. Conclusion
By: Adrian Portsmouth
Rating: starstarstarstarstar / 53
March 27, 2003

print this article


When we issue a LIMIT clause to MySQL we pass across two parameters, you can pass just a single parameter but we need to use them both for this type of script. This is an example of an SQL statement with a LIMIT clause:

// Example, this is NOT used in our script $SQL="SELECT * FROM Products WHERE Description LIKE '".$_REQUEST['Keyword']."' LIMIT 10, 20";
Lets say for example sake that this query when run without the LIMIT clause gives us 30 records in our result, by entering the LIMIT clause it is telling MySQL to start at row 10 and retrieve 20 records. So it will skip records 1 through to 9 and return 10 through to 30.

Ok back to the article, here is the if statement and SQL we will be using:

// Create a new SELECT Query with the // ORDER BY clause and without the COUNT(*) $SQL="SELECT * FROM Products WHERE Description LIKE '%".$_REQUEST['Keyword']."%' ORDER BY ProductID"; // Append a LIMIT clause to the SQL statement if (empty($_GET['Result_Set'])) { $Result_Set=0; $SQL.=" LIMIT $Result_Set, $Per_Page"; }else { $Result_Set=$_GET['Result_Set']; $SQL.=" LIMIT $Result_Set, $Per_Page"; }
Because we no longer need the original SELECT query we can assign a new one using the same $SQL variable, our new query above no longer contains the COUNT() function because we actually want to return the data from the table and it also contains an ORDER BY clause using the ProductID, it is important to include the ORDER BY clause to ensure that each time a next or prev link is clicked, the query sorts the data in the same order. Without an ORDER BY statement is pretty much useless creating a page system.

Ok, so the first line of the if statement simply says that if $_GET['Result_Set'] is not present, it has not been passed to the script. Next we run the first {} loop, if it is present then we run the else {} loop. Because we need to pass two parameters with the LIMIT clause the first thing we do in the first of the loops (Line Three) is to assign $Result_Set a value of 0. As per our previous example this means the LIMIT clause will return results from row 0 of the result set. The next line appends our LIMIT clause to our SQL statement, this is done by using a period prior to the equals sign. This tells PHP that we are not creating or overwriting the existing variable but appending to it. (Notice the space between the first double quote and the first letter of LIMIT) So if we were running this script right now and $Result_Set has no value, then it will go through the first of our loops and our variable $SQL will now look like this:

$SQL="SELECT * FROM Products WHERE Description LIKE '$Keyword' LIMIT 0, 10";
The else {} loop will come into play when the variable $_GET['Result_Set'] does have a value (If someone has clicked on one of our Next / Prev links) in this instance we set $Result_Set with the value passed in the URL.

Now that we have completed appending to our SQL statement we now need to run the statement like so:

// Run The Query With a Limit to get result $SQL_Result=mysql_db_query($DB_Name, $SQL); $SQL_Rows=mysql_num_rows($SQL_Result);
This code should look familiar as we used it earlier when we ran the first SQL statement therefore I do not need to explain the functions again but moreover the logic. This code will run our query and then count the results, you are probably thinking, 'Why count them when we have specifically said how many results to select?' Well, the reason we need to count them is in-case there are an odd number of results. If our original query returns 53 results, and the user has clicked our Next buttons five times and we are displaying 10 records per page, then we need to know that we only need to display 3 records on this the last page.{mospagebreak title=Display the Data} We should now display our results to the user, this is the area where you will need to make a lot of changes in order to display your data, but I will include an example based on our article's database of products:

// Display Results using a for loop for ($a=0; $a < $SQL_Rows; $a++) { $SQL_Array=mysql_fetch_array($SQL_Query); $Product=$SQL_Array['Name']; $Description=$SQL_Array['Description']; echo "$Product - $Description<BR><BR>"; }
The first line of this code sets the conditions of the loop, $a starts off with the value of 0, while the value of $SQL_Rows is greater than the value of $a then the loop will run, however the last condition '$a++' changes the value of $a so that each time it goes through the loop it adds 1 to the value of $a. Thus eventually the value of $a will match the value of $SQL_Rows and the loop will no longer run. The three lines of code within the loop start by retrieving the data from the result set and assign the values to a variable before echoing them out to the screen. The next step is to produce the Next / Prev links and display them as the script dictates. We also need to pass across the URL anything which is included in the SQL Statement, in this case $_REQUEST['Keyword'] and also $Result_Set which we will define during the creation of the Next / Prev links, you will need to modify the links if you pass any other variables across which are used in the SQL Statement. For example if you add a product type to the table and your search form then you will need to append it to each of the links below so that it is carried through the URL for the next page to use in the query.

// Create Next / Prev Links and $Result_Set Value if ($Total>0) { if ($Result_Set<$Total && $Result_Set>0) { $Res1=$Result_Set-$Per_Page; echo "<A HREF="test.php?Result_Set=$Res1&Keyword=".$_REQUEST['Keyword'].""> <;<; Previous Page</A> "; } // Calculate and Display Page # Links $Pages=$Total / $Per_Page; if ($Pages>1) { for ($b=0,$c=1; $b < $Pages; $b++,$c++) { $Res1=$Per_Page * $b; echo "<A HREF="test.php?Result_Set=$Res1&Keyword=".$_REQUEST['Keyword'].""> $c</A> n"; } } if ($Result_Set>=0 && $Result_Set<$Total) { $Res1=$Result_Set+$Per_Page; if ($Res1<$Total) { echo " <A HREF="test.php?Result_Set=$Res1&Keyword=".$_REQUEST['Keyword'].""> Next Page >></A>"; } } }
The first thing to take notice of in this code are the three echo statements, these make up the URL that will be printed. In this code we have named the file result.php that means you should name the file you are creating result.php, if you name it something else then you should change the name in all of the above echo statements.

As you can probably gather this is the more complex part of this script, the first line starts us off with an if statement, this checks to see if we have any records at all in our result. So if the value of $Total is not greater than 0 we obviously have no records in our result and we will not need to display Next / Prev links. If we do have some results then the second if statement (line 3) will determine if we need to display a Prev link, obviously if this is the first page of the result set then we don't want to display a Prev Link. The condition of this if statement checks to see if the value of $Result_Set is less than the value of $Total and greater than 0 then we will display a Prev link, if $Result_Set is not less than $Total and greater than 0 then we are on the first page of our results and do not need a Prev link. Line 5 of the code is the math which creates the value of $Result_Set for the Prev Link if it is being displayed. This simply takes the value of $Result_Set and subtracts the value of $Per_Page which we defined at the top of the script, we assign this value to the variable $Res1 and then echo the value in the URL as the new $Result_Set value for the Prev link (line 6). (Remember to append any variables you are passing through the scripts to all of the URL's that we echo out).

Line 9 is the start of the calculation for displaying page numbers for our result set, so a user can go directly to the page they think is most relevant to what they want. This is done by first calculating how many pages we need to display, we use the value of $Total and divide it by the value of $Per_Page. We then start a new if statement and check that there are more than 1 pages because we don't need to display any page numbers at all if there is no more than one page. The next step is a rather complex for statement (line 12). We define two variables $b and $c, both of these variables increment by 1 each time the loop is processed so the only difference is that $b starts at 0 and $c starts at 1. The reason being that we need a zero value in order to loop the correct number of times against the value of $Pages but we don't want our page number display to start at 0, instead we want it to start at 1 so we use the $c variable as the number we display in our page. The $Res1 calculation in this if statement is very simple, we take the value of $b and multiply it by the value of $Per_Page before using it in our URL.

The fourth if statement first checks that $Result_Set is equal or greater than 0, this indicates that we need a Next link, it also checks that the value of $Result_Set is less than the value of $Total as we don't want to display a link if there are no more results to display. Both of these must be true before a Next link will be displayed. The script then creates a value for $Res1 which will be passed across the URL in the Next link, again some simple math, we take the value of $Result_Set and add the value of $Per_Page. So if we are currently displaying results 20 through to 30 in our next page we need to start from 30 and display through to 40 (if there are that many records). So our $Result_Set is currently set to 20 and our $Per_Page is set to 10, add them together and we now have 30 assigned to $Res1. We then run one more if statement to check that the new value of $Res1 is less than the value of $Total before displaying the Next link. If $Res1 now equals 30 in our example situation and $Total only equals 29 then we do not want to display a link, but the fourth if statement will come through as true, so the fifth if statement is there to prevent the link being displayed in this situation.

The last thing we do in our script is close the MySQL connection, you should remember to do this each time you open a connection to MySQL. This is done as follows:

// Close Database Connection mysql_close($Connection);
Notice how we use the variable $Connection which we assigned earlier in the script.

>>> More PHP Articles          >>> More By Adrian Portsmouth

blog comments powered by Disqus
escort Bursa Bursa escort Antalya eskort


- Hackers Compromise PHP Sites to Launch Attac...
- Red Hat, Zend Form OpenShift PaaS Alliance
- PHP IDE News
- BCD, Zend Extend PHP Partnership
- PHP FAQ Highlight
- PHP Creator Didn't Set Out to Create a Langu...
- PHP Trends Revealed in Zend Study
- PHP: Best Methods for Running Scheduled Jobs
- PHP Array Functions: array_change_key_case
- PHP array_combine Function
- PHP array_chunk Function
- PHP Closures as View Helpers: Lazy-Loading F...
- Using PHP Closures as View Helpers
- PHP File and Operating System Program Execut...
- PHP: Effects of Wrapping Code in Class Const...

Developer Shed Affiliates


Dev Shed Tutorial Topics: