Using a MySQL database with PHP

It’s time to add some dynamic content to your website. The best choice for ease-of-use, price and support is the combination of PHP and MySQL. This article introduces the beginner to using MySQL with PHP.

 

Download the files used in this tutorial here

A beginners guide to using MySQL with PHP

This article assumes a few things.

  • You know how to use PHP. If you don’t, the best place to start is by reading Welcome to PHP
  • You know how to run basic queries in a database. If you don’t, I suggest you visit Getting started with SQL or parts 1 and 2 of Introduction to Databases for the Web
  • You have access to a server (or servers) running PHP and MySQL. If yuo don’t, you can get hold of the software, for free, along with installation instructions at the PHP site and the MySQL site.
  • The server running PHP can connect to the server running MySQL. Test this from the command line first. If you can’t connect without PHP, you’re not going to be able to connect with PHP. If you’re using other servers, you may need to ask the systems administrator for help
  • There’s an existing database and table already running on MySQL, which we’ll use with the PHP scripts. The database is called first_test, and has a table called people with some data in it. Run the following SQL to create and populate the table:
    mysql> CREATE DATABASE first_test;
    Query OK, 1 row affected (0.31 sec)
    mysql> USE first_test;
    Database changed
    mysql> CREATE TABLE people (
              id int UNIQUE NOT NULL,
    	  first_name varchar(40),
    	  surname varchar(50),
    	  PRIMARY KEY(id)
           );
           Query OK, 0 rows affected (0.24 sec)
    mysql> INSERT INTO people VALUES(1,'Ann','Brache');
    Query OK, 1 row affected (0.09 sec)
    mysql> INSERT INTO people VALUES(2,'Narcizo','Levy');
    Query OK, 1 row affected (0.02 sec)
    mysql> INSERT INTO people VALUES(3,'Tony','Crocker');
    Query OK, 1 row affected (0.00 sec)
    

    Your table should now look as follows:

    mysql> SELECT * FROM people;
    +----+------------+---------+
    | id | first_name | surname |
    +----+------------+---------+
    |  1 | Ann        | Brache  |
    |  2 | Narcizo    | Levy    |
    |  3 | Tony       | Crocker |
    +----+------------+---------+
    3 rows in set (0.19 sec)
    

Connecting

There are many MySQL functions in PHP. Perhaps you’ve taken a look at the official documentation and been intimidated by what looks like an endless task. But the good news is that to perform basic queries from within MySQL is very easy. This article will show you how to get up and running. Once you’re comfortable with the basics, you can start to investigate the other functions, and you’ll see that many of them are just duplicate ways of doing the same thing. Let’s get started. The first thing to do is connect to the database.
All mysql functions begin with mysql_, so it comes as no surprise that the function to connect to MySQL is called mysql_connect. Let’s assume you would connect to MySQL from the server you’re running PHP with the following details:

  • Username pee_wee
  • Password let_me_in

From the command line, you’d type the following:
mysql -upee_wee -p
You’d enter the password once the prompt appears (you can also enter it directly on the command line, but get into the habit of doing it this way – it’s more secure!). PHP can connect in the same way. The mysql_connect() function looks as follows:

resource mysql_connect ( [string server [, string username [, string password [, bool new_link [, int client_flags]]]]])

This function returns a resource which is a pointer to the database connection. It’s also called a link identifier, or a database handle, and we’ll use it in later functions.
Let’s replace your connection details, and run this in a script:

<?php
$username = "pee_wee";
$password = "let_me_in";
$hostname = "localhost";	
$dbh = mysql_connect($hostname, $username, $password) 
	or die("Unable to connect to MySQL");
print "Connected to MySQL<br>";
// you're going to do lots more here soon
mysql_close($dbh);
?>

All going well, you should see “Connected to MySQL” when you run this script. If you can’t connect to the server, make sure your password, username and hostname are correct, and that you’ve copied the script exactly.

The last line of the script contains another MySQL function – mysql_close(). Although this isn’t strictly speaking necessary, PHP will automatically close the connection when the script ends, you should get into the habit of closing what you open. If you start developing more serious applications, or move to other, less tolerant languages, you will find the transition more difficult if you haven’t learnt the basics well from the beginning.

Once you’ve connected, you’re going to want to select a database to work with. Let’s assume the database is called first_test. To start working in this database (the equivalent of typing USE first_test in MySQL), you’ll need the mysql_select_db() function.

bool mysql_select_db ( string database_name [, resource link_identifier])

To change to the first_test database, add the mysql_select_db() function call to your script, as follows:

<?php
$username = "pee_wee";
$password = "let_me_in";
$hostname = "localhost";	
$dbh = mysql_connect($hostname, $username, $password) 
	or die("Unable to connect to MySQL");
print "Connected to MySQL<br>";
$selected = mysql_select_db("first_test",$dbh) 
	or die("Could not select first_test");
// you're going to do lots more here soon
mysql_close($dbh);
?>
Ian Gilfillan
Ian Gilfillan
Ian Gilfillan lives in Cape Town, South Africa. He is the author of the book 'Mastering MySQL 4', published by Sybex, and has been working with MySQL since 1997. These days he develops mainly in PHP and MySQL, although confesses to starting out with BASIC and COBOL, way back when, and still has a soft spot for Perl. He developed South Africa's first online grocery store, and has developed and taught internet development and other technical courses for various institutions. He has majors in Programming and Information Systems, as well as English and Philosophy. For 5 years he was Lead Developer and IT Manager for Independent Online, South Africa's premier news portal. However, he has now 'retired' from fulltime work, and is hoping that his next book will be more in the style of William Blake and Allen Ginsberg.

Get the Free Newsletter!

Subscribe to Cloud Insider for top news, trends & analysis

Latest Articles