从数据库中搜索关键字

Question

function _searchProductByName($name = "") {
    $query = $this->db
            ->select("S.*,CC.id as catId, CC.name as CatName")
            ->from("categories as S")
            ->join("categories as C",'C.id=S.parentid','LEFT')
            ->join("categories as CC",'CC.id=C.parentid','LEFT')
            ->where("S.order", 2)
            ->like("S.name", $name, 'both')
            ->order_by("S.name", 'asc')
            //->order_by('locate('.$name.', S.name) asc, S.name asc')
            ->get();

    return $query && $query->num_rows() ? $query->result() : [];
}

when my keywords is lap it gives results:

{
"status": true,
"response": [
    {
        "id": "834",
        "parentid": "46",
        "name": "Collapsible Wardrobes",
        "order": "2",
        "hasChild": "0",
        "icon": null,
        "created": "2018-01-15 15:01:45",
        "catId": "5",
        "CatName": "Home Furniture, Decor , Pets"
    },
    {
        "id": "936",
        "parentid": "51",
        "name": "Laptop Desks",
        "order": "2",
        "hasChild": "0",
        "icon": null,
        "created": "2018-01-15 15:01:45",
        "catId": "5",
        "CatName": "Home Furniture, Decor , Pets"
    },
    {
        "id": "936",
        "parentid": "51",
        "name": "mobile latptop",
        "order": "2",
        "hasChild": "0",
        "icon": null,
        "created": "2018-01-15 15:01:45",
        "catId": "5",
        "CatName": "Home Furniture, Decor , Pets"
    }
  ]
}

when i use after in place or both it gives only Laptop Desks result but i need when i search for lap it gives me result.

Laptop Desk and mobile laptops

the first data is Collapsible Wardrobes which contains lap but it is in between collapsible which i don't need how do i get this?

Answer 1

If i understand you correctly (according to the comments) a possible solution could be to look for a word following with a space or at the beginning such as

$query = $this->db
        ->select("S.*,CC.id as catId, CC.name as CatName")
        ->from("categories as S")
        ->join("categories as C",'C.id=S.parentid','LEFT')
        ->join("categories as CC",'CC.id=C.parentid','LEFT')
        ->where("S.order", 2)
        ->group_start()
            ->like('S.name', $name, 'after')
            ->or_like('S.name', ' '.$name, 'both')
        ->group_end()
        ->order_by("S.name", 'asc')
        //->order_by('locate('.$name.', S.name) asc, S.name asc')
        ->get();

this should output

SELECT `S`.*, `CC`.`id` AS `catId`, `CC`.`name` AS `CatName`
FROM `categories` AS `S`
LEFT JOIN `categories` AS `C` ON `C`.`id`=`S`.`parentid`
LEFT JOIN `categories` AS `CC` ON `CC`.`id`=`C`.`parentid`
WHERE `S`.`order` = 2 AND (`S`.`name` LIKE 'lap%' ESCAPE '!' OR `S`.`name` LIKE '% lap%' ESCAPE '!')
ORDER BY `S`.`name` ASC