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

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

LVL 1
Black SulfurAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
Ray PaseurConnect With a Mentor Commented:
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
 
Marco GasiFreelancerCommented:
Hi Black. What is the actual value of the variable $spId?
0
 
Ray PaseurCommented:
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
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

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

Open in new window

0
 
Black SulfurAuthor Commented:
@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
 
Black SulfurAuthor Commented:
Hi Marco,

Not sure what you mean? $spId is the unique id number from the database for each record.
0
 
Black SulfurAuthor Commented:
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
 
Black SulfurAuthor Commented:
@ 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
 
Black SulfurAuthor Commented:
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
All Courses

From novice to tech pro — start learning today.