Php mysql delete where in array

mysql DELETE query failing, using implode IN clause

I am currently populating an array with checkboxes, imploding the array variable and using that variable in an IN clause within a DELETE function. var_dump is showing that I am creating a correct query, yet I can’t get any deletion to occur. This is a new approach to an existing problem I have had for a month or so. Here’s what I have.

//Connect to the db $id_array = NULL; // Make the query to display user's uploads $q = "SELECT upload_id, title, genre, length, created, views FROM upload WHERE owner_id =". $_SESSION['user_id'] ." ORDER BY title ASC"; $r = mysqli_query ($dbc, $q); // Run the query if($r) < // If it ran okay, display the records echo ''; // Fetch and print all the records: ?>  '. ''; > echo '
Title
 
Genre
 
Pages
 
Submitted
 
Views
 
' . $row['title'] . '
' . $row['genre'] . '
' . $row['length'] . '
' . $row['created'] . '
' . $row['views'] . '
' //. var_dump($row) //dump row value for testing . ''.'
'; // Close the table ?> 

the above code creates a simple table, each record is shown with a corresponding checkbox, check to delete then click «Delete»

   // End of if ($r) IF. mysqli_close($dbc); // Close the database connection 

function submit_delete() < if(!is_null($_POST['delete']) && !is_null($_POST['checkbox'])) < //Check to see if a delete command has been submitted. //This will dump your checkbox array to your screen if the submit works. //You can manually check to see if you're getting the correct array with values // var_dump($_POST['checkbox']);//Comment this out once it's working. $id_array = $_POST['checkbox']; $id_array = array_map('intval', $id_array); $id_array = array_unique($id_array); $id_array = implode(',',$id_array); //var_dump($id_array); deleteUploads($id_array); >else < echo "Error: submit_delete called without valid checkbox delete.";//var_dump($_POST['checkbox']); >> $delete_success = false; function deleteUploads ($id_array) < require_once(my connection to db); mysqli_free_result($r); if (count($id_array) //var_dump($id_array); 

 $gone = "DELETE FROM `upload` WHERE `upload_id` IN ("."$id_array".")"; var_dump($gone); 

 $goodbye = mysqli_query($dbc, $gone); if ($goodbye) < $delete_success = true; var_dump($delete_success);>else < $delete_success = false;>var_dump($delete_success); 

 //mysqli_close($dbc); if($delete_success == true) < echo 'done'; header('Location: newwriter_profile.php'); >else < echo "Error b/c delete failed"; 

 > > submit_delete(); mysqli_free_result($goodbye); mysqli_close($dbc); ?> 

Источник

MYSQL Delete Where Field is Not Part of Array

I have a table "groupdentlink" where I want to delete all the rows that weren't checked in a form. In essence I want to perform a query like:

DELETE * FROM groupdentlink WHERE group_id = 'a' AND dentist_id IS NOT IN ARRAY 'b' 

I think I could set a variable with a foreach loop and then keep adding the array values to it so I end up with:

DELETE * FROM groupdentlink WHERE group_id = 'a' AND dentist_id != 'D1' AND dentist_id != 'D5' AND dentist_id != 'D8' 

7 Answers 7

DELETE FROM groupdentlink WHERE group_id = 'a' AND dentist_id NOT IN ('D1','D5','D8') 

Thanks for the help. I know I'm close now, but getting syntax error with this query: DELETE * FROM groupdentlink WHERE group_id = 'a' AND dentist_id NOT IN ('UTG1','UTG10','UTG100')

If you want to execute this query from a Zend Framework driven application please take in consideration the followings :

$where = sprintf('dentist_id NOT IN ("%s")', implode('", "',array_map('mysql_escape_string', $array))); $this->sqlMapper->delete($where); 

If you try . operator for concatenation purposes the query will result in a fatal error because of the quotes. So from my experience using htmlspecialchars or htmlencode along with . operator will only consume your time and patience. The use of sprintf is elegant, helps you keep your code clean.

And I think these observations apply to any application that makes use of php objects.

Источник

DELETE. Удаление записей в таблице базы данных MySQL

Если вам необходимо удалить одну, несколько или все записи в таблице базы данных, то с этим вам поможет команда DELETE .

Синтаксис запроса на удаление записи.

Будьте предельно внимательны при выполнении запросов на удаление записей! Если вы не укажите команду WHERE и последующее условие, то будут удалены все записи в таблице.

Удаление нескольких записей таблицы

Для примера удалим несколько записей из таблицы books, которая хранится в базе данных Bookstore.

Оповестим сервер MySQL о базе данных, для которой будут выполнятся запросы.

Далее выведем записи таблицы books с идентификаторами с 1 по 5.

mysql> SELECT id, title, author, price, discount FROM books WHERE id BETWEEN 1 AND 5;
+----+-----------------------+------------------------------+--------+----------+
| id | title | author | price | discount |
+----+-----------------------+------------------------------+--------+----------+
| 1 | Капитанская дочка | А.С.Пушкин | 151.20 | 0 |
| 2 | Мертвые души | Н.В.Гоголь | 141.00 | 0 |
| 3 | Анна Каренина | Л.Н.Толстой | 135.00 | 20 |
| 4 | Бесы | Ф.М.Достоевский | 122.00 | 0 |
| 5 | Нос | Н.В.Гоголь | 105.00 | 0 |
+----+-----------------------+------------------------------+--------+----------+
5 rows in set (0.00 sec)

Допустим необходимо удалить все записи с книгами за авторством Н.В.Гоголя. Запрос на удаление и его результат будет выглядеть следующим образом.

mysql> DELETE FROM books WHERE author= 'Н.В.Гоголь' ;
Query OK, 2 rows affected (0.00 sec)

Удаление всех записей таблицы

Если вам нужно очистить всю таблицу от имеющихся в ней данных, то просто выполните команду DELETE без каких либо условий.

Следующая команда удалит все записи в таблице books.

Источник

how to delete all rows using IN OPERATOR in mysql php

it only deletes one email id and returns true . how can i make it run in a way it deletes all the emails . below is the query constructed using the above code.

DELETE FROM newsletter where email IN ("adfadsf@gmail.com "," asdfasfasf@gmail.com "," kjhkhsd@assdfsdf.sdfsf "," shit@gshit.com "," someother@gmail.com")

4 Answers 4

$condition = sprintf('IN ("%s")',implode(' "," ',$_POST['ids'])); 

$condition = sprintf('IN ("%s")',implode('", "',$_POST['ids'])); 

IN operator matches contents of the field with the values exactly. Spaces at the beginnings and ends of values might have cause your problems.

The query is valid however you are adding spaces before/after the email.

" asdfasfasf@gmail.com " does not match "asdfasfasf@gmail.com"

This code would be vulnerable to SQL injection attacks, i could make post something like

"); delete from newsletter; delete from newsletter where email in (" 

the " are part of my post string, this would wipe your newsletter table.

However that aside the key issue with your code is potentially the spaces (do your strings include spaces in the database?) and the use of double quotes. I'm not 100% certain but I'm sure " can be used as a field indicator rather than a string so you probably want single quotes.

$emails = $_POST['ids']; $sql = "DELETE FROM newsletter where email "; $condition = sprintf("IN ('%s')",implode("','",$_POST['ids'])); $sql = $sql.$condition; include '../includes/db.php'; $r = mysql_query($sql); echo $sql; 

DELETE FROM newsletter where email IN ('adfadsf@gmail.com','asdfasfasf@gmail.com','kjhkhsd@assdfsdf.sdfsf','shit@gshit.com','someother@gmail.com') 

Источник

UPDATE & DELETE multiple rows with an array for the id

I wan't to check for challenges a user's team posted that have not been excepted after 1 day, then auto refund the user's team back there credits, and then also delete all the challenges. So far here is my code.

//Delete all matches not accepted after 1 day $arrayin = array(); $autorefund = mysql_query("SELECT * FROM `challenges` WHERE `a` = " . $team['id'] . " " . "AND `accepted` = 0 AND `completed` = 0 AND `chtype` = 1 AND (`expires` < " . ((int) time()) . ")"); if (mysql_num_rows($autorefund) >0) < while ($autorefund = mysql_fetch_assoc($autorefund)) < $arrayin[] = $autorefund['id']; mysql_query("UPDATE `teams` SET `balance` = `balance` + " . $autorefund['credits'] . " " . "WHERE `id` IN (" . mysql_real_escape_string(implode(',', $arrayin)) . ")"); mysql_query("DELETE FROM `challenges` WHERE `a` IN " . "(" . mysql_real_escape_string(implode(',', $arrayin)) . ") " . "AND `accepted` = 0 AND `completed` = 0 AND `chtype` = 1 " . "AND (`expires` < " . ((int) time()) . ")"); >> 

4 Answers 4

You need to post the MySQL updated, delete conditions out of the loop.

You are already getting the ids in the loop.

So, the final code should be:

 //Delete all matches not accepted after 1 day $arrayin = array(); $autorefund = mysql_query("SELECT * FROM `challenges` WHERE `a` = " . $team['id'] . " AND `accepted` = 0 AND `completed` = 0 AND `chtype` = 1 AND (`expires` < ".((int)time()).")"); if (mysql_num_rows($autorefund) >0) < while ($autorefund = mysql_fetch_assoc($autorefund)) < $arrayin[] = $autorefund['id']; >> mysql_query("UPDATE `teams` SET `balance` = `balance` + " . $autorefund['credits'] . " WHERE `id` IN (" . mysql_real_escape_string(implode(',', $arrayin)) . ")"); mysql_query("DELETE FROM `challenges` WHERE `a` IN (" . mysql_real_escape_string(implode(',', $arrayin)) . ") AND `accepted` = 0 AND `completed` = 0 AND `chtype` = 1 AND (`expires` < ".((int)time()).")"); 

Don't use mysql_ functions as they are deprecated and will be removed in future versions of PHP.

Источник