Query help - SQL 'IN'

rl2

Hello,

I have a model created 'Knowledge Base Articles' and have chucked loads of dummy articles into the database. I want to show 5 per page, with query parameters for 'tag' and 'page' with pagination buttons Previous and Next.

One of the fields in the database has a comma separated list of strings in it surrounded by square brackets, i.e a JSON string, e.g. ["home", "travel", "bike", "car"]

And then I have a code fragment:

$limit = 5;
$offset = ($_GET['page']-1) * $limit;
$query = array('find' => 'all', 'limit' => $limit, 'offset' => $offset);
$articles = fuel_model('knowledge_base_articles', $query);

-----

I want to be able to check if $_GET['tag'] i.e. car, bike, pet etc.. is IN the JSON string. How do I incorporate an SQL IN statement into the original query?

admin

fuel_model is really just a wrapper function around the knowledge_base_articles_model. You can also do the following:
$CI->load->model('knowledge_base_articles_model'); $CI->load->knowledge_base_articles_model->db()->where_in(...); $results = $CI->load->knowledge_base_articles_model->find_all();

rl2

Thanks. Nearly there, but not quiet.

$offset = ($_GET['page']-1) * $limit;

$CI->load->model('knowledge_base_articles_model');
$CI->load->knowledge_base_articles_model->db()->where_in("tags", array('["' . $_GET['tag'] . '"]'));
$results = $CI->load->knowledge_base_articles_model->find_all();

----

It is finding "home" inside the tags column if the cell contains only ["home"].. but if the cell contains multiple tags e.g. ["breakdown","home"]... "home" is no longer being found.

If I use the below nothing is found at all.

$CI->load->knowledge_base_articles_model->db()->where_in("tags", array($_GET['tag']));

I think I somehow need to return the field contents as a whole and convert the JSON into an array... just not sure at this stage how/what to do.

admin

You are at this point just using query builder to get results. You can use the following to get the actual SQL query being run if you want to test it out:
$CI->load->knowledge_base_articles_model->debug_query();