only Delete option is not working PHP CRUD

Question

I have been working on basic PHP CRUD. I am using PHP, AJAX, MySQL based CRUD.

I have features like Add / Update / Delete / GetResults / Search. Everything is working fine except for the Delete option. I have tried many ways to solve this but can't seem to find an exact error or mistake in code. Here is the code I am using for CRUD operations.

exam_packages.php

<!DOCTYPE html>
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Add / Update / Delete Exam Packages</title>
<link rel="stylesheet" href="css/bootstrap.min.css" type="text/css" media="all">
<link href="css/jquery.bootgrid.css" rel="stylesheet" />
<script src="js/jquery-1.11.1.min.js"></script>
<script src="js/bootstrap.min.js"></script>
<script src="js/jquery.bootgrid.min.js"></script>
</head>
<body>
    <div class="container">
      <div class="">
        <h1 style="text-align: center;">Add / Update / Delete Exam Packages</h1>
        <div class="col-sm-12">
        <div class="well clearfix">
            <div class="pull-right"><button type="button" class="btn btn-xs btn-primary" id="command-add" data-row-id="0">
            <span class="glyphicon glyphicon-plus"></span> Add New Exam Package</button></div></div>
        <table id="exam_package_grid" class="table table-condensed table-hover table-striped" width="60%" cellspacing="0" data-toggle="bootgrid">
            <thead>
                <tr>
                    <th data-column-id="pkg_id" data-type="numeric" data-identifier="true">Package ID</th>
                    <th data-column-id="test_quantity">Test Quantity</th>
                    <th data-column-id="price">Price</th>
                    <th data-column-id="commands" data-formatter="commands" data-sortable="false">Commands</th>
                </tr>
            </thead>
        </table>
    </div>
      </div>
    </div>

<div id="add_model" class="modal fade">
    <div class="modal-dialog">
        <div class="modal-content">
            <div class="modal-header">
                <button type="button" class="close" data-dismiss="modal" aria-hidden="true">&times;</button>
                <h4 class="modal-title">Add New Exam Package</h4>
            </div>
            <div class="modal-body">
                <form method="post" id="frm_add">
                          <input type="hidden" value="add" name="action" id="action">
                  <div class="form-group">
                    <label for="test_quantity" class="control-label">Test Quantity:</label>
                    <input type="text" class="form-control" id="test_quantity" name="test_quantity"/>
                  </div>
                  <div class="form-group">
                    <label for="price" class="control-label">Price:</label>
                    <input type="text" class="form-control" id="price" name="price"/>
                  </div>
            </div>
            <div class="modal-footer">
                <button type="button" class="btn btn-default" data-dismiss="modal">Close</button>
                <button type="button" id="btn_add" class="btn btn-primary">Save</button>
            </div>
            </form>
        </div>
    </div>
</div>
<div id="edit_model" class="modal fade">
    <div class="modal-dialog">
        <div class="modal-content">
            <div class="modal-header">
                <button type="button" class="close" data-dismiss="modal" aria-hidden="true">&times;</button>
                <h4 class="modal-title">Edit Exam Package</h4>
            </div>
            <div class="modal-body">
                <form method="post" id="frm_edit">
                          <input type="hidden" value="edit" name="action" id="action">
                          <input type="hidden" value="0" name="edit_pkg_id" id="edit_pkg_id">
                  <div class="form-group">
                    <label for="test_quantity" class="control-label">Test Quantity:</label>
                    <input type="text" class="form-control" id="edit_test_quantity" name="edit_test_quantity"/>
                  </div>
                  <div class="form-group">
                    <label for="price" class="control-label">Price:</label>
                    <input type="text" class="form-control" id="edit_price" name="edit_price"/>
                  </div>
            </div>
            <div class="modal-footer">
                <button type="button" class="btn btn-default" data-dismiss="modal">Close</button>
                <button type="button" id="btn_edit" class="btn btn-primary">Save</button>
            </div>
            </form>
        </div>
    </div>
</div>
</body>
</html>
<script type="text/javascript">
$( document ).ready(function() {
    var grid = $("#exam_package_grid").bootgrid({
        ajax: true,
        rowSelect: true,
        post: function ()
        {
            /* To accumulate custom parameter with the request object */
            return {
                id: "b0df282a-0d67-40e5-8558-c9e93b7befed"
            };
        },

        url: "exam_package_response.php",
        formatters: {
                "commands": function(column, row)
                {
                    return "<button type=\"button\" class=\"btn btn-xs btn-default command-edit\" data-row-id=\"" + row.pkg_id + "\"><span class=\"glyphicon glyphicon-edit\"></span></button> " + 
                        "<button type=\"button\" class=\"btn btn-xs btn-default command-delete\" data-row-id=\"" + row.pkg_id + "\"><span class=\"glyphicon glyphicon-trash\"></span></button>";
                }
            }
   }).on("loaded.rs.jquery.bootgrid", function()
{
    /* Executes after data is loaded and rendered */
    grid.find(".command-edit").on("click", function(e)
    {
        //alert("You pressed edit on row: " + $(this).data("row-id"));
            var ele =$(this).parent();
            var g_pkg_id = $(this).parent().siblings(':first').html();

        //console.log(grid.data());//
        $('#edit_model').modal('show');
                    if($(this).data("row-id") >0) {

                                // collect the data
                                $('#edit_pkg_id').val(ele.siblings(':first').html()); // in case we're changing the key
                                $('#edit_test_quantity').val(ele.siblings(':nth-of-type(2)').html());
                                $('#edit_price').val(ele.siblings(':nth-of-type(3)').html());
                    } else {
                     alert('No row selected! First select row, then click edit button');
                    }
    }).end().find(".command-delete").on("click", function(e)
    {

        var conf = confirm('Delete No.' + $(this).data("row-id") + ' package?');
        alert(conf);
    if(conf==true){
      // console.log($(this).data("row-id"));
      $.post('exam_package_response.php', { id: $(this).data("row-id"), action:'delete'}, function()
      {
        $("#exam_package_grid").bootgrid('reload');
      }); 
            $(this).parent('tr').remove();
            $("#exam_package_grid").bootgrid('remove', $(this).data("row-id"))
                    }
    });
});

function ajaxAction(action) {
                data = $("#frm_"+action).serializeArray();
                $.ajax({
                  type: "POST",  
                  url: "exam_package_response.php",  
                  data: data,
                  dataType: "json",       
                  success: function(response)  
                  {
                    $('#'+action+'_model').modal('hide');
                    $("#exam_package_grid").bootgrid('reload');
                  }   
                });
            }

            $( "#command-add" ).click(function() {
              $('#add_model').modal('show');
            });
            $( "#btn_add" ).click(function() {
              ajaxAction('add');
            });
            $( "#btn_edit" ).click(function() {
              ajaxAction('edit');
            });
});
</script>

exam_package_resposnse.php

<?php
    //include connection file 
    include_once("db_connection_mysqli.php");

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

    $params = $_REQUEST;

    // console.log($params);
    // echo '<script>alert("aaaaaaaaaa");</script>';
    $action = isset($params['action']) != '' ? $params['action'] : '';
    $empCls = new ExamPackage($connString);

    switch($action) {
     case 'add':
        $empCls->insertExamPackage($params);
     break;
     case 'edit':
        $empCls->updateExamPackage($params);
     break;
     case 'delete':
        $empCls->deleteExamPackage($params);
     break;
     default:
     $empCls->getExamPackages($params);
     return;
    }

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

    public function getExamPackages($params) {

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

        echo json_encode($this->data);
    }

    function insertExamPackage($params) {
        $data = array();;
        $sql = "INSERT INTO `tblExamPackages` (test_quantity, price) VALUES('" . $params["test_quantity"] . "', '" . $params["price"] . "');  ";

        echo $result = mysqli_query($this->conn, $sql) or die("error to insert package data");

    }

    function getRecords($params) {
        $rp = isset($params['rowCount']) ? $params['rowCount'] : 10;

        if (isset($params['current'])) { $page  = $params['current']; } else { $page=1; };  
        $start_from = ($page-1) * $rp;

        $sql = $sqlRec = $sqlTot = $where = '';

        if( !empty($params['searchPhrase']) ) {   
            $where .=" WHERE ";
            $where .=" (test_quantity LIKE '%".$params['searchPhrase']."%' ";
            $where .=" OR price = '".$params['searchPhrase']."' )";
       }
       if( !empty($params['sort']) ) {  
            $where .=" ORDER By ".key($params['sort']) .' '.current($params['sort'])." ";
        }
       // getting total number records without any search
        $sql = "SELECT * FROM `tblExamPackages` ";
        $sqlTot .= $sql;
        $sqlRec .= $sql;

        //concatenate search sql if value exist
        if(isset($where) && $where != '') {
            $sqlTot .= $where;
            $sqlRec .= $where;
        }
        if ($rp!=-1)
        $sqlRec .= " LIMIT ". $start_from .",".$rp;


        $qtot = mysqli_query($this->conn, $sqlTot) or die("error to fetch tot package  data");
        $queryRecords = mysqli_query($this->conn, $sqlRec) or die("error to fetch package data");

        while( $row = mysqli_fetch_assoc($queryRecords) ) { 
            $data[] = $row;
        }

        $json_data = array(
            "current"            => intval($params['current']), 
            "rowCount"            => 10,            
            "total"    => intval($qtot->num_rows),
            "rows"            => $data   // total data array
            );

        return $json_data;
    }

    function updateExamPackage($params) {
        $data = array();
        //print_R($_POST);die;
        $sql = "Update `tblExamPackages` set test_quantity = '" . $params["edit_test_quantity"] . "', price='" . $params["edit_price"]."' WHERE pkg_id='".$_POST["edit_pkg_id"]."'";

        echo $result = mysqli_query($this->conn, $sql) or die("error to update package data");
    }

    function deleteExamPackage($params) {
        $data = array();
        echo "<script>
        var conf = confirm('Delete No.' + $(this).data('row-id') + ' package?');
        alert(conf);</script>";

        $sql = "delete from `tblExamPackages` WHERE pkg_id'" . $_REQUEST["edit_pkg_id"] . "'";

        echo $result = mysqli_query($this->conn, $sql) or die("error to delete package data");
    }
}
?>

Please let me know If I am missing anything here. Everything is working fine except Delete

So what _does_ happen when you attempt to delete? Have you done any debugging to try to narrow down the problem? — Patrick Q, Oct 14 '19 at 16:19
`WHERE pkg_id'" . $_REQUEST["edit_pkg_id"] . "'"` First, _never_ inject form data directly into a query like that. Second, you're not posting a "edit_pkg_id" key when you post `{ id: $(this).data("row-id"), action:'delete'}` — Patrick Q, Oct 14 '19 at 16:27
Wait, why is there JavaScript in the `deleteExamPackage()` method? It’s way too late at this point. You’re confusing server side and client side — Tim Morton, Oct 14 '19 at 16:31
@PatrickQ: As coded, it shows dialog box with a message true but does not actually delete anything in DB. It seems I am not getting `pkg_id` via `$_POST` method. — Mahadev, Oct 14 '19 at 16:42
@TimMorton: Actually I was trying to check if I am getting data in `$(this).data('row-id')`. I am not getting data in `$_REQUEST["edit_pkg_id"]` though. Any idea why? — Mahadev, Oct 14 '19 at 16:44
@TimMorton: As I am getting data in `$(this).data('row-id')`, is there any way to pass it to `PHP` delete statement in the same block? That will solve this I think. — Mahadev, Oct 14 '19 at 16:50
@PatrickQ: Well, I tried like this - `{ id: row.pkg_id, action:'delete'}` but it's not working. Am I still missing something here? What should I post? :( — Mahadev, Oct 14 '19 at 17:09
Your PHP needs to look for the key that is posted by your ajax. You are currently posting `id`. So you either need to use that in your PHP, or you need to change that to match the key currently used by your PHP. — Patrick Q, Oct 14 '19 at 17:15
[How to get the error message in MySQLi?](https://stackoverflow.com/a/22662582/1839439) — Dharman, Oct 14 '19 at 17:23
@PatrickQ: Well, `$(this).data("row-id")` is the key posted by AJAX which I am passing to variable ID which then posted to next file for further processing — Mahadev, Oct 14 '19 at 17:41
No. `$(this).data("row-id")` is the _value_. Now might be a good time for you to review some basic jQuery/ajax tutorials. — Patrick Q, Oct 14 '19 at 17:42
@PatrickQ: Yeah, I think you are right. But for the sake of this problem, can you tell me the solution. Will help me understand more. — Mahadev, Oct 14 '19 at 17:48

score 1 · Answer 1 · edited Jun 20 '20 at 09:12

Short answer:

Your delete query has a syntax error. This was hinted at in the comments...

You’re also mixing server side and client side by trying to use JavaScript in a php class. That’s a major mistake and requires some going back to review the basics.

Long answer:

I perceive you’re trying to move towards OOP programming, and you’re doing some things right; notably, injecting the database connection into your crud object.

But there’s some things that could be done better. Here’s a few ideas—

Use PDO instead of mysqli, and use prepared statements. Very likely this would have eliminated the syntax error in the query.
Always return json encoded data from Ajax requests. I personally always have the php script respond with something like return json_encode( ['message'=>$message, 'data'=>$data] );. Then your JavaScript back in the browser can decide what to communicate to the user.
Methods should do One Thing. If the method gets bigger than what fits on the screen, you probably need to refactor. For example, getRecords() checks for page number, creates a query, gets total number, and gets all records. This should probably be broken up into separate methods, or better yet, create a pagination object that could be injected into the query creation method.

BTW, to illustrate how important the prepared statements are: what if someone went to hxxp://yourdomain/exam_package_response.php?action=delete&edit_pkg_id=1+or+1%3D1?

Answer: you just lost all the data in this table. Prepared statements will prevent this.

Thank you for the suggestions. I'll definitely implement them in the program. `delete` query has a syntax error? Can you notify where and how? I need to solve this asap before investing time to learn basics again. I'll definitely work on basics but just need to solve this first — Mahadev, Oct 15 '19 at 03:33
In your head, the delete query is probably right. What you've actually programmed probably is not what you think you've programmed. I would be doing you a disservice to just name it; debugging is an essential part of programming. A couple of things you can do to find the error: 1) print the query. Since this is an ajax request, you'll have to look in the network tab of your browser's Developer Tools to see what the script's output is. 2) follow the link that Dharman gave in the comments. Once you see the error, you'll do a facepalm. — Tim Morton, Oct 15 '19 at 19:20
Thank you for your insights. I have followed your instructions and found that I am not getting `pkg_id` while trying to delete. I can access the same while update but not delete. Now I am trying to find a reason for that. Plus I had a simple mistake (=) in delete query. Thank you for guidance. — Mahadev, Oct 16 '19 at 07:18

score 0 · Answer 2 · answered Oct 15 '19 at 20:10

0

In the

deleteExamPackage($params) function

on line 7, before the echo statement, you forgot to add

=

change this

`$sql = "delete from `tblExamPackages` WHERE pkg_id'" . $_REQUEST["edit_pkg_id"] . "'";`

to

$sql = `"delete from `tblExamPackages` WHERE pkg_id = '" . $_REQUEST["edit_pkg_id"] . "'";`

answered Oct 15 '19 at 20:10

gbenga wale

359
4
23

follow what @Tim Morton told you – gbenga wale Oct 15 '19 at 20:20
Yeah, its one of the mistakes but that didn't solve the problem. – Mahadev Oct 16 '19 at 06:26

score 0 · Accepted Answer · answered Oct 16 '19 at 13:08

I managed to solve the problem with the help of some fellow members' guidance here.

So basically, I couldn't delete a row from my CRUD grid. And actually, I wasn't able to detect the error properly. As per the answers and comments above, I traced down the error and solved it.

Here are the changes I made to make it work.

Delete Query had a Syntax error.

My Delete query had a minor error. I missed = sign in where clause. Although that didn't original problem, that was an issue to solve.

Wasn't getting pkg_id sent from AJAX to PHP.

So the core problem was, my query was executing but I wasn't getting the value of pkg_id in the passed variable. Actually I wasn't debugging it properly otherwise I did not even have to post it here. Thanks to Mr. Tim Morton above, I debugged the issue. Strangely, with the same method, I was getting value in UPDATE function but not in DELETE. I tried some methods to work that out but couldn't manage.

So I used Cookies to solve the problem. The workaround is Creating Cookie using AJAX and retrieving its value in PHP. Here is the code I used to do this. I am mentioning only part of code I rewrote.

exam_packages.php

Wrote a function to create cookie right after $(document).ready(function()

function createCookie(name, value, days) 
    {
        var expires;
        if (days) 
          {
            var date = new Date();
            date.setTime(date.getTime() + (days * 24 * 60 * 60 * 1000));
            expires = "; expires=" + date.toGMTString();
          }
          else 
          {
            expires = "";
          }
        document.cookie = escape(name) + "=" + escape(value) + expires + "; path=/";
    }

and using it as

createCookie("pkg_id", $(this).data("row-id"), "1");

after var conf = confirm('Delete No.' + $(this).data("row-id") + ' package?');

This creates Cookie named pkg_id.

exam_package_resposnse.php

Accessed cookie value in this file

$pkg_id = $_COOKIE["pkg_id"];

and used pkg_id as a parameter for my where clause.

I know this might not be a proper way to solve this but this definitely one of the ways. I tested it and it's working. If anyone has a better way to do this, please feel free to share as it might be useful to someone else and me too. :)

Regards

only Delete option is not working PHP CRUD

3 Answers3