Useful database helper class to generate CRUD statements using PHP and MySQL


For my AngularJS applications I use PHP as my server side language which mainly interacts with the MySQL or Oracle database. I need to created a database helper class using PHP to collect all recurring database operations into a single class, so that we can easily use it whenever we need to select, insert, update or delete record from database.

This database helper class generate the CRUD statements dynamically and always output the response in a standard format. Hence reducing the recurring tasks load on programmer. This also makes bug tracking task easy as everything stores in a central location.


I thought about sharing my work. I hope you all like it.


  • Well tested code.
  • Secured against SQL injections by using PHP Data Objects(PDO) and Prepared Statements.
  • Always returns objects which you can directly use to inform the user.
  • Includes proper error handling and information broadcast.

The Framework:

Mainly in SQL database we use the following 4 operations to manage our data (DML Operations)
C – Create
R – Read
U – Update
D – Delete

R – Read: The following is a simple read or select operation in SQL

Now we will generate similar select statement and return the selected data using the followin helper function

C – Create : The following is a simple create or insert operation in SQL

After this operation we need to return the caller function the proper data with any associated success or error message. To handle those recurring tasks these CRUD modules will be helpful for you. That’s why i named this databaseHelper(dbHelper) class.

For insert operation we need the table name, the values to insert and mandatory columns

U – Update : Following is a simple update operation in SQL where we update email of the user having id 170

For update operation we need the table name, the columns names and the corresponding values to update, the condition on which the update will occure on a record

D – Delete: Following is a simple delete operation in SQL

For delete operation we need the table name and the condition on which the delete will occure. In this case the where clause or condition of delete is mandatory otherwise all records will be deleted from database. Hence we will run the delete script only if atleast one condition is specified.

For any application, data is always essential. So to protect and validate our data we have to sanitise it both at the client side(for speed and instant help to the user) and server side(for better security). Here comes the module verifyRequiredParams();

How to use:

Download the zip file from the download link provided above. Import the customer_php.sql file into your database

Add your database settings to the file “config.php”

Following is the template of how to use it

Here are example methods

Next tutorial i will create a simple AngularJS application and show you the power of these database helper functions used in a real world app.

Print Friendly

Related Posts

Written By:
  • Miki

    Very cool ! Do you have some kind of authenrication helper ? That will be cool. This is very simple and usefull, once again great job !!!

  • Felipe

    Very nice! I’m waiting for this part

    “Next tutorial i will create a simple AngularJS application and show you
    the power of these database helper functions used in a real world app”

  • Ricardo Tribaldos H.

    I’m getting this error: Fatal error: Call to undefined function echoResponse()… on line 14

    • itswadesh

      Thanks Ricardo for pointing out. I’ve uploaded the newer version.

  • Suresh Reddy

    hi swadesh behera your tutorials very nice.
    i like your posts
    but in this post i have an error

    Fatal error: Call to undefined function echoResponse() in dbHelper.php on line 14

    • itswadesh

      Thanks Suresh for pointing out. I’ve uploaded the newer version.

  • dyo

    I have error when import the database, #1067 – Invalid default value for ‘created’ anyone can help?

    • hcanos

      just change “datetime” to “timestamp”

  • manojkumar

    i unable to update and delete the data in db why?

  • Manojkumar

    i had created the database as in the code given and there is no problem in fetching and inserting values but when i update and delete ,those actions doesn’t complete successfully may i know what is the reason?, thanks for the code my friend.

  • kay

    How to user more than 1 where parameter in db select. For instance more than 1 id in $db->select(“customers_php”,array(‘id’=>171));

    • Razvan

      It’s self-explanatory. You’re using an array. So it would be: $db->select(“customers_php”,array(‘id’=>171, ‘foo’=>’bar’));

  • Isaac Manubag

    Nice work! thanks for sharing this. Can this support joins too?

    • Tony Benci

      Hi Isaac,

      I have only just started using this helper but pretty sure it could handle joins. I’ll work it out and let you know.


    • Tony Benci

      Yep, supports joins.


      $rows = $db->select(“ts_users LEFT JOIN ts_companies ON usr_co_id = co_id”,array());


      Actually, I am going to include a columns array in the select statement so you can nominate the columns that will be returned if you don’t want the complete dataset.

      I’ll post the code when I make the mod.


  • Tony Benci

    Hi Swadesh,

    First, thank you for this tutorial, I have found this class to be exactly what I was looking for to streamline my JSON work for AngularJS.

    OK, that said :), looking at the update code I found a problem.

    It seems that the presumption of the function is that the WHERE field would not be one of the fields being updated. The example is using a record ID as an example.

    If you use the current code with a before value for the WHERE and then an after value for the edit the column loop overwrites the before value with the after value and breaks everything.

    I fixed this simply by doing this;

    $a = array();

    $w = “”;

    $c = “”;

    foreach ($where as $key => $value) {

    $w .= ” and ” .$key. ” = :”.$key.”_w”;

    $a[“:”.$key.”_w”] = $value;


    foreach ($columnsArray as $key => $value) {

    $c .= $key. ” = :”.$key.”, “;

    $a[“:”.$key] = $value;


    If we had the following;

    $rows = $db->update(“customers_php”,array(‘name’ => ‘Ipsita Sahoo 2′, ’email’=>’email’),array(‘name’=>’Ipsita Sahoo’), array(‘name’, ’email’));

    After we run the function and before we execute the PDO we’d see in the $w array

    :name_w: string = “Ipsita Sahoo”

    :name: string = “Ipsita Sahoo 2”

    :email: string = “email”

    Of course, passing this array down will resolve everything so the update function will now handle updates of fields regardless of whether they are in the WHERE clause or not.

    Hope that helps others.


    Tony Benci

    • itswadesh

      Thanks Tony for the update. It was helpful

      • Tony Benci

        My pleasure… I am just modifying the SEARCH to enable a column list to allow selection field return (often there is no need to return the complete record) and will post the code when I have finished it.

        This is the first time I’ve used PDO… I will immediately replace all other dataobjects I think, it is a very good tool.


  • Tony Benci

    hello all,

    I have implemented this helper with PDO and love it. One thing I found was that the UPDATE and DELETE WHERE clauses were hard coded to “=”.

    There are occasions where you’d need to do both updates and deletes using “LIKE”.

    I added an $operator to the UPDATE and DELETE functions

    function update($table, $columnsArray, $where, $requiredColumnsArray, $operator)


    delete($table, $where, $operator)

    and made this change to the where loop

    foreach ($where as $key => $value) {

    $w .= ” and ” .$key. ” $operator :”.$key;

    $a[“:”.$key] = $value;


    which meant I could pass

    $rows = $db->update(“customers_php”,array(‘name’ => ‘Ipsita Sahoo’, ’email’=>’email’),array(‘name’=>’%Nahoo%’), array(‘name’, ’email’),’like’);

    and the update would handle all records like %Nahoo%.


    $rows = $db->delete(“customers_php”, array(‘name’ => ‘%Sahoo%’), ‘like’);

    and the update would handle all records like %Sahoo%.

    I considered making the $operator an array that would match the array of where fields so we could mix and match the “=” and “like” as needed but can’t see that as necessary for the what I usually do. If it becomes an issue you’d just add an array and resolve the operator in the loop.

    Hope that helps someone.



    • Tim Frangioso

      I took your idea about creating a operatorArray and I ran with it. Hope you don’t mind 😉

      Here is how I changed the update and delete functions

      function update($table, $columnsArray, $where, $requiredColumnsArray, $operatorArray)

      foreach ($where as $key => $value) {
      $w .= ” and ” .$key. ” $operatorArray[$i] :w”.$key;
      $a[“:w”.$key] = $value;

      Now I can do queries like this:

      $rows = $db->update(“customers_php”,array(’email’ => ‘’),array(’email’ => ‘%fred%’,’id’ => ’24’ ), array(’email’), array(‘LIKE’, ‘=’));

      I did the same thing to the delete function as well. Of course in this test database this is not very useful but in my live database this is like mana from heaven.

      Thanks a bunch Tony – I have made other adjustments to the code if you are interested let me know.

      • Tony Benci

        Hi Tim,

        A further and elegant improvement; I like that a lot :). I built what I needed for the application I needed right there and then but can see exactly what you’ve implemented and will definitely dust this code off and tweak it the same.

        This is what I love about the Internet :)…


  • dl

    I’m giving your helper a chance for my project, it looks really nice for now. Thank you very much.

  • Sanket Wankhade

    Nice work. but i have one problem in ‘select’ function can you suggest me for ‘like clause’

  • freddy

    Hello Admin, thanks for your effort but hard to understand for who newbie at this case, can i give suggest to make one by one i mean first day insert, second day delete, etc it will more power full understand but over all thanks Again

    Freddy sidauruk

  • Betty

    Thanks! This was very helpful 🙂

  • Pingback: AngularJS, MySQL and PHP RESTful API()

  • Norbs ツ

    Hi man thanks for sharing! I have a question for you (I’m newby in this) How can I do to create a select with options from any database?


    Hi there !

    First of all thanks a lot for sharing your codes, it was very helpful to me, as an angularjs beginner.

    I’ve been using your ‘Products manager’ to learn how to interact with angularjs, and so far i’ve learned a lot, but there’s something I’m stalling on : I can’t use the “Delete” function provided with your DBHelper.

    The thing is I don’t think it’s coming from your code, but rather my server, because deleting is working fine on your demo site. I’ve tried to reupload your app to see if my changes where in cause, but same problem.

    When trying to use the “delete” function, I get this error in the console :

    DELETE http://mylink/manage/api/v1/delete_income/7 net::ERR_EMPTY_RESPONSE

    I’ve been looking around the web for hours trying to find a solution… and nothing so far !

    Maybe you already have seen something similar ?

    Thanks again,

  • Zizou

    Can search multiples criterias ? $rows = $db->select(“rent”,array(‘ZipCode’=>3314 AND 33149));

  • Tim Frangioso

    Swadesh This is exactly what I have been looking for. (Thank you) * 2^302 -> that’s the estimated number of particles in the universe 😉

    I did two things first I had problems with my updates because I need to do queries like this:

    $rows = $db->update(“customers_php”,array(‘name’ => ‘Tim Frangioso’),array(‘name’ => ‘tim frangioso’), array(‘name’));

    The crux of this is that I was using the same placeholder :name in the WHERE clause and in the SET portion of the statement. Here is where I found the solution to my problem

    Any way I was able to fix this simply by reorganizing the update code a bit:

    foreach ($columnsArray as $key => $value) {
    $c .= $key. ” = :c”.$key.”, “;
    $a[“:c”.$key] = $value;
    foreach ($where as $key => $value) {
    $w .= ” and ” .$key. ” = :w”.$key;
    $a[“:w”.$key] = $value;

    Now my placeholder for the columns is “:cname” and for the where clause it is “:wname”. Also I have to move the foreach loops so the columns came first that way the values in the $a array will be in the correct order for the execute.

    Anyway I have this fixed code. I would love to share it. Do you have this on git?

  • Pingback: Ejemplos Proyectos Angular Descargar | Tusolutionweb()

  • tom chan

    great job, but it is better if it come with mysql limit and paging. thanks

  • tom chan

    I modified it like this and seems works!
    function select($table, $where, $limit=NULL){
    $a = array();
    $w = “”;
    foreach ($where as $key => $value) {
    $w .= ” and ” .$key. ” like :”.$key;
    $a[“:”.$key] = $value;
    $stmt = $this->db->prepare(“select * from “.$table.” where 1=1 “. $w .’ ‘.$limit);