Code Igniter - remove single quotes from where_in

问题:

I have 2 queries:

$genres = $this->db->select('Group_Concat(intGenreId) strDJGenres')
                                ->from('tblDJGenres')
                                ->where('intDJId', $this->session->userdata('non_admin_userid'))
                                ->get()
                                ->row();

            $results = $this->db->select('tblTracks.*, tblGenres.strName as strGenreName')
                                ->from('tblTracks')
                                ->join('tblGenres', 'tblTracks.intGenreId = tblGenres.intGenreId', 'left')
                                ->where_in('tblTracks.intGenreId', $genres->strDJGenres)
                                ->get()
                                ->result();

The first query is returning a string such as

'1,2,3,4,8,6,5,7,45,66'

which I am using in my where_in clause on the second query. The issue is that with this string, it is writing the SQL like:

SELECT `tblTracks`.*, `tblGenres`.`strName` as strGenreName FROM (`tblTracks`) LEFT JOIN `tblGenres` ON `tblTracks`.`intGenreId` = `tblGenres`.`intGenreId` WHERE `tblTracks`.`intGenreId` IN ('1,2,3,4,8,6,5,7,45,66') 

With the quote around it, it is treated as a single value. How can I get the second query to perform how I want it? ie

.... where `tblTracks`.`intGenreId` IN (1,2,3,4,8,6,5,7,45,66) 

回答1:

Multiple values can be passed to the where_in clause as an array.

---

The quotes from the start and end of the string can be removed using trim():

$dj_genres = trim($genres->strDJGenres, "'");

This string can then be converted into an array of strings, to pass to the where_in clause of the second query.

$dj_genres_array = explode(",", $dj_genres);

Or, if you need an array of integers:

$dj_genres_int_array = array_map('intval', $dj_genres_array);

---

You can just add the resultant array into the second query:

// ...    
->where_in('tblTracks.intGenreId', $dj_genres_array)
// ...

Or:

// ...    
->where_in('tblTracks.intGenreId', $dj_genres_int_array)
// ...

回答2:

Answer given by the JLeft :

The quotes from the start and end of the string can be removed using the following function:

$dj_genres = trim($genres->strDJGenres, "'");

This string can then be converted into a array of strings, to pass to the where_in clause of the second query.

$dj_genres_array = explode(",", $dj_genres);

If you need an array on integers, it can be generated as so:

$dj_genres_int_array = array_map('intval', $dj_genres_array);

was working absolutely fine...Thanks JLeft