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

<?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);
?>