CODEWITHSUNDEEP
phpmysqljoinsearchsql-like
SReca
SReca

Reputation: 683

How can I improve my LIKE with JOIN search in mysql?

I currently have a search that looks like this:

SELECT s.ID 
    FROM Shoe s
        INNER JOIN Account a
            ON s.UserID = a.ID
        WHERE s.Publish='1' AND
            (s.Brand LIKE '%$Search%'
             OR s.Name LIKE '%$Search%'
             OR s.PrimaryColor LIKE '%$Search%'
             OR a.User LIKE '%$Search%') 
        ORDER BY s.ID DESC
LIMIT $offset, $rowsPerPage"

This works fine when I do a search such as "Blue" or "Nikes", but if I do a search such as "Blue Nikes" nothing returns. Should I use FULLTEXT? How can I improve this? I want to be able to search all columns that may relate to the search variable.

Upvotes: 7

Views: 241

Answers (2)

SReca
SReca

Reputation: 683

So after messing around and testing different things, I came up with this:

"FROM Shoe AS s
    LEFT JOIN Accounts AS a ON s.UserID = a.ID
    WHERE (MATCH (s.Brand, s.Name, s.PrimaryColor AGAINST('$Search' IN BOOLEAN MODE)
        OR MATCH (a.User) AGAINST('$Search' IN BOOLEAN MODE))
            AND s.Publish='1'
    ORDER BY s.ID DESC"

This seems to fix my issue that I mentioned above, I can now do a search such as "Blue Nike" and all items related to blue & nike will show up. Not sure if this is the most efficient way to go about it, but it does work.

Upvotes: 1

AXM
AXM

Reputation: 836

Instead of LIKE try using SOUNDEX, or both.

Secondly, you may want to optimize the query to maintain capitalization, for example:

(lower(s.Brand) LIKE '%" . strtolower($Search) . "%'
OR lower(s.Name) LIKE '%" . strtolower($Search) . "%'
OR lower(s.PrimaryColor) LIKE '%" . strtolower($Search) . "%'
OR lower(a.User) LIKE '%" . strtolower($Search) . "%')

Upvotes: 0

Related Questions