How can I use my passed-in $events array in a DELETE WHERE IN query in CodeIgniter?
public function remove_events(array $events): void
{
$sql = "DELETE FROM events WHERE event_id IN $events";
$query = $this->db->query($sql);
}
-
I should have clarified. $events is an array with an unknown number of integers.Catfish– Catfish2011-03-01 22:40:11 +00:00Commented Mar 1, 2011 at 22:40
Add a comment
|
5 Answers
if $event is a single value then you must use
$sql = "delete from events where event_id = $events";
Or if it is an array
$sql = "delete from events where event_id in (".implode(',', $events).") ";
1 Comment
Catfish
Thanks for that. Would I need to escape my $events array too?
the following sql syntax should help you to resolve your queries .
delete FROM events WHERE event_id IN ('2','3');
In the above example (2,3) should be a codeigniter array.
Comments
hope that you have the contents in $events as like 1,2,3... . if so no problem, else if you have spaces between the numbers , use like this format.
implode(',',explode(' ',$events));
give it a try and let me know.
Comments
Let Active Record make the job:
$this->db->where_in('id', $events)->delete('events');
All values are escaped automatically producing safer queries.
1 Comment
Carlos Mora
Sorry! Where it says 'where' should say 'where_in'. Editing right now!
Do NOT implode your array into a comma-separated string -- this will provide no protections and will not work as intended if any of the values contain the glue string used in the implosion.
With a raw SQL string, CodeIgniter expects only a ? after IN and it will automatically wrap the placeholder in parentheses (do not add the parentheses yourself because it will break the script/query). The second parameter of the query() method accepts an array of parameters. In your case, the first item in that 2nd parameter needs to be your array. Notice that your array is nested in this containing array.
public function removeEvents(array $eventIds): void
{
$sql = "DELETE FROM events WHERE event_id IN ?";
$query = $this->db->query($sql, [$eventIds]);
}
Alternatively, you can enjoy active record syntax which is more elegant but restricts your ability to directly copy your codebase SQL into your preferred RDBMS for testing/development.
public function removeEvents(array $eventIds): void
{
$this->db->where_in('event_id', $eventIds)->delete('events');
}