Delete a database row using JQuery

Question

I am using a PHP function to format any 2D PHP array to HTML table, In that table I need to add a delete button in each row, So when the user clicks the delete button jQuery should take particular fields ( 3 fields ) and submit in a php file and it should give the response without reloading the page, I have several dynamic tables in same PHP files, So i have used $table_name as the form ID to differentiate the FORMS, and In the del.php ( Where my form get submitted ) I decide which table should I look up to delete the row using the PRIMARY KEY. My Problem is Do I have to create Forms Within each table to do this task? or can I simply put some fields and submit the form using jQuery?

Any help would be much appreciable .

function formatArrayToTable($foo, $deletable = 0, $restaurant_id ='', $table_name = '') {
  //open table
  echo '<table class="imagetable">';

  // our control variable
  $first = true;

  foreach($foo as $key1 => $val1) {
    //if first time through, we need a header row
    if($first){
      echo '<tr>';
      foreach($val1 as $key2 => $value2) {
        echo '<th>'.$key2.'</th>';
      }

      if($deletable) {
        echo "<th>'Delete'</th>";
      }

      echo '</tr>';

      //set control to false
      $first = false;
    }

    echo '<tr>';

    foreach($val1 as $key2 => $value2) {
      echo '<td>'.$value2.'</td>';
    }

    if($deletable) {
      $primary = $val1["id"];

      echo "<input type='hidden' name='table_name' value='{$table_name}' />";
      echo "<input type='hidden' name='restaurant_id' value='{$restaurant_id}' />";
      echo "<td><input class='delete_class' type=\"button\" name=\"delete_id\" value={$primary} onclick='SubmitForm($table_name)'/></td>" ;
    }
    echo '</tr>';
  }
  echo '</table>';
}

My Javascript Function

function SubmitForm(formId){
  var message = "";
  $("#"+formId+" input").each(function() {
    message += $(this).attr("name");
  });

  $.ajax({
    type: "POST",
    url: "del.php",
    data: message,
    success: 
      function() {
        $('#message').html("<h2>Contact Form Submitted!</h2>")
          .append("<p>Entry is Deleted </p>")
          .hide()
      }
  });
}

-Regards

loushou · Accepted Answer · 2013-12-24 04:08:43Z

Your question seems to ask if you can remove items from a DB using just jQuery. Conventionally, as far as I know, this is not doable, because your DB is server-side and your jQuery is client-side. That being said, I am sure some kook has created a library for it. Despite that, to answer your actual question:

You need to know how you can use jQuery to simulate direct removal of a table row from a DB table. Here is a rough example of your needed jQuery, a sample output of your current php function, and something that should live in del.php to handle the actual delete.

Example Table

Quick notes. Add thead and tbody tags to help browsers with the displaying. Remove the onclick="" bit, you are using jQuery, so just add your callbacks with a JavaScript block. Make sure your code adds the `.submittable' class (or other descriptive name) to your table. You could wrap the whole table in a form, then use a plugin like jquery form to handle submissions of each form, but that seems like overkill for only handful of fields, so I will explain how to do it with the raw materials.

<table class="imagetable submittable">
  <thead>
    <tr>
      <th>id</th>
      <th>name</th>
      <th>file</th>
      <th>meta</th>
    </tr>
  </thead>
  <tbody>
    <tr>
      <td>
        <input type='hidden' name='table_name' value='secret_image_table' />
        <input type='hidden' name='restaurant_id' value='123' />
        <input class='delete_class' type='button' name='delete_id' value="Delete" />
      </td>
      <td>Silly Cat Image</td>
      <td>yarny-cat.jpg</td>
      <td>{"submitter":"De Zéro Toxin"}</td>
    </tr>
  </tbody>
</table>

jQuery code block

It is a terrible idea to submit your table name from any client-side form, ajax or otherwise. That is super sensitive information, and any programmer/hacker could use that knowledge to their advantage when trying to attack your site. Despite that, I don't know the usage of this, so it may be fine in your setting. Still bad practice though.

// any time any element with the 'delete_class' on it is clicked, then
$(document).on('click', '.delete_class', function(e) {
  var row = $(this).closest('tr');

  var data = {
    table: $('[name="table_name"]').val(),
    id: $('[name="restaurant_id"]').val()
  };

  $.post('del.php', data, function(r) {
    // do some special stuff with your json response 'r' (javascript object)
    ...

    // do what you showed us you are doing, displaying a message
    $('#message').html("<h2>Contact Form Submitted!</h2>")
      .append("<p>Entry is Deleted </p>")
      .hide();

    // remove the row, since it is gone from the DB
    row.remove();
  }, 'json');
});

del.php

Again, table_name on submission = bad-idea. Horse beaten.

// define a delete function that accepts a table name an id

// define some functions to sanitize your $_POST data, to prevent SQL Injection.
// run said functions before you get to this point

// json response function, since you said you want a response from your js
function respond($data) {
  echo @json_encode($data);
  exit;
}

if (empty($_POST)) respond(array('error' => 'Invalid request')); 

$table_name = $_POST['table_name'];
$id = $_POST['id'];

$response = deleteRecord($table_name, $id);

if ($response == $what_you_expect_on_a_successful_delete) {
  // create a response message, in associative array form
  $message = array('success' => true);

  // add some other information to your message as needed
  $message['sideNote'] = 'I like waffles.';

  // respond with your message
  respond($message);
}

// if we got this far your delete failed
respond(array('error' => 'Request Failed'));

Hope this helps.

Allen King · Accepted Answer · 2013-12-24 05:09:35Z

0

If you really want to use jQuery to delete a row in a DB directly, you will need to establish a DB connection from jQuery. Not such a bright idea. Instead, you should have a server side function to do the job and call that function using an AJAX call from jQuery.

answered Dec 24, 2013 at 5:09

Allen King

2,5326 gold badges38 silver badges59 bronze badges

Collectives™ on Stack Overflow

Delete a database row using JQuery

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related