Paginating MySQL Data with PHP - Making a Connection (
Page 3 of 5 )
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.
/ 47 
