MySQL Queries Made Easy With PHP Functions Library

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!

Pages: 1 2 3

Tagged as: class, database, function, mysql, oop, PHP

This site runs on the Thesis WordPress Theme

If you're someone who doesn't understand a lot of PHP, HTML, or CSS, Thesis will give you a ton of functionality without having to alter any code. For the advanced, Thesis has incredible customization possibilities via extensive hooks and filters. And with so many design options, you can use the template over and over and never have it look like the same site.

If you're more familiar with how websites work, you can use the fantastic Thesis User's Guide and world-class support forums to make more professional customizations than you ever thought possible. The theme is not only highly customizable, but it allows me to build sites with a much more targeted focus on monetization than ever before. You can find out more about Thesis below:

• Thesis Blog
• Plans & Pricing (with a 30-day money back guarantee!)