PHP Tutorial: Connecting to a MySQLi Database

Leave a comment Standard

So, here’s the low down on how to setup a connection to a MySQL database using MySQLi and PHP. This will also make sure your login information is protected. First, you’ll need a database, PHP, and a place to host your files. Once you have those you’re ready to go. Don’t have a hosting provider? No problem, check out XAMPP.

1) You want your login for your database to be protected. That means you want the file to reside in a non-public part of your website. For instance, in most hosting places when you go to view all of your files you know to put them in the public_html folder or the www folder or the index folder. Your database login needs to be in the directory ABOVE your public folder. For most people this is called home, the name of your website, or even the root directory.

2) Create a file called dbh.php (or any other name that suits your fancy) in your home/root directory. That means if people navigate your website they’ll never be able to accidentally access this information because your home/root directory has different chmod permissions (711) then your public_html directory (750).

3) Now you need to write the code to make the actual connection to the database in dbh.php.

<?php
/**
* replace things with { } curly brackets with the appropriate information
* including the curly brackets! don't leave those in there...
* You'll use your database handler ($dbh) when you run your queries
**/

//connect to the server
$dbh = mysqli_connect("localhost", "{username}", "{password}", "{database}")
     or die ('cannot connect to database because ' . mysqli_connect_error());
?>

4) That’s it for the code to connect! Short and sweet. Now the question is how to get it in all your files.

5) Create a file in your public_html folder called index.php. You want this file to be publicly accessible to anyone. Put this inside of it:

<?php
/**
* include your database connection from a protected directory
* by include it from the directory above (../) where this file is
**/
include('../dbh.php');

//do all the rest of your usual php coding here
echo "connection successful!";

?>

If you don’t get an error message when you try to view this php script then you know your include was successful. Now there’s no chance someone can get the login and password for your database and you can access it from your php files like you normally would AND you don’t have to rewrite the connection statement at the top of EVERY php page you make.

Nifty huh?

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s