MySQL helpers in PHP

Now, as most everyone that programs is accustomed to, it’s the high use of Object Oriented Programming (OOP).  Now, with that, the Liquid Web Framework (though, I am wondering if I should call it Platform? Maybe?  A developers code is separate from the core of the project, and it provides all the useful classes to work with it, so not sure what its technical name should be.)  Anyways, the LW Project is completely OOP is both its design and its implementation, so most code samples and projects that will be presented here will have a containing class -even if a class exists in a PHP module that most installs have.  The reason for this is to try and allow a module to be removed and replaced by another one that serves the same person, but fulfills it in a different manner.  I mention this because that is why these MySQL helper functions came in to play.  I wanted everything I could to pass raw data into the class, and have it form the queries needed to complete.  However, with select statements, that become more difficult, but I did still create a few helper functions that will work nicely in the install script once it’s built.

Now, as I go through these helper functions, keep in mind that like with all good “pre-built” systems that use a database, you want to give those using it the ability to have multiple installs in the same database rather than forcing them to be able to have a new database for each install.  So, the first thing declared with these functions is: define('LW_TP', 'LW_'); That statement will set a constant LW_TP to be LW_, LW_TP stands for “Liquid Web Table Prefix’, but since it will be used often in a database intensive application, I used the acronym. The ‘LW_’ is what the users of this system will change for their specific projects (if they so choose to change it).

The first method that we create will be to sanitize string input:

Notice the call to $this->connection , that is the stored object resulting from creating a new mysqli object.  The method real_escape_string requires a connection to be made, and can escape the string based on the database charset.  That’s it.  That is the entire method for sanitizing user input to prevent SQL Injections. (Though, it should be noted that it must be called individually on each input into the database – but we’ll get to that.)

Once you are connected to a database, the first thing you need to do is add tables – whether you do that manually, or in an install script really makes no difference; though using an install script allows your entire base application to be portable.  This next method will create a SQL statement based on input, and then execute it.

The tableName parameter is pretty obvious, coupled with our earlier define, we get the new table name stored in $tbName that will be used in the SQL statement.  Now, the fun part has to do with $fields , as that is an associative array of the fields that will be in the table.  An example input would be:

See how it is set up?  The name of the table is the key of the array, while the parameters are the value of said array.  On lines 4/5 of the method, we loop through the array, adding to the array we created on line 3.  From there, we make use of the implode function to separate our new array out by comma’s.  After that, we put it all into a SQL statement and then execute it via $this->query() method.  That function can and should be changed for your own integration, but it is a simple method that can be used in a script to automatically add the table prefix and piece it together.

This next function is a little more useful, as it does a couple of things other than making sure the table name has the prefix.  It is for inserting information into your database:

Inputs are pretty much the same as the createTable method, however, the associative array is formed slightly differently, with the key being the name of the field you want to insert in to; the value is…the value you want to insert.  Please note – for auto-incrementing fields, you don’t want to have a key set for that field, ignore it completely in the insert so it can do its thing. Example of the fields variable with a static input, you would want to use the user-input information instead:

Now, this method will create two arrays, one for the names of the fields, and one for the values, on lines 3/4.  On lines 5-8, we loop through the $fields setting our two new arrays appropriately. Notice on line 7, we use the sanitize method that we created previously to escape the user-input and prevent SQL injections.  On lines 9/10, we implode the arrays we created with a comma so that they are in appropriate form.  After that, it’s just putting the pieces together and executing them (Lines 11/12 in the method).

That’s it! The last method I gave is the actual useful one outside of needing to prefix the table name, so have fun.

No Comments Yet

There are no comments yet. You could be the first!

Leave a Comment

    Search the Blog