Making a Connection - PHP

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