Aufait Official Blogs

MagicQuery is a PHP class that can automate the task of writing INSERT and UPDATE database queries. This is so simple that if you make an HTML form, MagicQuery can write and execute the query for you in just three lines of code no matter how long your form is. Such a class can play down both coding time and chance of errors in the query, because everything in here is pre-tested and automated.

Lets go with an example and see why MagicQuery is called so.

Take a simple form as shown above. Clicking on the ADD button submits the form to execute the INSERT query on a database table, say ‘address_table’. Now think if the same form has over 50 textboxes. You sit down to prepare the insert query and spend hours writing and testing it before everything is brought under control.

MagicQuery can bypass the task with one simple rule. Just that, the name of the form field should be kept same as the name of the corresponding field in the database table. Refering the above form, if the database field-name is ‘name’, the HTML form field name attribute should be name=”name”. The MagicQuery function fetchForm() can automatically fetch up values from $_REQUERT global array variable and prepare the query all by itself.

Lets see the three lines of code needed to handle the action and execute the query.

1) The first line is of course instantiating the MagicQuery class.

$query = new MagicQuery( ‘address_table’ ); Note that, database table name is passed when creating the object.

2) Fetching values from form is the next step.

$query->fetchForm(); However arguments like, $query->fetchForm( $_POST ) and $query->fetchForm( $_GET ) can also be passed, which tells the function to depend on the passed argument to make query.
3) The query is now ready and you just need to run it.

$query->run(); But before you go for the actual run, MagicQuery lets you to see your query (with some visual formattings). Just put the code $query->printQuery() instead of $query->run() and see how the query is printed on the top of the page.

Feel free to download the example below and try it out yourself. I’ve used this class to build a few sites and found it to be very effective and time saving. Hope you’ll feel the same.

Class Name : MagicQuery ( Download Example - MagicQuery.zip )

Dealing with File uploads using MagicQuery class

We already saw how MagicQuery can effectively help in automating queries. Taking it further, we’ll see how ‘$query->upload()’ function works for file uploads.

Suppose you have a file-field in your form to upload an image. Name it say, ‘file_my_image’ (I believe you remember to retain the golden rule, “name of the form field should be kept same as the name of the corresponding field in the database table”). Please also remember to put enctype=”multipart/form-data” in your form tag. Now lets see how the upload() function works.

$query->upload( file-field-name , destination-folder , file-name , allowed-file-types , max-file-size );

Lets take a closer look into the parameters.

file-field-name : This is the only mandatory parameter. That means, if this parameter alone is provided, the function still works, taking in the default values for the rest of the parameters.

destination-folder : This is the folder on the server into which the file gets uploaded finally. Defaults to the current folder where the script exists. Note:- Always put a trailing ‘/’.

file-name : The final name of the uploaded file on the server. Defaults to the original file name on your local system.

allowed-file-types : Restrict upload to a string of comma separated file types. Eg: image/jpeg,image/pjpeg,image/gif,image/png

max-file-size : Maximum allowable size of uploading file in bytes.

An example of the function in action is as below.

$newId = $query->getNewId('id');

$query->add( 'id' , $newId );

$query->upload( 'file_my_image' , 'my_gallery/' , $newId , ‘image/jpeg,image/pjpeg,image/gif’ , 2000000 );

In the above example, I wanted to keep the uploaded file-name the same as database record ID (eg: 2.jpg). For this I have introduced a new function $query->getNewId(’id’). This function can calculate and return the next auto_increment id for the passed database field name. Then in line 2, forcefully assigned value $newId to the field ‘id’. At last used $query->upload() function to upload the file and insert the filename into the database field simultaneously.

If that simply ads more confusion, Download and try out an example, its simple.

Developed by : Jaichand Singh

Date : 26.08.2008

Updated : 08.09.2009

Company : Aufait Technologies.

jaichand Custom Application Development, PHP Custom Application, Learnings, PHP