Solved

2 delete statements in one query, one of which has multiple rows to delete

Posted on 2016-11-27
9
59 Views
Last Modified: 2016-11-28
I had this question after viewing Insert multiple select data into single database row, not multiple.

Currently this code is only deleting the multiple records from the second query and not the single record from the first query. I have put the id in an array like this:

<td><input type="submit" name="action" data-action="delete" data-category-name="$spName" data-sp-id=["$spId"] class="delete btn btn-xs btn-fill btn-danger" value="Delete"></td>

Open in new window


$stmt = $link->prepare("DELETE FROM `service_providers` WHERE `service_provider_id` = ? LIMIT 1");
		$stmt->bind_param("i", $_POST['spId']);
		$stmt->execute();
		$stmt->close();
	
		foreach($_POST['spId'] as $sp_cat_id) {
		$stmt = $link->prepare("DELETE FROM `sp_cats` WHERE `sp_id` = ?");
		$stmt->bind_param("i", $sp_cat_id);
		$stmt->execute();
		$stmt->close();

Open in new window


And the ajax :

	$(document).ready(function() {
$('.delete').on('click', function (e) {
					e.preventDefault();
					var $spId = $(this).data('sp-id');
	console.log($spId);
					swal({
						title: 'Are you sure?',
						text: "You won't be able to undo this!",
						type: 'warning',
						showCancelButton: true,
						confirmButtonColor: '#FB404B',
						cancelButtonColor: '#d33',
						confirmButtonText: 'Yes, delete it!'
					}, function (isConfirm) {
						if (isConfirm) {
							$.ajax({
								type: 'post',
								url: 'functions/sp-handler.php',
								data: {spId: $spId},
								success: function (result) {
//									window.location.href = 'service-providers.php'; 
									alert(result);
								},

							});
						}else{
							console.log('cancelled');
						}
					});
				});
	});

Open in new window

0
Comment
Question by:Black Sulfur
9 Comments
 
LVL 31

Expert Comment

by:Marco Gasi
ID: 41903113
Hi Black. What is the actual value of the variable $spId?
0
 
LVL 109

Expert Comment

by:Ray Paseur
ID: 41903124
Just a thought... You might not want to name your JavaScript variables starting with dollar sign.

My guess is that $_POST['spId'] is an array.  You might want to pass this value from the JavaScript front end to the PHP back end in the form of a JSON string.  The overall thinking here is that you are asking the back end script to do two different things, so it seems reasonable to pass it two different variables, instead of trying to shoehorn them both into $_POST['spId'].  Here's an example of a JSON string, showing how to decode it and use the contents to create delete queries.
<?php // demo/temp_black_sulfur.php
/**
 * https://www.experts-exchange.com/questions/28985663/2-delete-statements-in-one-query-one-of-which-has-multiple-rows-to-delete.html
 */
error_reporting(E_ALL);
echo '<pre>';


// A SAMPLE OF WHAT MIGHT BE SENT FROM THE JAVASCRIPT FRONT END
$json = <<<EOJ
{ "sp_id": 3
, "sp_cat_id":
  [ 3, 5, 7 ]
}
EOJ;


// SHOW HOW TO CREATE THE DELETE QUERIES
$obj = json_decode($json);

$dq1 = "DELETE FROM myTable WHERE id = $obj->sp_id LIMIT 1";
echo PHP_EOL . $dq1;

foreach ($obj->sp_cat_id as $id)
{
    $dq2 = "DELETE FROM myOtherTable WHERE id = $id LIMIT 1";
    echo PHP_EOL . $dq2;
}

Open in new window

1
 
LVL 82

Expert Comment

by:leakim971
ID: 41903125
why using ajax to do that on success (should be everytime) :
    window.location.href = 'service-providers.php'; 
    alert(result);

Open in new window

0
Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

 

Author Comment

by:Black Sulfur
ID: 41903237
@Ray,

The dollar sign javascript variables was actually a solution from Marco :) Is there a particular reason you suggest not using the dollar signs? In my limited knowledge I can only think that it is because it might get confused with php as dollar signs are also used?
0
 

Author Comment

by:Black Sulfur
ID: 41903238
Hi Marco,

Not sure what you mean? $spId is the unique id number from the database for each record.
0
 

Author Comment

by:Black Sulfur
ID: 41903242
If I look at console after console logging $spId, it shows:

Array[1]
0: "2"
length: 1

It seems like "2" is the id number from the database. I am not sure if the other info means suggests why the main record isn't deleting?
0
 

Author Comment

by:Black Sulfur
ID: 41903248
@ Ray,

I think json and creating objects are a bit too advanced for me at this stage. But I intend on getting there one day!
0
 

Author Comment

by:Black Sulfur
ID: 41903254
I got it to work. If anyone could offer some explanation as to why it would be helpful. I simply changed $_POST['spId'] to the variable I set in the foreach loop $sp_cat_id and it now works. So, in the second $stmt, I changed $_POST to the set variable, that's all.

foreach($_POST['spId'] as $sp_cat_id) {
		$stmt = $link->prepare("DELETE FROM `sp_cats` WHERE `sp_id` = ?");
		$stmt->bind_param("i", $sp_cat_id);
		$stmt->execute();
		$stmt->close();
		
		}

		$stmt = $link->prepare("DELETE FROM `service_providers` WHERE `service_provider_id` = ? LIMIT 1");
		$stmt->bind_param("i", $sp_cat_id);
		$stmt->execute();
		$stmt->close();

Open in new window

0
 
LVL 109

Accepted Solution

by:
Ray Paseur earned 500 total points
ID: 41903334
The reason it works now is because $_POST['spId'] was always an array.  The new code uses foreach() which is an iterator, used to access elements of the array.  Learn more about PHP arrays here:
http://php.net/manual/en/book.array.php

If you use a PHP array as if it were a string, PHP unhelpfully substitutes the word "Array."  The type-juggling makes no sense here, but PHP has "always done it this way" and it's too late to fix the problem (there should probably be a Warning message).  You can see this bizarre phenomenon in action by trying to echo an array.

Why not use dollar signs on variable names in JavaScript?  For the same reason that we would try to use meaningful variable and function (method) names in our code.  The strongest semantic evidence of intent you can attach to your programming is through naming.  You're currently involved in programming experiments that intermix JavaScript and PHP.  We know that PHP variable names start with dollar signs.  We know that jQuery has an alias of $.  Probably best to remove confusion wherever you can.

Going forward, questions like this are data-dependent.  Success or failure of the code will occur depending on what the data contains.  In this case, the data you want to see is the data that is getting sent from the client to the server.  So before you encounter unexpected results in the server-side script, take a moment to visualize the information that is being sent from the client in the HTTP request.  This script will put the request variables in a place where you can find them and see what they contain.
<?php // demo/request_logger.php
/**
 * Log the request variables
 */
error_reporting(E_ALL);

// START A BUFFER TO CAPTURE THE BROWSER OUTPUT
ob_start();

// SHOW THE COOKIE(S)
echo '$_COOKIE: ';
var_dump($_COOKIE);
echo PHP_EOL;

// SHOW THE GET REQUEST
echo '$_GET: ';
var_dump($_GET);
echo PHP_EOL;

// SHOW WHAT WAS RECEIVED IN POST
echo '$_POST: ';
var_dump($_POST);
echo PHP_EOL;

// SHOW WHAT WAS RECEIVED IN FILES
echo '$_FILES: ';
var_dump($_FILES);
echo PHP_EOL;

// CAPTURE THE BUFFER AND SAVE THE LOG
$request_data = ob_get_clean();
error_log($request_data);

Open in new window

0

Featured Post

Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

Question has a verified solution.

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

Suggested Solutions

Things That Drive Us Nuts Have you noticed the use of the reCaptcha feature at EE and other web sites?  It wants you to read and retype something that looks like this.Insanity!  It's not EE's fault - that's just the way reCaptcha works.  But it is …
Password hashing is better than message digests or encryption, and you should be using it instead of message digests or encryption.  Find out why and how in this article, which supplements the original article on PHP Client Registration, Login, Logo…
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 …

777 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