Connect Database From PHP to MySQL

php5-mysql

In order to connect to the MySQL database from the PHP language, we must have php5-mysql package installed. This is a package name for Debian/Ubuntu Linux. On other derivatives the name might differ. This package has three modules. They are also called extensions.

• mysql module
• mysqli module
• pdo_mysql

The generic mysql module is the original PHP API for the MySQL database. Our tutorial covers this module. The API is procedural. This module does not provide all the latest features of the newer MySQL databases. The MySQL improved mysqli module is the recommended module for MySQL versions 4.1.3 or later. It provides both object oriented and procedural APIs. It has several benefits and enhancements over the original mysql module.
The pdo_mysql, PHP Data Objects module is a database abstraction layer for PHP applications. This module is beneficial if we write portable database PHP scripts.

First script

The following script is a simple PHP script. If this small script runs OK, we have everything needed installed.

<?php

$host = "localhost"; 
$user = "user12"; 
$pass = "34klq*"; 

$r = mysql_connect($host, $user, $pass);

if (!$r) {
    echo "Could not connect to server\n";
    trigger_error(mysql_error(), E_USER_ERROR);
} else {
    echo "Connection established\n"; 
}

echo mysql_get_server_info() . "\n"; 

mysql_close();

?>

We connect to the database and get some info about the MySQL server.

$host = "localhost"; 
$user = "user12"; 
$pass = "34klq*"; 

These are three variables holding the host name, user name and password. The variables are needed when connecting to the MySQL database.

$r = mysql_connect($host, $user, $pass);

We use the mysql_connect() function to connect to the database. The function returns a boolean value indicating whether the connection was successfully created or not. The function has 3 parameters. The first is the host, where the server is installed. The second and third parameters are the user name and user password.

if (!$r) {
    echo "Could not connect to server\n";
    trigger_error(mysql_error(), E_USER_ERROR);
} else {
    echo "Connection established\n"; 
}

Now we check the $r variable. If it contains a boolean false, the connection to the database was not created. We call the trigger_error()function to generate an error message. The first generic message goes to the user. The more specific error message generated with the trigger_error() function is logged.

echo mysql_get_server_info() . "\n"; 

The mysql_get_server_info() returns the MySQL server version.

mysql_close();

The mysql_close() function closes the connection to the database. Closing connection in our case is not necessary, as non-persistent open links are automatically closed at the end of the script's execution. However, it is a good programming practice.

$ php version.php
5.1.41-3ubuntu12.6
5.3.2-1ubuntu4.5

On my system, I got the following output.

---

We have a similar script.

<?php

$host = "localhost"; 
$user = "user12"; 
$pass = "34klq*"; 

$r = mysql_connect($host, $user, $pass);

if (!$r) {
    echo "Could not connect to server\n";
    trigger_error(mysql_error(), E_USER_ERROR);
} else {
    echo "Connection established\n"; 
}

$query = "SELECT VERSION()";

$rs = mysql_query($query);

if (!$rs) {
    echo "Could not execute query: $query\n";
    trigger_error(mysql_error(), E_USER_ERROR);
} else {
    echo "Query: $query executed\n"; 
}

$row = mysql_fetch_row($rs);

echo "Version: $row[0]\n";

mysql_close();

?>

We check for the version of the MySQL database. This time using an SQL query.

$query = "SELECT VERSION()";

This is the SQL SELECT statement. It returns the version of the database. The VERSION() is a built-in MySQL function.

$rs = mysql_query($query);

The mysql_query() function executes an SQL query on the database. This is a SELECT query, so the result is a result set, containing some data.

if (!$rs) {
    echo "Could not execute query: $query\n";
    trigger_error(mysql_error(), E_USER_ERROR);
} else {
    echo "Query: $query executed\n"; 
}

In case of an error we generate an error message. Otherwise we print the SQL query executed.

$row = mysql_fetch_row($rs);

We fetch a row from the result set. The $row variable is an array containing data.

echo "Version: $row[0]\n";

We print the data from the array. We know from the nature of our query, that we have only one item it the array, the MySQL version string.

$ php version2.php
Connection established
Query: SELECT VERSION() executed
Version: 5.1.62-0ubuntu0.11.10.1

Output of the script on our system.