[Tutorial] Connecting PHP with MySQL

Discussion in 'Web Design & Programming' started by RHochstenbach, Mar 3, 2011.

  1. RHochstenbach

    RHochstenbach Administrator

    Likes Received:
    26
    Trophy Points:
    48
    One of the many features of PHP is the ability to connect to a MySQL database. From there, it can execute SQL queries. Every wondered how to set this up? I'll teach you in this tutorial.

    In this example I have a Web Server running Linux. My website is stored in this folder:
    Code:
    /var/www
    To perform SQL queries, you have to set the connection details like server address, username, password and database name. We do this with the following code:

    PHP:
    // Set these 4 options
    $host="your_database_server"// Location of the Database server. Usually localhost
    $username="username"// Username of the database
    $password="password"// password of the database
    $db_name="database_name"// Name of the database

    mysql_connect($host$username$password); // Connect to the database
    mysql_select_db("$db_name"); // Select the database
    This piece of code should be put above all other PHP code in each file that needs database access.

    But as you might have noticed, your credentials (including password) are stored in plain text. This is a security risk. Therefore you should follow these steps:

    1. Create a folder inside your web directory. In this case I'll create the folder secure.

    2. Edit your httpd.conf or apache.conf file using a text editor (need to be root for this).

    You can find any of these files using these 2 commands:
    Code:
    updatedb
    whereis httpd.conf
    
    When you found it, open it in a text editor. This can be nano if you don't have a desktop, gedit on Gnome, kate on KDE or which text editor you might have.

    3. Add the following code to the file (in this example my secure folder is located in /var/www where my website is located):
    Code:
    <Directory "var/www/secure">
    order allow,deny
    deny from all
    </Directory>
    
    4. Save the file and then restart Apache or reboot.

    Notice: If you don't have access to your server's Apache configuration, you could also use .htaccess to do the trick of securing a directory using this guide: http://webdesign.about.com/od/htaccess/ht/hthtaccess.htm

    5. In this example I save the PHP script I've written to connect to the database. I'll use the name database.php and save it inside the 'secure' folder.

    6. Try to run the PHP file from your browser. You'll see an Access Denied message. Basically only PHP can access the folder, because it runs locally on the server. But no one can access it from the outside.

    7. If you create a PHP script that needs to access your database, just use the 'include' or 'require' function of PHP. In this example I have to begin such a PHP script with this line:
    PHP:
    <?php include("secure/database.php"); ?>

    All other code here
    This will first run the database.php file from the server. Because PHP is a server-side scripting language, it can safely access the file from the server itself.

    Basically the difference between include and require is that if the file you're pointing to is missing or inaccessible, it keeps executing the rest of the file if it's called with include. But if it's called with require, it stops executing and gives a fatal error. If your script only works with the contents of a called file, then I'd suggest to use require instead of include

    Setting the database options for our lessons
    If you read trough the tutorial "Preparing MySQL for the PHP tutorials", you've set up a database. With those instructions, fill in the database settings code with the following data:

    PHP:
    $host="localhost"
    $username="student";
    $password="learning";
    $db_name="tutorial";

    mysql_connect($host$username$password);
    mysql_select_db("$addresses");
    There you go, you can now use your database with these tutorials.
     
    Sniper likes this.

Share This Page