Grow your CSS skills. Land your dream job.

Mysql – find exactly what has matched across several fields

  • # July 2, 2013 at 2:57 pm

    I have a query that searches across multiple tables and fields for whatever someone has searched for.

    SELECT 	SQL_CALC_FOUND_ROWS
    user.username,
    user.fname,
    user.lname,
    user.biopic,
    work.company,
    work.position,
    categories.title AS category_title
    FROM user
    LEFT JOIN work
    ON work.user_id = user.id
    LEFT JOIN user_categories
    ON user_categories.user_id = user.id
    LEFT JOIN categories
    ON categories.id = user_categories.category_id
    WHERE fname LIKE "%er%" OR
    lname LIKE "%er%" OR
    username LIKE "%er%" OR
    company LIKE "%er%" OR
    position LIKE "%er%" OR
    categories.title LIKE "%er%"
    GROUP BY
    username
    ORDER BY
    CASE WHEN fname LIKE "%er%"
    THEN 0 ELSE 0 END
    + CASE WHEN lname LIKE "%er%"
    THEN 1 ELSE 0 END
    + CASE WHEN username LIKE "%er%"
    THEN 2 ELSE 0 END
    + CASE WHEN category_title LIKE "%er%"
    THEN 3 ELSE 0 END
    + CASE WHEN company LIKE "%er%"
    THEN 4 ELSE 0 END
    + CASE WHEN position LIKE "%er%"
    THEN 5 ELSE 0 END
    ASC,
    lname ASC,
    fname ASC,
    category_title ASC,
    position ASC,
    company ASC
    LIMIT 10
    OFFSET 0;

    Is there any efficient way to find exactly what has matched? Fname, lname, company, etc??

Viewing 1 post (of 1 total)

You must be logged in to reply to this topic.

*May or may not contain any actual "CSS" or "Tricks".