Back to Basics: Connecting to a MySQL Database using PHP
If you’re building a website using PHP then at some you’ll no doubt want to read and write data to a MySQL database. In order to do this we first need to establish a connection to the database. Presuming the database has already been setup we can do this by placing just a few lines of code before any queries take place, normally at the top of the page:
$db_host = "localhost"; $db_user = "mysql_user"; $db_pass = "mysql_pass"; $db_name = "my_database"; mysql_connect($db_host, $db_user, $db_pass); mysql_select_db($db_name);
The Code Explained
Lets break down the above code and look at what each line means:
Line 1: Here we set the MySQL server. If the script making the connection is on the same server as MySQL then this will more than likely be “localhost”, however if hosted elsewhere this will be the hostname of the external server.
Line 2: MySQL users are setup in order to specify different privileges to different databases and tables. I won’t go into too much detail on users in this post but to connect you will need a username and password. Default MySQL installations already have a user setup with the username ‘root’ and no password, however I recommend that you contact your web hoster if you are unsure on these details.
Line 3: As above, like the username, you will need the password of a valid MySQL user in order to successfully connect.
Line 4: This is the name of the database that you wish to perform actions on. If your script has trouble connecting to the database I would firstly ensure that the database is setup and spelt correctly (note that the name is case-sensitive) and that the MySQL user you are connecting as (Lines 1 and 2) has permission to access the database in question.
Line 6: Now that we have our variables setup we are ready to connect to the MySQL server. The php function mysql_connect() allows us to do this.
Line 7: If your script has got this far without showing an error you’re doing well. We’re connected and all that’s left is to select the relevant database. As with connecting we can do this by using the built in PHP function mysql_select_db().
Well done! You’re connected to MySQL enabling you to run queries on the database you specified.
The Icing on the Cake
What we’ve done above allows us to connect and select a database in a very primitive form. That’s great, but what if you’re getting an error, nothings happening at all or we want to make our script a bit more future-proof were something to go wrong in the future. The snippet below is similar to above but adds error checking meaning if something is wrong with your setup you’ll get a much better idea of what and where it’s going wrong:
$db_host = "localhost"; $db_user = "mysql_user"; $db_pass = "mysql_pass"; $db_name = "my_database"; mysql_connect($db_host, $db_user, $db_pass) or die('Could not connect: ' . mysql_error()); mysql_select_db($db_name) or die('Could not select database: ' . mysql_error());
Note the additional die() commands? By adding these along with the mysql_error() function you’ll get an explanation of exactly what’s wrong should an error occur.