在不同的MySQL列中搜索不区分大小写

Question

On my website I have a search integrated. I tried full text search, but it is not case insensitive as I would like. Artist and song are in different columns in MySQL.

The code is:

public function search($query, $page_num = 1, $cid=null) {
    if (empty($page_num)) $page_num = 1;
    $items = $this->db->get_results('SELECT added, store.id, categories.id AS category_id, categories.name AS category_name, artist, song, file, size, youtube_url, downloads, hits
        FROM store LEFT JOIN categories ON (store.cid = categories.id)

        WHERE MATCH(artist, song, added) AGAINST ("+' . $this->db->escape($query) . '" IN BOOLEAN MODE)

        '. ($cid!==null ? ' AND store.cid = "'.intval($cid).'"' : '') .'
        ORDER BY added DESC
        LIMIT ' . ($page_num - 1) * SONGS_ON_PAGE . ', ' . SONGS_ON_PAGE, ARRAY_A);

Answer 1

You could return results from a query like this:

SELECT * FROM store
WHERE CONCAT(artist, song) LIKE '%<search>%'