HowTo – Connecting To A MySQL Database With PHP

Filed under: Site Management,Web Design

This Howto will cover the basics of connecting to a MySql Database with PHP.

Before you can connect to a database with PHP you must setup your database giving it a new name, user with password and you must apply rights to the new user to allow them to edit the database.

If you are using a web host that has cPanel then setting up a new database and user is pretty easy. You will need to follow through the steps as asked and remember to save the password for the user you make.

If you are working on your home computer there are a number of ways to add a database and user then set permissions but I strongly suggest that you make use of a gui application instead of using command line tools.

There are a couple good MySql Tools that you can use I use MySql Administrator for Windows. It is a little clunky at first but once you understand all of its options it is a tool that you won’t want to be without. You can get this free tool or other free tools at the official MySQL website.

Along with Gui Tools you may want to make use of PHPMyAdmin it is a mysql database manager that runs in your browser. If you install XAMP package of servers on windows or another platform phpmyadmin is part of the package. I do not suggest downloading it separately and configuring phpmyadmin on your own if it is not in your package because it can be confusing for first time webmasters.

Example Database

Setup A Database and User with the following:

USER: testuser

PASSWORD: 1234abcd

DatabaseName: mytestdb

Select the User testuser and give them full edit rights to the database mytestdb that you just created.


Connecting and Making A Table

The following code will use your database setup information to make a connection to the database and create a table with columns.



$database = "mytestdb";
$USER = "testuser";
$PASS = "1234abcd";

//Table Name: contacts
//Cols: first, last, phone, email, website

mysql_connect(localhost,$USER,$PASS); // connect to db server

@mysql_select_db($database) or die( "Unable to select database"); //select our db

$query="CREATE TABLE contacts (id int(6) NOT NULL auto_increment,first varchar(15) NOT NULL,last varchar(15) NOT NULL,phone varchar(20) NOT NULL,email varchar(30) NOT NULL,website varchar(30) NOT NULL,PRIMARY KEY (id),UNIQUE id (id),KEY id_2 (id))";
mysql_query($query); // run the query defined above
mysql_close(); // close the connection


If you have completed all the steps you should be able to use MySql Administrator or another tool to take a look at your new table and the columns that have been created.

As you will notice the steps in the connection were

Connect to the Server
Select the Database
Define our Query
Run our Query
Disconnect from the Server

Final Note

So, now you know how to make a connection to your MySql Database and define and run a simple query with PHP.

Don’t worry from here its all up hill…. heh