Connecting to mySQL Database with PHP

Programmers

While PHP can do many things, connecting to a mySQL Database is one of the most powerful and useful.

*UPDATE – If you’re learning how to connect to a mySQL Database, the new norm is mysqli_connect(). The functions below have been depreciated and so removed.*

mySQL Databases – a quick setup into

There are several types of databases out there, but mySQL is free, easy to use, and easy to setup. On your hosting account (you can do this on your computer – localhost – as well, but I won’t be giving directions on how to do that here), navigate to Databases.

If you’re using Godaddy, Launch the Hosting Account, then use the tab at the top for Databases. If you can’t find the database link, call your company and they should walk you through it. (NOTE: if you can’t get someone on the phone from the hosting company you’re using, switch. All the good companies provide phone and/or chat with representatives with almost no wait time to help you with almost any problem.)

Walk through your hosting account’s Setup Process for a database. Don’t forget to write down passwords and usernames!

It takes somewhere around 30 minutes to usually get a database set up. So, go get some coffee (and a cheese danish cause they’re awesome).

OK, all set up? Now navigate to the Database (should be a link right there where you set up the databases) and enter your username and password. Welcome to mySQL!

Now that it’s all setup, let’s test a connection with it using your PHP page. Granted, you haven’t put anything in your database yet, but that will be another post.

Connection

It is actually quite easy to set up a connection with your database. Copy and Paste this code into your PHP page.

—————–

Replace the “connection IP” with the IP address of your database. To find this, look at the top of your mySQL Database, and find the line that looks like: User: username@12.123.123.12. Use the IP address that comes after the “@” sign. Then replace username and password as well.

The second line is selecting a database. This is where all your tables will go. Basically, it’s the actual name of the database.

On the end of both lines, I added a “die” option. So if something doesn’t work when you connect, you’ll at least know and have a PHP alert. I always include these, but you don’t have to. You can remove the “or die(mysql_error())” section and move the semicolon to right after the ending parenthesis.

Organizing

As in all things, organization is vital. Personally, I include one PHP file with this information(only the lines shown above) that I then include in my pages. That way, if/when I change the password, I only have to update one file.

To do this, open a new page, and give it the ending “.php”. Then copy only the coding above, erase everything else in the file, and paste that code ONLY into that PHP file. Save.

Now go back to your original PHP page that you will be actually adding the page information to (such as “home.php”, “contact.php”…your actual website pages). Add this coding to the top:

————

Change the “connectionName.php” to your file name, and upload both pages to your hosting account. Now you’ll only need to update one file when you change the password.

Security

And of course, we need to talk about security. Security within your mySQL database (passwords, etc.) is a huge topic. Here, I’m only going to talk about security when connecting to your database.

First, I would suggest you get a really great password. Make it long, make it random, and include uppercase, lowercase, and symbols. This is not a password you’ll need to remember off the top of your head so don’t worry about making it memorable – but do write it down somewhere!

PHP is a server-side language, so a user would never be able to see the coding we just put in. It “disappears” in the actual source code when viewing it online.

The only way someone could view this file is if they hacked into your hosting account and downloaded the actual source file.

To make it more secure, you can take the “connection.php” file and put it into a folder that is not a publicly readable area of your hosting account. Every hosting account has private and public files. The private files are not able to be reached by users on the web, but can still be accessed by the public files. To make this happen, you’ll probably need to have the hosting account you’re using walk you through the steps.

That being said, I usually just use a good password and name the file something unusual (not “connection.php”). I’m not a security expert, but for most websites out there, this security level will be plenty to dissuade any bored hackers from gaining access. If your database will be including any financial data, though, you’ll definitely have to look into at least a Security Certificate and additional security measures.

Comments