Solved

delete database record with modal

Posted on 2016-11-18
21
160 Views
Last Modified: 2016-11-20
I am trying to use a sweet alert popup with an ajax/jquery/php delete record. At this point when I click on the icon the sweet alert pops up but when I click delete, the record is not actually deleted and the row does not fade out. So, what I want to happen is that I click delete, the record deletes and the row fades out. I am not getting any errors in the console. Here is the jQuery:

$(document).ready(function () {
			$(".pe-7s-trash").click(function(e) {
				e.preventDefault();
				 var pid = $(this).attr('data-id');
				var parent = $(this).parent("td").parent("tr");
				
				swal({
					title: "Are you sure?",
					text: "You are about to delete this record!",
					type: "warning",
					showCancelButton: true,
					confirmButtonColor: "#DD6B55",
					confirmButtonText: "Yes, delete it!",
					cancelButtonText: "No, cancel!",
					closeOnConfirm: false,
					closeOnCancel: false
				},
					 
					 function(isConfirm){
					if (isConfirm) {
						

					$.ajax({    
					type: 'POST',
					url: 'deletecat.php',
					data: 'delete='+pid    
					})
				
						 parent.fadeOut('slow');
						
						
						swal("Deleted!",
							 "Record delete successfully.",
							 "success");
					} else {
						swal("Cancelled",
							 "Delete cancelled :)",
							 "error");
					} });
				
				});
		});

Open in new window


here is the code that is displaying the records:

function display_categories($link) {
$stmt = $link->prepare("SELECT `cat_id`, `category_name` FROM `categories`");
$stmt->execute();
$result = $stmt->get_result();
$numRows = $result->num_rows;
if($numRows > 0) {
	while ($row = $result->fetch_assoc()) {
		
		
		$categoryName = htmlentities($row['category_name'], ENT_QUOTES, "UTF-8");
		$catId = htmlentities($row['cat_id'], ENT_QUOTES, "UTF-8");
		
		$showcat = <<<EOD
		
		<tr>
		<td>{$categoryName}</td>
		<td></td>
		<td></td>
		<td></td>
		<td></td>
		<td>
		<a data-id="" href="javascript:void(0)"> <i class="pe-7s-tools"></i> </a>
		<a data-id="{$catId}" href="javascript:void(0)"> <i class="pe-7s-trash"></i> </a>
		</td>
		</tr>
		
EOD;
echo $showcat;
		}
	}
}

Open in new window


And here is the delete deletecat.php

if ($_REQUEST['delete']) {

$pid = $_REQUEST['delete'];
$stmt = $link->prepare("DELETE FROM `categories` WHERE `cat_id` = ? LIMIT 1");
$stmt->bind_param("i", $pid);
$stmt->execute();
$stmt->close();
	
}

Open in new window

0
Comment
Question by:Black Sulfur
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 9
  • 4
  • 3
  • +2
21 Comments
 
LVL 2

Expert Comment

by:Andrew Derse
ID: 41893550
In your PHP file change $_REQUEST to $_POST and see if that does the trick.
0
 
LVL 2

Expert Comment

by:Andrew Derse
ID: 41893553
Also, whenever I load php using jquery it loses the global db variable, so I reset that right away in my php file.  Maybe you php file being called can't access your database. Test that and send back an error message to console.log to verify you are actually connecting to the database.
0
 
LVL 1

Author Comment

by:Black Sulfur
ID: 41893571
Thanks Andrew,

I changed to POST like this but it didn't do anything:

if ($_POST['delete']) {

$pid = $_POST['delete'];
$stmt = $link->prepare("DELETE FROM `voucher_categories` WHERE `cat_id` = ? LIMIT 1");
$stmt->bind_param("i", $pid);
$stmt->execute();
$stmt->close();
	
}

Open in new window


I was following a tutorial to do this but it was using bootbox and not sweet alert. I prefer sweet alert so I am trying to modify the code to work with sweet alert. I am new to ajax/jQuery so not sure if it is necessary but in the tutorial code there is no form. I thought if I am going to use POSt in the ajax there should be a form somewhere i.e.: <form method="post"> but I could be mistaken of course.

Secondly, I don't know how to do this:

Maybe you php file being called can't access your database. Test that and send back an error message to console.log
0
Secure Your WordPress Site: 5 Essential Approaches

WordPress is the web's most popular CMS, but its dominance also makes it a target for attackers. Our eBook will show you how to:

Prevent costly exploits of core and plugin vulnerabilities
Repel automated attacks
Lock down your dashboard, secure your code, and protect your users

 
LVL 110

Expert Comment

by:Ray Paseur
ID: 41893593
Almost all of the PHP functions return a value of some sort.  To test for errors or omissions, it's useful to (1) go to PHP.net and look up what the expected return values might be; (2) look for examples of error checking, because things like database extensions do not tell you about errors; (3) implement the tests for the return values and the error checks.

By way of example, consider this code block...
if ($_POST['delete']) {

$pid = $_POST['delete'];
$stmt = $link->prepare("DELETE FROM `voucher_categories` WHERE `cat_id` = ? LIMIT 1");
$stmt->bind_param("i", $pid);
$stmt->execute();
$stmt->close();
	
}

Open in new window

In that code, $link is undefined.  But the use of an undefined variable may not even raise a Notice-level message unless you deliberately tell PHP to report those things!  Another problem with this script arises if you call it from an AJAX request.  It has no direct browser output, so you can't look for a message on the screen.

You might try something like this to see if it will help you find the errors, if any.  Run the script and look on your server for the error_log file.
<?php
error_reporting(E_ALL);
ini_set('error_log', 'error_log');
ini_set('log_errors', TRUE);
if ($_POST['delete']) {

$pid = $_POST['delete'];
$stmt = $link->prepare("DELETE FROM `voucher_categories` WHERE `cat_id` = ? LIMIT 1");
$stmt->bind_param("i", $pid);
$stmt->execute();
$stmt->close();
	
}

Open in new window

0
 
LVL 2

Expert Comment

by:Andrew Derse
ID: 41893596
Ok, I think I understand this a little bit more for you. How are you currently accessing your database?
You need a connection string in your php file that will allow you to talk with your database...like this:

<?php
$servername = "localhost";
$username = "username";
$password = "password";
$db = 'mydatabase';

// Create connection
$conn = mysqli_connect($servername, $username, $password, $db);

// Check connection
if (!$conn) {
    die("Connection failed: " . mysqli_connect_error());
}
echo "Connected successfully";
?>

Open in new window


Just put that in your php file, but change the parameters to match your database...otherwise it will fail.
0
 
LVL 2

Expert Comment

by:Andrew Derse
ID: 41893606
And you can trust just about anything Ray says...he's really good. He will steer you in the right direction.
0
 
LVL 110

Expert Comment

by:Ray Paseur
ID: 41893607
Also, this is a little beside the point, but worth knowing.  NEVER make a change to your database on the basis of information in $_REQUEST.  $_REQUEST contains both GET and POST request values.  In other words, I can type a URL with the POST request variables like ?delete=1 and cause a change in your database.  You don't want that.  And here's another thing that can happen.  I can put up a web page with hundreds of links to your delete script, each with ?delete=nnn, where each of nnn is one of the collection of database keys.  Then I can feed the page to any search engine, and the search engine will "spider" follow all of the links.  Poof, your database is destroyed.  I won't actually do this, of course, but it makes no sense to invite mischief when you can just use POST in the way it was intended.
1
 
LVL 82

Accepted Solution

by:
leakim971 earned 300 total points
ID: 41893609
also add a bit of code to handle asynchronous event like success and error of your ajax call :
$(document).ready(function () {

	$(".pe-7s-trash").click(function(e) {
		e.preventDefault();
		var pid = $(this).attr('data-id');
		var parent = $(this).parent("td").parent("tr");

		swal({
			title: "Are you sure?",
			text: "You are about to delete this record!",
			type: "warning",
			showCancelButton: true,
			confirmButtonColor: "#DD6B55",
			confirmButtonText: "Yes, delete it!",
			cancelButtonText: "No, cancel!",
			closeOnConfirm: false,
			closeOnCancel: false
		}, function(isConfirm){
			if (isConfirm) {
				parent.fadeOut('slow');
				$.ajax({
					type: 'POST',
					url: 'deletecat.php',
					data: 'delete='+pid,
				}).done(function() {
	    			alert( "success" );
					swal("Deleted!", "Record delete successfully.", "success");
	  			}).fail(function() {
				    alert( "error" );
					swal("Error!", "Record NOT deleted!!!!!!!!!!!", "success");
	  			});
			}
			else {
				swal("Cancelled", "Delete cancelled :)", "error");
			} 
		});
	});

});

Open in new window

1
 
LVL 57

Assisted Solution

by:Julian Hansen
Julian Hansen earned 200 total points
ID: 41893679
Here is a working sample that does the delete and the fade out using a sweet alert confirm.
HTML (The table is rendered dynamically using JavaScript);
    <table class="table" id="data">
    </table>

Open in new window

jQuery
<script>
$(function() {
  renderTable();
  $('.pe-7s-trash').click(function() {
    var tr = $(this).closest('tr');
    var id = $(this).data('id');
    swal({
      title: "Are you sure?",
      text: "You are about to delete this record!",
      type: "warning",
      showCancelButton: true,
      confirmButtonColor: "#DD6B55",
      confirmButtonText: "Yes, delete it!",
      cancelButtonText: "No, cancel!",
      closeOnConfirm: true,
      closeOnCancel: true
    },function(isConfirm) {
      if (isConfirm) {
        $.ajax({
          url: 'reflect.php',
          data: {delete: id},
          type: 'POST'
        }).done(function(resp) {
          tr.fadeOut('slow', function() {
            alert('all gone');
          });
        });
      }
    });
  });
});

function renderTable() 
{
  for(var i = 0; i < 10; i++) {
    var tr = $('<tr/>');
    for(var j = 0; j < 5 ; j++) {
      var td = $('<td/>').html('Row [' + i + '] Col[' + j + ']');
      tr.append(td);
    }
    var del = $('<td/>').append(
          $('<a/>', {class: 'pe-7s-trash'}).html('X').data('id', i * 10 + j));
    tr.append(del);
    $('#data').append(tr);
  }
}
</script>

Open in new window

Working sample here

NB: This is a sample only - I wouldn't code the interface this way as there are many loose ends that are not catered for. This is purely to demonstrate how this could work.
0
 
LVL 1

Author Comment

by:Black Sulfur
ID: 41894032
Apologies, I did not state it but it can always be assumed that if you see $link in my function or code I have a file being required with my database connection where $link looks like:

$link = new mysqli($server_name, $db_username, $db_password, $db_dbname);

Open in new window


So, the code actually looks like:

require '../config/db.php';


if ($_POST['delete']) {

$pid = $_POST['delete'];
$stmt = $link->prepare("DELETE FROM `voucher_categories` WHERE `cat_id` = ? LIMIT 1");
$stmt->bind_param("i", $pid);
$stmt->execute();
$stmt->close();
	
}

Open in new window

0
 
LVL 1

Author Comment

by:Black Sulfur
ID: 41894033
@ Ray,

Regarding this:

NEVER make a change to your database on the basis of information in $_REQUEST.  $_REQUEST contains both GET and POST request values.

Thanks for explaining that. Sadly, it seems that a lot of code I try to learn from on the internet is bad. I keep finding this out as as I go along. It is difficult to find good code to learn from it would seem!
0
 
LVL 1

Author Comment

by:Black Sulfur
ID: 41894036
Run the script and look on your server for the error_log file.

Thanks for that info. I am not running this on a live server but on localhost using MAMP. Where would I find the log file? I checked in the directory I am using for this particular project but can't see it in there.
0
 
LVL 1

Author Comment

by:Black Sulfur
ID: 41894037
Thank you, Julian. I still don't see a form with a method of post. Do I not need one? I thought that if ajax was using POST I would have to have a form but it doesn't seem like I have to?

$.ajax({
type: 'POST',
url: 'deletecat.php',

Open in new window

0
 
LVL 57

Expert Comment

by:Julian Hansen
ID: 41894115
AJAX is a means to communicate with the server - you have to tell the AJAX function what data you are sending - it is therefore independent of a form. If you are sending form data you still have to explicitly gather that form data (using .serialize() or similar) and then give that data to the AJAX function.

So, no a forms presence or absence on the page is not relevant when doing an AJAX call to the server.
0
 
LVL 110

Expert Comment

by:Ray Paseur
ID: 41894129
seems that a lot of code I try to learn from on the internet is bad
Yes, and that is why we have this article:
https://www.experts-exchange.com/articles/11769/And-by-the-way-I-am-New-to-PHP.html
1
 
LVL 1

Author Comment

by:Black Sulfur
ID: 41894590
As usual, I am being too ambitious. If it's okay, I would like to ask the question another way without worrying about the bells and whistles with sweet alert and making rows fade out. I think I just need to understand the ID part first because that is the confusing bit.

So, I want this icon, pe-7s-trash to be clickable. When a user clicks on it the record must just delete, no fancy pop-ups etc.

<a id="{$pid}" href="javascript:void(0)"> <i class="pe-7s-trash"></i> </a>

Open in new window


Can I take the href out now since I wan't be using the pop-up?

<a id="{$pid}" > <i class="pe-7s-trash"></i> </a>

Open in new window


Secondly, in the php displaying the table, should the $pid variable be in the <tr> tag or with the pe-7s-trash, or doesn't it matter?

<tr id='$pid'>
<td>{$categoryName}</td>
<td></td>
<td></td>
<td></td>
<td></td>
<td>
<a> <i class="pe-7s-trash"></i> </a>
</td>
</tr>

Open in new window


If I view the page source I can see that each table row has the correct ID pulling from the database which is great.

I am guessing this has something to do with getting the ID?

var tr = $(this).closest('tr');
var id = $(this).data('id');

Open in new window


I have tried to simplify the ajax and console.log it:

      
$(document).ready(function () {

		$(".pe-7s-trash").click(function(e) {
		e.preventDefault();
		
		var tr = $(this).closest('tr');
		var id = $(this).data('id');

		$.ajax({
			url: "deletecat.php",
			type: "POST",
			data: {
				'delete': id	
			},
			success: function(data){
				console.log(data);
	
			},
		})
		
	})
})

Open in new window


The error I get in console is:

undefined index: delete

in my php delete($link is defined in required file containing DB connection)

if ($_POST['delete']) {

$stmt = $link->prepare("DELETE FROM `voucher_categories` WHERE `cat_id` = ? LIMIT 1");
$stmt->bind_param("i", $_POST['delete']);
$stmt->execute();
$stmt->close();
	
}

Open in new window

0
 
LVL 1

Author Comment

by:Black Sulfur
ID: 41894612
Ahah. I figured out what I was doing wrong.

The id actually needs to be assigned to what I am clicking on. So, the icon should actually have the ID.

<a> <i class="pe-7s-trash" id='$pid'></i> </a>

Open in new window


And now this is deleting the record from the database:

	$(document).ready(function(){
			$(".pe-7s-trash").click(function(e) {
			e.preventDefault();

			var id = $(this).attr('id');
			var tr = $(this).closest('tr');
			console.log(id);

			$.ajax({
			url: "deletecat.php", 
			type: "POST",
			data: {delete: id},

			success: function(result) {
			tr.fadeOut('slow', function() {

				})
			}

		})
	})
})

Open in new window


And the row is fading out. :)
1
 
LVL 1

Author Comment

by:Black Sulfur
ID: 41894629
And finally, I got it to work with sweet alert. That felt like a real mission but its working now in case anyone is interested in the code :)

$(document).ready(function() {
	    $(".pe-7s-trash").click(function(e) {
	        e.preventDefault();

	        var id = $(this).attr('id');
	        var tr = $(this).closest('tr');

	        swal({
	                title: "Are you sure?",
	                text: "You will not be able to undo this action.",
	                type: "warning",
	                showCancelButton: true,
	                confirmButtonColor: "#FB404B",
	                confirmButtonText: "Yes, delete it!",
	                cancelButtonText: "Cancel!",
	                closeOnConfirm: false,
	                closeOnCancel: false
	            },
	            function(isConfirm) {
	                if (isConfirm) {
	                    $.ajax({
	                        url: "deletecat.php",
	                        type: "POST",
	                        data: {
	                            delete: id
	                        },

	                        success: function(result) {
	                            tr.fadeOut('slow', function() {
	                                swal("Deleted!", "Category successfully deleted.", "success");
	                            })
	                        }

	                    })

	                } else {
	                    swal("Cancelled", "Maybe later then..", "error");
	                }
	            });

	    })
	})

Open in new window

0
 
LVL 82

Expert Comment

by:leakim971
ID: 41894713
If your issue is now resolved, please close the question. You can accept your own comment(s) as solution.
0
 
LVL 57

Expert Comment

by:Julian Hansen
ID: 41895388
One thing I would change
<a> <i class="pe-7s-trash" id='$pid'></i> </a>

Open in new window

To
<a> <i class="pe-7s-trash" data-id='$pid'></i> </a>

Open in new window

And
var id = $(this).attr('id');

Open in new window

To
var id = $(this).data('id');

Open in new window

Your solution does work but just from an HTML point of view id identifies the element  custom data attributes (data-XXXX) are we what we use to decorate elements with data we need when we do something with the element. For someone reading the code it makes it clearer what you are doing if you use the data-id attribute to store the id you wish to delete.
1
 
LVL 1

Author Comment

by:Black Sulfur
ID: 41895404
Awesome, thanks for the tip!
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction Knockoutjs (Knockout) is a JavaScript framework (Model View ViewModel or MVVM framework).   The main ideology behind Knockout is to control from JavaScript how a page looks whilst creating an engaging user experience in the least …
Introduction This article is intended for those who are new to PHP error handling (https://www.experts-exchange.com/articles/11769/And-by-the-way-I-am-New-to-PHP.html).  It addresses one of the most common problems that plague beginning PHP develop…
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…
The viewer will learn the basics of jQuery, including how to invoke it on a web page. Reference your jQuery libraries: (CODE) Include your new external js/jQuery file: (CODE) Write your first lines of code to setup your site for jQuery.: (CODE)

696 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question