Improve company productivity with a Business Account.Sign Up

x
?
Solved

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

Posted on 2016-11-27
9
Medium Priority
?
91 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 111

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 83

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: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
LVL 1

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

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

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

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

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 111

Accepted Solution

by:
Ray Paseur earned 2000 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

The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Build an array called $myWeek which will hold the array elements Today, Yesterday and then builds up the rest of the week by the name of the day going back 1 week.   (CODE) (CODE) Then you just need to pass your date to the function. If i…
It’s a season to be thankful, and we’re thankful for users like you who engage on site, solve technology problems, and network with others in the industry. What tech are we most thankful for? Keep reading.
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.

595 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