How to remove single quotes in prepare statement?

Question

My query is like this :

$group_id = $session['group_id'];

$sql = "SELECT *
        FROM notification 
        WHERE group_id IN(?)";

$result = $this->db->query($sql, array($group_id))->result_array();

When I add : echo $this->db->last_query();die();, the result is like this :

SELECT * FROM notification WHERE group_id IN('1,13,2')

I want remove single quotes in order to the result is like this :

SELECT * FROM notification WHERE group_id IN(1,13,2)

How to remove single quotes in prepare statement?

It would be easier to help if we know what you were actually passing in `$group_id` — RiggsFolly, Jul 04 '16 at 17:10
@RiggsFolly, It's string. If I do `echo $group_id;die();`, the result : `1,13,2` — moses toh, Jul 04 '16 at 21:21

score 8 · Answer 1 · answered Dec 10 '19 at 23:59

8

Changing %s placeholder to %1s remove Automattic single quote in prepare statement.

Example:

global $wpdb
$sql = $wpdb->prepare( "SELECT * FROM {$wpdb->prefix}posts WHERE post_status='wc-completed' AND ID IN(%1s)", '1,2,3' );

Reference links: Thanks

answered Dec 10 '19 at 23:59

Sushil Adhikari

720
6
12

1

After 2 years of trying to get around of this with any possible workaround, you changed my life with a 1, thank you. – silvered.dragon Dec 03 '20 at 23:18

score 1 · Answer 2 · answered Nov 15 '18 at 15:15

You either need to dynamically add in as many ? as you have values in the array...

Or stop using a prepared query and do something like the following:

$group_id = $session['group_id'];

$sql = "SELECT *
    FROM notification 
    WHERE group_id IN (".implode($group_id,",").")";

If the data hasn't come from a user you don't necessarily need to use a prepared query to make sure the data is safe. But if necessary you could do an is_numeric() check before the query to see if the data is valid.

score 0 · Answer 3 · answered Jul 05 '16 at 07:06

0

I prefer you can use where_in command as below:-

$this->db->select('*');
$this->db->where_in('group_id',$group_id);
$this->db->get('notification');

answered Jul 05 '16 at 07:06

manu joseph

77
5

score 0 · Answer 4 · answered Jul 12 '16 at 03:08

0

All you have to do is make an array of group ids. Try as following

$group_id = explode(',',$session['group_id']);
$this->db->select('*');
$this->db->where_in('group_id',$group_id);
$this->db->get('notification');

it will work

answered Jul 12 '16 at 03:08

Rejoanul Alam

5,365
3
36
67

score 0 · Answer 5 · answered Jul 12 '16 at 04:34

You can this as below:

$session['group_id'] is probably a string. so you can convert that string into an array.

$group_id = explode(",", $session['group_id']);

Now $group_id is already an array. So, in below statement replace array($group_id) with just '$group_id':

$result = $this->db->query($sql, array($group_id))->result_array();

so whole code will be like:

$group_id = explode(",", $session['group_id']);

$sql = "SELECT *
        FROM notification 
        WHERE group_id IN(?)";

$result = $this->db->query($sql, array($group_id))->result_array();

How to remove single quotes in prepare statement?

5 Answers5

Linked