Ninedays Blog

As every PHP developer does, I have a library of code I am always picking and choosing from when I write anything. I'm going to let you in to see one of my favorite sets of functions I use frequently to connect and interact with a MySQL database.

Connect to MySQL

The first thing I do is set the variables for my MySQL connection:

PHP

<?php
    # config.php file

    $MYSQL_USER = 'db1234';
    $MYSQL_PASS = 'uBerS3cRe7';
    $MYSQL_DATABASE = 'db1234_dev';
    $MYSQL_HOST = 'localhost';

    // Connect to the DB
    $sql = mysql_connect($MYSQL_HOST, $MYSQL_USER, $MYSQL_PASS);
    mysql_select_db($MYSQL_DATABASE, $sql);
    if (!$sql) {
       die('Could not connect: '. mysql_errno() . mysql_error());
    }

?>

Obviously, replace the MySQL user, password, database name and host (99% likely to be localhost) with the correct information to access the database you have set up on your server.

What this file does, is establish the connection to MySQL and supplies the resource all of our future MySQL/PHP functions will need to access the database.

The most secure thing you can do is to put this connection information outside of the realm accessible by the web, say a level lower than your htdocs or html folder. That's entirely up to you. As long as you include it properly put it wherever. If you don't know what I'm yammering on about put it in the same directory as the next file we're going to set up.

Now I'm going to create a class to get my queries processed.

MySQL Quick Query Class

I'm not really going to brief you on OOP PHP (creating classes) but this is a super simple example to learn from.

We'll set up out class Query and give it two variables, $action and $connect which will be defined in the Query contructor function.

<?php
    # functions-db.php file

    # Query Class

    Class Query {
        var $action;
        var $connect;

        function Query($query,$sql) {
            $this->action = mysql_query($query,$sql);
            $this->connect = $sql;
        }

        /* More functions will go here */
    }
?>

When we create a new Query object and pass it both a mysql query to process and the MySQL resource ($sql as defined in the config.php file.)

For example, this quick PHP script will insert a new value into a database table.

PHP

<?php
    # sample.php file

    include_once('includes/config.php');
    include_once('includes/functions-db.php');

    $insertQuery = 'INSERT INTO `sample_table`(`id`, `name`, `date`) VALUES(NULL, 'sample insert', NOW());';

    $result = new Query($insertQuery,$sql);
?>

Now let's add more common functions to our class so we can retrieve more information about the query on the next page!