Basic PHP Principles to Interface MySQL Server

Conventional Web Development with PHP and MySQL – Part 2

Forward: In this part of the series, we look at the basic PHP principles used to interface the MySQL database server.

Introduction

This is part 2 of my series, Conventional Web Development with PHP and MySQL. I assume you have read the previous part of the series, before reaching here. In this part of the series, we look at the basic PHP principles used to interface the MySQL database server.

Information from the web page at the clients computer meant for the MySQL database has to be received by a script at the server first. It is this server script that will send the information to the database, which is also in the server host computer (everything being equal). Information flow in the reverse direction from the database passes through the script before reaching the client. In this series the scripts at the server are written in PHP. Everything taught in this part of the series can be placed in one server script. In practice, some of the principles would be used in one script, while others would be used in other scripts. You can have more than one PHP server script in an application (web development).

The $_GET and $_POST Arrays
In PHP, $_GET and $_POST are two superglobal variable arrays. $_GET corresponds to the GET method of the HTML Form. $_POST corresponds to the POST method of the HTML Form. So, if the dataset of the Form in a web page is sent using the GET method or if the client clicks a hyperlink on a web page that has the dataset attached to it, then the PHP script at the server will receive the dataset with the $_GET array, automatically. If the dataset of the Form in the web page is sent using the POST method, then the PHP script will receive it with the $_POST array, automatically.

Consider a simple Form, which has just the following two input text controls and the submit button:

    <input type="text" name="firstname">
    <input type="text" name="lastname">

To receive the name/value pairs (dataset) of these two controls, you would type something like the following in the PHP script (at the top) for the GET method:

    $firstname = $_GET['firstname'];
    $lastname = $_GET['lastname'];

For the POST method, replace $_GET with $_POST in these two statements. Down in the PHP script, you can then use the $firstname and $lastname variables conveniently.

Connecting to the Database Server

For the client computer to communicate with the database in the Internet server, a kind of network link has to be established. This link is called the connection. There are two types of connection: non-persistent and persistent connections. In this series, we use only the non-persistent connection. The PHP server scripts are responsible for making this connection. With non-persistent connection, the connection closes automatically when the server script completes its execution (from top to bottom).

The syntax for the non-persistent connection function to the database server is:

    resource mysql_connect (string $server, string $username, string $password)

An example is:

    $link = mysql_connect('localhost', 'john', 'psswrd');

The return value is a resource and in this example it is assigned to the variable, $link. In this example, the server host (computer) is localhost, meaning that the PHP script and the database server are in the same computer. You can have a domain name or IP number in place of the word, localhost.

If the database server needs a port number in order to be accessed, the website hosting company will tell you so. In that case you will have something like:

    $link = mysql_connect('localhost:3307', 'john', 'psswrd');

where 3307 is a port number.

The script needs the returned resource ($link) in order to send SQL statements to the server. This is because, when the database server receives a SQL statement, it needs to know the connection (user name, password and sending host) that the SQL statement belongs to.

The above function (mysql_connect) returns a resource if the connection is made (successful); otherwise it returns FALSE. If the connection is successful, it stays until the end of the script. If you want the connection to close before the end of the script, then type the following function call, where you want the connection to close, in the script:

    mysql_close($link);

In this call, $link is the returned resource of the non-persistent connection function above. For the mysql_connect and mysql_close function, you can use some other variable name instead of $link. The return resource is sometimes called, Link Identifier.

The SQL statements for the database server are typed in the script between the mysql_connect function and mysql_close function (or end of script).

Functions for SQL Statements

To use any database in a MySQL server, you have to select the database first. The syntax for the function for this is:

    bool mysql_select_db ( string $database_name, resource $link_identifier )

This function returns TRUE if it succeeds or FALSE if it fails. An example of its use is:

        $db_selected = mysql_select_db('petShop', $link);

        if (!$db_selected)
                    {
                        echo "<body style='background-color:lightblue'><h3>The database could not be selected.</h3>";
                    }

If you do not select a database, then you may want to create one. I normally create databases, tables, stored procedures and triggers using the mysql client (command prompt) program. However, the insert, update, delete and select statements are typically done using the PHP script (or Perl script or some other script).

You can still use PHP to create a databases or tables for a MySQL server.

To send a SQL statement to the database, you first of all have to form a string whose content is the SQL statement without the ending semicolon. The following examples illustrate this. There has to be a semicolon after the string; this is for the PHP statement and not the SQL statement.

    $createDbStr = "create database petShop";

    $createTblStr = "create table animal (name varchar(20), owner varchar(20), species varchar(20), sex char(1), birth date, death date)";

    $insertStr = "insert into animal values ('Puffball','Diane','hamster','f','1999-03-30',NULL)";

    $updateStr = "update animal set birth = '1989-08-31' where name = 'Bowser'";

    $deleteStr = "delete from animal";

    $selectStr = "select * from animal";

The SQL statements you are likely to be using in your PHP script are the Create Table, Create Database, Insert, Update, Delete and Select statements. For these statements I use Insert, Update, Delete and Select in PHP scripts. I use Create Database and Create Table in the mysql client program. However, for these 6 SQL statements only Select returns a resultset (resource) on success; the rest return TRUE on success and False on failure. Select returns a resultset on success and FALSE on failure.

The mysql_query Function
PHP has a function called, mysql_query. The syntax is:

    resource mysql_query (string $query, resource $link_identifier)

Each of the above strings for the SQL statements would be the first argument for this function. This is the function that PHP uses to send a SQL statement to the MySQL database server. For the Select SQL statement, on success, the function returns a resource, which is the resultset, from which data rows are obtained; on failure for a Select statement, the function returns FALSE. For the rest of the SQL statements above, the function returns TRUE on success and FALSE on failure.

The following code segment creates a MySQL database and selects it:

    $link = mysql_connect('localhost', 'john', 'psswrd');
    if (!$link)
        {
            echo "<body style='background-color:lightblue'><h3>Connection could not be made! Check username and/or password, and try again.</h3>";
        }
    else
        {
            $createDbStr = "create database petShop";
            if (!mysql_query($createDbStr, $link))
             {
            echo "<body style='background-color:lightblue'><h3>The database could not be created! You may have to rename the database and try again.</h3>";
             }
            else
                {
                     mysql_select_db('petShop', $link);
                }

            mysql_close($link);
        }

In the above code, note how the link identifier has been used in the PHP MySQL functions while we expect the connection to be on. In this way, the database server knows who is executing what function (and what SQL statement).

Note: if $link is omitted in the functions, then PHP will use the last successful link identifier.

Obtaining Data Rows from the Resource Resultset

You need to obtain the data rows from the return resource resultset, if the SQL statement for the mysql_query function, is Select. You need other PHP functions for this. These new functions are mysql_fetch_assoc and mysql_fetch_array. The resultset consists of rows of data, which these functions are used to extract.

The syntax for the mysql_fetch_assoc function is:

    array mysql_fetch_assoc ( resource $result )

where $result is the resultset returned by the mysql_query function. The mysql_fetch_assoc function returns an associative array that is a row in the resultset, and moves the internal pointer one step ahead. There are usually more than one row in a resultset. So the mysql_fetch_assoc function is normally placed inside a while loop, as in the following example:

    while ($row = mysql_fetch_assoc($result))
        {
            echo $row["userid"];
            echo $row["firstname"];
            echo $row["lastname"];
        }

The while loop will send out (echo) elements of the first row, then elements of the second, then the third, and so on. In the while loop, userid, firstname and lastname are names of columns in the database. So, if there are 5 rows in the resultset, there will be 5 values for userid, 5 values for firstname and 5 values for lastname. This is the situation where the resultset consists of 5 rows and 3 columns. In the while condition, $row is a variable whose name you choose; it holds the row fetched by the mysql_fetch_assoc function.

It is not all the time that you would want to get a resultset cell datum (singular of data) using a column name. In that case, you would use the mysql_fetch_array function to obtain the rows from the resultset. In simple terms, the syntax is:

    array mysql_fetch_array (resource $result, MYSQL_NUM)

The first argument is the resultset (resource) returned by the mysql_query function, and the second argument is some constant, which in this case is, MYSQL_NUM. The return value is an integer index based array that is a row in the resultset. The resultset usually has many rows. So the mysql_fetch_array function is normally placed inside a while loop, as in the following example:

    while ($row = mysql_fetch_array ($result, MYSQL_NUM))
        {
            echo $row[0];
            echo $row[1];
            echo $row[2];
        }

The first index in the row (array) is 0, second is1, third is 2, etc.

Email

You can have a script send an email to somebody. PHP has a function called the mail function. The script uses this function to send email. In simple terms the syntax is:

    bool mail ( string $to , string $subject , string $message [, string $additional_headers] )

If the email will be sent, the function will return TRUE. If the email will not be sent, the function will return FALSE.

The first argument is the recipient’s email address, e.g. "greatman@kingdom.com" . The second argument is the subject of the email, e.g. "Application for Employment". The third argument is the message body in quotes. You actually form the message as follows:

            $message = wordwrap($msg, 70);

where $msg has the actual text of the message typed. The wordwrap function and the number 70, breaks down the entire text into lines of not more than 70 characters long. An example of the fourth optional argument is:

    $additional_headers = "From: info@kingdom.comrnBcc: manager@kingdom.comrnCc: info@kingdom.comrnDate: ".date("D, j M Y H:i:s")." -0000";

This string has the From email, the Bcc email, the Cc email and the date the email is sent. The following code shows a typical use of the mail function, after its argument variables have been formed:

            if (mail($to, $subject, $message, $additional_headers))
                {
                    header('Location: sent.htm');
                }
            else
                {
                    header('Location: error.htm');
                }

Here, sent.htm, is an HTML file in the same directory as the PHP script. This HTML file has feedback information for the client like, “Thanks. The email has been sent.” if the email has been accepted for delivery. If the email is not accepted for delivery, then the client has to be told so. This would happen when there is an error. The “error.htm” file is another file in the same directory as the script. It has a simple error message such as, “Error: The email could not be sent. Try again later.” So, if the email is accepted for delivery, the sent.htm file is sent to the client; if the email is not accepted for delivery, then the error.htm file is sent to the client.

Well, at this point, you have the basic knowledge to produce a PHP script that will interface the Internet MySQL database server in web development. Let us stop here and continue in the next part of the series.

Chrys

Broad Network