Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

PHP

Posted Sep 24, 2002

Using a MySQL database with PHP

By Ian Gilfillan

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


PHP Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 



















Thanks for your registration, follow us on our social networks to keep up-to-date