PHP and MySQL were made for each other!

Hi Everyone!  I wanted to post a very basic tutorial series on using PHP and MySQL.  This tutorial is Part 1 and it will not focus on anything complicated, rather it will break down the process of connecting to your MySQL database.  This tutorial assumes that you know how to create a MySQL database and it also assumes that you have web hosting with PHP and MySQL installed.  For MySQL database management, I personally prefer using phpMyAdmin.  Some people prefer other MySQL clients, while some hardcores prefer doing all of their database management from within their PHP script.  I’ll leave the decision as to which method you’ll use up to you.

We will be using the following database throughout this entire tutorial series, so don’t delete it after you complete this tutorial! You’ll need it for Part 2 and so on!

Now, the first thing we need to do is create a database.  So, using whatever method you prefer, create a database called “test_data” (without the quotes) and give it one table called “test_table” with 3 fields.  The first field should be labeled “id”, and it should be set to INT, Auto-Increment, and set it as the primary key.  The second field should be labeled “name”, and it should be set to VARCHAR with a length of 32.  The third field should be labeled “city”, and it should be set to VARCHAR with a length of 64.

Okay, so now we have our database and table all set up!  So, the next thing we need to do is create our PHP script!  Open a good text editor such as Notepad++, Dreamweaver, etc.  The first thing we need to do is create a PHP code block.  Here’s how it should look:

<?php

?>

Next, we need to establish a connection to the database. We do this using the mysql_connect function that is built in to PHP. You will need to know the hostname of your MySQL server, as well as the username and password for the database. Typically, the hostname is “localhost”.

<?php
//connect to mysql database server
mysql_connect("hostname","username","password");
?>

Replace hostname with your actual hostname, username with your actual username, and password with your actual password. This code attempts to connect to your database with the credentials that you provided. The problem is, there is nothing in place to inform you if your script was successful or not! So, we will need to add some error handling.

<?php
//connect to mysql database server
mysql_connect("hostname","username","password") or die (mysql_error());
?>

Now, if you get an error, then there is something wrong with your connection script. If nothing happens at all, that means the connection is successful!

Next, we will want to make this script a little cleaner and add some code that will tell you if the connection to the database was successful or not. So we will want to create three variables at the top of our script. db_host, db_user, and db_pass. We will also create an echo for a successful database connection.

<?php
$db_host = ""; //place your hostname in this variable
$db_user = ""; //place your username in this variable
$db_pass = ""; //place password in this variable

//connect to mysql database server
mysql_connect($db_host, $db_user, $db_pass) or die (mysql_error());
echo "Successfully connected to MySQL Database!";
?>

Now that our script is connecting to our MySQL server, we just need to tell it which database we want to use. Do this using the mysql_select_db function in PHP. So, we will add one more variable at the top of our script called db_name. We will add the mysql_select_db function at the bottom of the script with an echo for a successful selection. You will notice that the success message is dynamic which will allow you to use it on other scripts without changing the code.

<?php
$db_host = ""; //place your hostname in this variable
$db_user = ""; //place your username in this variable
$db_pass = ""; //place password in this variable
$db_name = ""; //place your database name in this variable

//connect to mysql database server
mysql_connect($db_host, $db_user, $db_pass) or die (mysql_error());
echo "Successfully connected to MySQL Database!";

//Select Database
mysql_select_db($db_name) or die (mysql_error());
echo '<h2>Successfully selected ' . $db_name . ' database!</h2>';
?>

And there you have it! A perfectly respectable MySql connection script! The next tutorial will focus on the basic while loop for pulling data from the database!