PHP and MySQL Introduction

PHP and MySQL Introduction

This tutorial covers the basics of accessing a MySQL database from your PHP script. You will learn how to connect to a MySQL database, and how to execute a SQL statement.

MySQL is a popular relational database management system that is commonly used in PHP-based web applications. The free version of MySQL is called "MySQL Community Server" and can be downloaded here: MySQL Downloads. Installation instructions are available here: Installing MySQL Community Server. For the purposes of this tutorial, we will access a database named "helloworld". This database will have a table called Widgets with the following structure.
Column Name Data Type Modifiers
id INTEGER UNSIGNED NOT NULL AUTO_INCREMENT
name VARCHAR(32) NOT NULL
color VARCHAR(16) NOT NULL

The PHP language has built-in APIs for accessing MySQL servers. To access a database in MySQL from a PHP script, the first step is to connect to the MySQL server, using the mysql_connect() function. This function will return a connection identifier on success, and FALSE on failure. In the case of failure, the script can call mysql_error() to get an error message describing the failure.

// Replace these strings with the actual hostname:port,
// username, and password
$conn = mysql_connect("localhost:3306",
"username", "password");
if($conn === FALSE) {
die("Error connecting to database: "
. htmlspecialchars( mysql_error() ) );
}

The next step is to select which database the script will be accessing via the mysql_select_db() function. This function takes the name of the desired database, and returns TRUE on success and FALSE on failure.








// For this example we will connect to
// the 'helloworld' database
if(mysql_select_db('helloworld') === FALSE) {
die("Error selecting database: "
. htmlspecialchars( mysql_error() ) );
}

Now we are at the point where we can begin interacting with the database tables. The following code will select all records from our Widgets table, ordered by the name column. The mysql_query() function executes the SQL statement. For select statements, this function returns a result table on success, and FALSE on failure.

$statement = "select id, name, color from Widgets "
. "order by name";
$result = mysql_query($statement);
if($result === FALSE) {
die("Error executing statement: "
. htmlspecialchars( mysql_error() ) );
}

The following code will output an HTML table containing the contents of the Widgets table by iterating through the result table that was returned from mysql_query(). The mysql_fetch_assoc() function will be used to retrieve an associative array containing the next row of data from the result table. This function will return FALSE when all the rows have been retrieved.

// Iterate through the result table
echo('

Widgets

');
echo('');
echo('');
while($resultRow = mysql_fetch_assoc($result)) {

// The keys of the associative array
// are the column names
$id = $resultRow['id'];
$name = $resultRow['name'];
$color = $resultRow['color'];

echo('');

}
echo('

ID	Name	Color
' . htmlspecialchars($id) . '	' . htmlspecialchars($name) . '	' . htmlspecialchars($color) . '

');

We'll finish up this example by freeing the result table and closing the connection.

mysql_free_result($result);
mysql_close($conn);

This introduction should give you enough information to get started with using MySQL from PHP.