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

Making a Connection - 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


Now we have set the variables the next stage is to open a connection to MySQL, this should be done as follows:

// Open MySQL Connection $Connection=mysql_connect($DB_Host, $DB_User, $DB_Pass);
We use the mysql_connect() function to open the connection using the variables we defined at the beginning of the script. The link identifier will then be assigned to the variable $Connection which will be used later in the script to obtain our result sets and also when we close the connection to MySQL.

The next step is to run a count query without any limits thus giving us the total number of results, we will use this total number in conjunction with our $Per_Page variable to create the Next / Prev links as appropriate.

// Run The Query Without a // Limit to get Total result $SQL="SELECT COUNT(*) AS Total FROM Products WHERE Description LIKE '".$_REQUEST['Keyword']."'"; $SQL_Result=mysql_db_query($DB_Name, $SQL); $SQL_Result_Array=mysql_fetch_array($SQL_Result); $Total=$SQL_Result_Array['Total'];
The first line is the SELECT statement which tells MySQL what data we are looking for. In this article we are selecting data from a table called 'Products' (Full Table Schema can be found at the end of this page). We use the COUNT(*) function and assign the result AS Total. In some tutorials you may have noticed that instead they use the mysql_num_rows() function. We opted for the COUNT() function due to it's speed. Our WHERE clause indicates that this table has a column called 'Description'. In other words a product description. And we are searching the Description column for all records that contain something LIKE the value of $_REQUEST['Keyword']. We therefore assume that $_REQUEST['Keyword'] is passed to this page via a post form or through the URL, so what we are simulating in this article is a Keyword search.

The second line actually runs the query on the database using the mysql_db_query() function, we first pass the database name and then the SQL query itself. This runs the query and the result identifier is assigned to the $SQL_Result variable. In the third line we use the mysql_fetch_array() function to retrieve the data from the Result. In the fourth line we assign the value to the Total variable.

Now that we have the total result we can reset the SQL variable with a new query and begin to append to the original SQL statement. In order to do this we need to run an if statement that checks the value of $Result_Set, we have not yet touched on this variable yet so I will now explain.

The variable $Result_Set is used to define where to start retrieving results, we pass this variable across through the Next / Prev URL's which we create later in the script and when it reaches this point in the script we use the value in a LIMIT clause. This is probably a little confusing for beginners so I will take us away from the article and explain in a little more depth the LIMIT clause.

>>> 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: