Solved

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

Posted on 2016-11-27
9
31 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 30

Expert Comment

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

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
 

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
How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

 

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 108

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

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

Suggested Solutions

Popularity Can Be Measured Sometimes we deal with questions of popularity, and we need a way to collect opinions from our clients.  This article shows a simple teaching example of how we might elect a favorite color by letting our clients vote for …
Since pre-biblical times, humans have sought ways to keep secrets, and share the secrets selectively.  This article explores the ways PHP can be used to hide and encrypt information.
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.
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 …

743 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

14 Experts available now in Live!

Get 1:1 Help Now