mySQL Database Querying Functions

Back to home page | Download the functions | Comment on this code

I have constructed the following three functions (an architecture, if you will) to help ease the "workings" with a mySQL database. They mitigate the need for a PHP object to be created, and instead SQL can be passed as a string to these functions. The following explains each function's specific use:

  1. q(STRING, int [0/1 optional]) (placing a "1" as the second parameter will put you in "debug" mode to help you find where a query fails)
  2. This function is best used to query the database for multiple fields. It returns an associative array (with the indexes being the NAME of the database field). For example you could execute the following code:

    $recordset = q("SELECT fldName, fldPhoneNumber
                          FROM tblPersons LIMIT 20");
    

    This would retrieve up to 20 names and phone numbers from the tblPersons table and store the fields into the var $recordset. Then to retrieve the data you could do the following:

    foreach ($recordset as $record){
        $name   = $record[fldName];
        $phone  = $record[fldPhoneNumber];
        echo "$name" . "'s" . " phone # is " . $phone . ";
    }
    

    (you can obviously also use the list() function [with any type of loop] to put the values into variables as well, but I find the above easier to read)

    Similarly, two additional functions are created:

  3. qr(STRING, int [0/1 optional])
  4. The qr function can be used to send INSERT, UPDATE, OR SELECT statements to the database. For the former two SQL attempts, (insert/updatE) the function will return true if rows were created or updated (respectively) [and false if not]. It'll also retrieve one row of data into an array for easy retrieval. This is demonstrated below.

    $person =  qr("SELECT fldName, fldAddress
                      FROM tblPersons
                          WHERE pkPersonID = 3");
    $person_name    = $person[fldName];
    $person_address = $person[fldAddress];
    

    As you can see, you do NOT need to iterate through the array to get at its data since there is only one row retrieved.

  5. q1(STRING, int [0/1 optional])
  6. This last function, q1, is easy for getting one SINGLE value from a SQL query that returns one row with one value. Example:

    $person_name = q1("SELECT fldName FROM tblPersons
                             WHERE pkPersonID = 23");
    

There you have it: 3 very easy functions create an easy, non-class-based architecture for querying from a mySQL database!


Download the functions



Your Rating      Overall Rating   

Comment on this Code

Your Name
Comment

Recent Comments

"these functions have helped take 10s of hours from the development on most every project I do." - Posted by Sean Dempsey on Thursday Feb 7th, 2008 at 1:47pm

"c445t [a] [/a] " - Posted by ma686zda on Sunday Jul 13th, 2008 at 12:07am

"c416t [a] [/a] " - Posted by ma548zda on Sunday Jul 13th, 2008 at 12:19am

"I found just what I was needed, and it was entertiannig!" - Posted by Jayvee on Thursday Jul 14th, 2011 at 8:00am

"Thank you for present hgihly wonderful informations. Your internet is fantastic, I am happy by the material which you have on this blog. It exhibits how very well you have an understanding of this subject. Bookmarked this page, will arrive again for a whole lot more. You, my close friend, I observed just the details I already searched all over the place and just couldn’t obtain. What a perfect web site. Such as this web page your site is one particular of my new favorites.I such as this info proven and it has provided me some type of contemplation to possess good results for some purpose, so retain up the decent work!" - Posted by Madara on Wednesday Oct 17th, 2012 at 12:44am