How to remove multiple rows from mysql table, using an array of IDs in PHP

Question

I'm having trouble using an array to make an sql query delete multiple rows of data at once. Please ignore that I'm using sql injection for now.

EDIT: A somewhat better explanation - my php does not work at all. I have edited it somewhat, after checking the link Sebastian provided in the comments

NOTE: I'm using AngularJS to post

This is my $data structure, from my controller.js function. devicesToDelete is just a simple array, e.g. ["1", "2", "3"]:

$http.post('./js/removeMultiple.php', { 'devicesToDeleteArray': devicesToDelete })
     .success(function(data) {
          $scope.results = data;
          devicesToDelete = [];
     })
     .error(function(err) {
         $log.error(err);
     })

This is the updated php code im having trouble with. On clicking my deleteMultiple button, only the confirm alert fires, the selected items aren't deleted and the console is clear of errors:

<?php

$data = json_decode(file_get_contents("php://input"));

include('config.php');

$sql = "DELETE FROM devices WHERE devID in ( implode(",", $data) )";

$qry = $conn->query($sql);

$sql = "SELECT * FROM devices";

$qry = $conn->query($sql);

$data = array();

if($qry->num_rows > 0){
    while($row = $qry->fetch_object()){
        $data[] = $row;
    }
}else {
    $data[] = null;
}




$conn->close();

echo json_encode($data);

EDIT 2: I managed to solve the issue on my own. Here is the solution:

I used .join() on my array on the javascript side to have all of the IDs for deletion be separated by commas (e.g. 1, 2, 3):

$http.post('./js/removeMultiple.php', { 'devicesToDeleteArray': devicesToDelete.join(", ") })
                    .success(function(data) {
                        $scope.results = data;
                        devicesToDelete = [];
                    })
                    .error(function(err) {
                        $log.error(err);
                    })

Then I just added what I completely glanced over in my php code which is the name of the array i was passing into it - devicesToDeleteArray

<?php

$data = json_decode(file_get_contents("php://input"));

include('config.php');

$sql = "DELETE FROM devices WHERE devID in ($data->devicesToDeleteArray)";

$qry = $conn->query($sql);

$sql = "SELECT * FROM devices";

$qry = $conn->query($sql);

$data = array();

if($qry->num_rows > 0){
    while($row = $qry->fetch_object()){
        $data[] = $row;
    }
}else {
    $data[] = null;
}

$conn->close();

echo json_encode($data);

You should avoid SQL in loops at any cost. To solve your WHERE IN problem, take a look here: [http://stackoverflow.com/questions/2373562/pdo-with-where-in-queries] — Sebastian, Apr 03 '17 at 06:15
what is trouble of you ? you should use `.then` instead `success` and `error` — Akashii, Apr 03 '17 at 06:16
if this is your working function ... what is your real problem .. ? .. no result ? wrong result ? ..explain better .. — ScaisEdge, Apr 03 '17 at 06:36
`$data` is an object by default. Clarify the `$data` structure. — Deadooshka, Apr 03 '17 at 06:43
can't understand `str_repeat` there. `$data = array_map('intval', $data); $ids = implode(',', $data);` — Deadooshka, Apr 03 '17 at 06:52
You should always use prepared statements when you have variables that are dependant on user input in your SQL command. — user10398534, Apr 21 '20 at 09:01

score 0 · Answer 1 · answered Apr 03 '17 at 07:25

here is php code:

<?php
$ids = $_POST['devicesToDeleteArray'];
if(!is_array($data)) {
   $ids = json_decode($data, true);
}
if(!empty($ids)) {
include 'config.php';

$sql  = "DELETE FROM devices WHERE devID in (implode(",", $ids))";
$qry  = $conn->query($sql);
$sql  = "SELECT * FROM devices";
$qry  = $conn->query($sql);
$data = array();
if ($qry->num_rows > 0) {
    while ($row = $qry->fetch_object()) {
        $data[] = $row;
    }
} else {
    $data[] = null;
}
$conn->close();
echo json_encode($data);

How to remove multiple rows from mysql table, using an array of IDs in PHP

1 Answers1