Solved

delete database record with modal

Posted on 2016-11-18
21
31 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
  • 9
  • 4
  • 3
  • +2
21 Comments
 
LVL 1

Expert Comment

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

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
 

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
 
LVL 108

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 1

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 1

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 108

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 51

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
 

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
Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

 

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
 

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
 

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 51

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 108

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
 

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
 

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
 

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 51

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
 

Author Comment

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

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

I found this questions asking how to do this in many different forums, so I will describe here how to implement a solution using PHP and AJAX. The logical flow for the problem should be: Write an event handler for the first drop down box to get …
These days socially coordinated efforts have turned into a critical requirement for enterprises.
Learn how to match and substitute tagged data using PHP regular expressions. Demonstrated on Windows 7, but also applies to other operating systems. Demonstrated technique applies to PHP (all versions) and Firefox, but very similar techniques will w…
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …

705 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now