Add,Edit and Delete Record using Bootgrid , PHP and MySQL

Last Updated On: | By: Parvez

jQuery Bootgrid is very popular grid plugin and especially designed for bootstrap.This tutorial help to add .edit and delete records using php and mysql. I am using AJAX technology to add,edit and delete records with mysql. Bootgrid is very rich jQuery grid plugin which is used to convert a simple HTML table into grid with powerful features like sorting,pagination,searching, add record,edit record and delete record functionality. We will cover below listed features using PHP, MySQL and ajax.I done many googling but do not found any example which has below features,

  • Bootgrid listing Using Ajax
  • Bootgrid listing with search server-side using Ajax
  • Enable ajax pagination
  • Enable Sorting
  • Enable Command button with for-matter function
  • Enable server side dynamic pagination
  • Add records using bootstrap ModalBox and ajax
  • Edit records using bootstrap ModalBox and ajax
  • Delete records using bootstrap ModalBox and ajax

You can bind data in bootgrid, Either you use the server-side way (e.g. a REST service) for querying a dynamic data source like a database or you use the client-side way for simple in-memory data.

jQuery Bootgrid dependent library is jquery and bootstrap. We will learn how to add,edit and delete records in bootgrid using php, mysql and ajax.

Add,Edit and Delete records using Bootgrid, PHP and MySQL

I am using following files and folder

dist folder: This folder will contains all css and js libs of jquery,bootstrap and bootgrid.
font folder: This folder will contains all fonts related files.
index.php: This file will use to create HTML layout and handle ajax response.
connection.php: This file will use to create database connection.
response.php: This file will use to handle all server side functionality. I will create add,edit,delete action methods and return back json response to ajax method.

bootgrid-serverside

Also Checkout other tutorials of grid,

How to add listing in Bootgrid

Step 1: Created connection file to handle database connection and return db connection object.

Where is:

  1. $servername: your mysql db hostname
  2. $username: mysql db username
  3. $password: mysql db pass
  4. $dbname: database name

Step 2: Included all js and css files into index.php file.

Step 3: Create HTML table for listing data in index.php file.

Here we have added a 'Add record' button which will use to create new record and table for listing records.

Step 4: Applied Bootgrid constructor method on HTML table.The Bootgrid method converts HTML table into beautiful grid listing.

Added above jquery code into footer of index.php file.

Step 5: Added action handler in response.php file.

How to add insert record in Bootgrid

Step 1: Added Bootstrap add modal box to add record in index.php file.

Step 2: Added callback method in 'add record' button to open add modal box.

Step 3: Added event on submit button to add modal input values.

Step 4: Added ajaxAction() method which will fire ajax request to server-side. We will passed json data to server side using ajax technology.

Step 6: Added a switch option in response.php file.

Step 7: Added add action method in response.php file which will insert record in mysql database and send status.

How to add Edit record in Bootgrid

Step 1: Added Bootstrap edit modal box to update record in index.php file.

Step 2: Added callback method on event of Bootgrid contsructor,

We are collecting all data of clicked row which you want to edit and shows value into modal box.

Step 3: Added click event on submit button to edit modal input values.

Step 4: Added a switch option in response.php file.

Step 5: Added add action method in response.php file which will update record in mysql database and send status.

How to add Delete record in Bootgrid

You need to select row in Bootgrid then click delete record button icon, Each row of bootgrid has edit and delete row icon and bonded action on them.

Step 1: Added delete callback method under bootgrid constructor,

Step 2: Added a switch option in response.php file.

Step 3: Added add action method in response.php file which will insert record in mysql database and send status.

You can download source code and Demo from below link.

  Live Demo

How To Use Downloaded Source Code

Conclusion :

In previous tutorial, we have learn listing of bootgrid with PHP, MySQL and AJAX.This tutorials help to add crud functionality with bootgrid using ajax.I have demonstrate about how to add,edit and delete row using php,mysql and ajax.

80 thoughts on “Add,Edit and Delete Record using Bootgrid , PHP and MySQL”

  7. Hi,

    I am using this currently and its working great. I would like to implement inline editing with this. Do you think its possible with jQuery bootgrid? If yes, a few suggestions would be really helpful!

    Thanks so much!

    Reply

  8. Hi, newbie question. How can i link the database? I’m currently using xampp and still cant connect even thou i changed the database locations

    Reply

  9. hi i have commented delete option, you uncomment del ajax like,
    .end().find(“.command-delete”).on(“click”, function(e)
    {

    var conf = confirm(‘Delete ‘ + $(this).data(“row-id”) + ‘ items?’);
    alert(conf);
    if(conf){
    /*$.post(‘response.php’, { id: $(this).data(“row-id”), action:’delete’}
    , function(){
    // when ajax returns (callback),
    $(“#employee_grid”).bootgrid(‘reload’);
    }); */
    //$(this).parent(‘tr’).remove();
    //$(“#employee_grid”).bootgrid(‘remove’, $(this).data(“row-id”))
    }
    });

    response.php file,
    case ‘delete’:
    $empCls->deleteEmployee($params);
    break;
    function deleteEmployee($params) {
    $data = array();
    //print_R($_POST);die;
    $sql = “delete from employee WHERE id='”.$params[“id”].”‘”;

    echo $result = mysqli_query($this->conn, $sql) or die(“error to delete employee data”);
    }

    Reply

  11. There id Problem when i m including your code in my other code which already have style.css .Bootstap style.css conflicts.is
    there is any solution to handle it.

    Reply

  16. Hi,

    Do you have solution to add additional rowCount function:
    QUOTE
    rowCount An Array of Integer which will be shown in the dropdown box to choose the row count. Default value is [10, 25, 50, -1]. -1 means all. When passing an Integer value the dropdown box will disapear.
    UNQUOTE

    I want to add in 5 to display in the datatable. Changing the related .js script doesn’t work.

    Also specifying column width is not working. How can it be like column widths be set to auto width instead (text-wrapped)
    Thanks

    Reply

  18. How can I specify column width of table? I try it but not working. I want to define individual column width for each column instead of auto width.

    Reply

  19. Hello,

    What an excellent tutorial.. help me alot!
    Anyway is there a way when click the save button (edit mode for example) the grid is load with the current page not refresh and back to the first page.

    Then, for some reason my save button click didn’t close the pop up (hide the div) but the save function working with no problem.. is it possible because of my action after update the table.. the script will fire an email to the user ?.

    Thank you!

    Reply

    • Ok,for now I dint get any error.You code quit good,and you have explain the code clearly.
      But can I ask one more question?
      May I know how to select certain data to display?If normal php code,I can do it,but jquery I really no idea
      exp:
      sql=”SELECT * FROM cusinfo where id = ‘$ID’ “;
      I dunno where to put this code,and what should replace with this code

      Reply

  26. I have removed the commented out alert box. And I am getting “You have pressed row : undefined”
    I have changed the code a little bit. What could e the possible reason?

    Thanking you in advance.

    Reply

  27. Thx for this, super useful.
    While I correctly set up the connection to the db, the initial form is stuck on “loading” and data is never displayed. (there are no error messages displayed)
    What am i missing?

    Reply

  30. Hi

    This is great code and I am using it without any issue.

    Just I have to include when Inserting Data to table, I need to run 3 different queries depending on conditions, I am just unable to get the result;

    here is code

    function insertFillEmpty($params) {
    $data = array();

    $catintid = “SELECT categoryinternalID FROM inhandemptystock”;
    $results = mysqli_query($this->conn, $catintid);
    $raw = mysql_fetch_array($results);
    $idcat = $raw[“categoryinternalID”];
    if ($idcat == “5”) {

    $sqll = “INSERT INTO testing (goog) VALUES(‘” . $params[“enteredBy”] . “‘); “;
    echo $result = mysqli_query($this->conn, $sqll) or die(“error to insert employee data”);

    } else {

    echo ”;
    echo ‘alert(“Something is wrong”)’;
    echo ”;
    }

    }

    Can you please help me to sort out the issue?

    Reply

  31. I want to run different query as per ID received from table, Please help to resolve;

    //include connection file
    include_once(“connection.php”);

    $db = new dbObj();
    $connString = $db->getConnstring();

    $params = $_REQUEST;

    $action = isset($params[‘action’]) != ” ? $params[‘action’] : ”;
    $empCls = new FillEmpty($connString);

    switch($action) {
    case ‘add’:
    $empCls->insertFillEmpty($params);
    break;
    case ‘edit’:
    $empCls->updateFillEmpty($params);
    break;
    case ‘delete’:
    $empCls->deleteFillEmpty($params);
    break;
    default:
    $empCls->getFillEmpty($params);

    return;
    }

    class FillEmpty {
    protected $conn;
    protected $data = array();
    function __construct($connString) {
    $this->conn = $connString;
    }

    public function getFillEmpty($params) {

    $this->data = $this->getRecords($params);

    echo json_encode($this->data);
    }

    function insertFillEmpty($params) {
    $data = array();

    $catintid = “SELECT categoryinternalID FROM inhandemptystock”;
    $results = mysqli_query($this->conn, $catintid);
    $raw = mysql_fetch_array($results);
    $idcat = $raw[“categoryinternalID”];
    if ($idcat == “5”) {

    $sqll = “INSERT INTO testing (goog) VALUES(‘” . $params[“enteredBy”] . “‘); “;
    echo $result = mysqli_query($this->conn, $sqll) or die(“error to insert employee data”);

    } else {
    echo ”;
    echo ‘alert(“Something is wrong”)’;
    echo ”;
    }

    }

    Reply

  33. Of course the id field is necessary. Also, I have another important identifying field used to pull records only for the current user. I do not want either of these fields to show up on the table, but need to access both of them. I cannot figure out a way to hide them in the table without presenting other problems. Could you please tell me how you would do this?

    Also, I tried setting column widths in the table using inline CSS. I believe you told another user this was possible, but, for some reason, it did not work for me.

    Thanks very much for the code. You need a donation button.

    Reply

  34. In addition to the record id, I also pull another field that is crucial, as it belongs to the current user. I need to access both of these variables. My problem is that I do not want these two fields to display in the table, they are superfluous to the user. In the “Add” and “Edit” forms, I simply make this hidden inputs. Everything I have tried as far as hiding these fields from the table results in other problems. Is there a way I can do this without rewriting most of the code?

    Additionally, I believe you told another person that one could use HTML or CSS width commands to control the column widths, but this has not worked for me using either.

    Reply

    • The css file has .bootgrid-table {table-layout: fixed;} This will override column width settings. Commenting out solves that problem.
      Still no idea how to hide columns without affecting add and edit form data.

      Reply

  35. hello guys,
    i downloaded the source code and i have got a problem .However i can do all the actions of the table (insert ,delete ) ,i cannot get the values from my database .It only shows me the id.When i press the action to edit ,all fields have   value.Can someone help me ?

    Reply

  36. Hello,
    i would like to add a command button that it keeps the selected row id of the table id=”employee_grid” in a input value of the form .Then to open a modal with another table to keep the id in a 2nd input value type of the form.
    when i do the action ,it will success only when i click the first elements of the table.
    Could you give me any advice of this?
    Thank you!
    my script command button has this code:

    .end().find(“.command-courses”).on(“click”, function(e)
    {
    document.getElementById(“id_form”).value = $(this).data(“row-id”);
    $(‘#courses_to_show’).modal(‘show’);
    });
    });

    Reply

  38. How can I add employee picture on add employee form and send picture into database and folder by response.php file

    Reply

  40. Hey when i am trying this code, data is not getting loaded into datatable, what will be the reason for that, what i am missing, can u guide me in this

    Reply

  43. Hello,

    thank you very much for this awesome tutorial!!!
    i want to add a new button near “Record” on the top named “Show open Records”.
    I have in my Database a column called “status” with possible values “open” and “closed”.
    What is the best to show all open Records via Button.

    In my getRecords function i have
    SELECT * FROM employee

    But if I press the Button “Show open Records” i want to execute:
    SELECT * FROM employee WHERE status = ‘open’

    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *