Solved

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

Posted on 2016-11-27
9
63 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
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

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.

 

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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

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

Suggested Solutions

Part of the Global Positioning System A geocode (https://developers.google.com/maps/documentation/geocoding/) is the major subset of a GPS coordinate (http://en.wikipedia.org/wiki/Global_Positioning_System), the other parts being the altitude and t…
The Confluence of Individual Knowledge and the Collective Intelligence At this writing (summer 2013) the term API (http://dictionary.reference.com/browse/API?s=t) has made its way into the popular lexicon of the English language.  A few years ago, …
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.

840 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