Order By Case String Not Working

Question

I am trying to get my SQL search to work. What I am looking for is a search result that pulls information based on either a company's name or their stock trading ticker. If one would to type in M, I would expect:

MAT  - Mattel
MFST - Microsoft
MA   - MasterCard
MDLZ - Mondelez
MON  - Monsanto

It queries the "ticker" just fine, but how would I get it to allow both? If I search MO instead of "M", I would expect to see this:

MON  - Monsanto
MDLZ - Mondelez

I want it to give bias towards the "ticker", but also query the name if it is like the search term. Here is my code:

SELECT `stock_id`, `stock_ticker`, `stock_simpleName` 
FROM `stocks` 
WHERE `stock_ticker` = 'M' 
GROUP BY `stock_ticker`, `stock_simpleName` 
ORDER BY CASE  
    WHEN `stock_ticker` LIKE 'M%' THEN 1 
    WHEN `stock_simpleName` LIKE 'M%' THEN 2 
    ELSE 3 
END 
LIMIT 5

I wrote a different query that easily pulled the "ticker" alone, but trying to query them both with bias towards one of the columns like stated above seems to be a bit difficult. My AJAX returns an error which obviously means it's returning a PHP error rather than the data that should be requested.

Is there a tweak for this to get it to work?

Answer 1

So I tweaked the code a bit by testing it in phpMyAdmin, and this is what worked:

SELECT `stock_id`, `stock_ticker`, `stock_simpleName` 
FROM `stocks` 
GROUP BY `stock_ticker`, `stock_simpleName` 
ORDER BY CASE 
    WHEN `stock_ticker` LIKE 'MO%' THEN 1 
    WHEN `stock_simpleName` LIKE 'MO%' THEN 2 
    ELSE 3 
END 
LIMIT 5

I just removed the WHERE Statement entirely. It works exactly like I needed it to. I thought WHERE was always required for any statement that succeeds the FROM table.column part of the query. `

Answer 2

Build a table specifically for this lookup:

CREATE TABLE Lookup (
    name VARCHAR(22) NOT NULL,
    precedence TINYINT NOT NULL,  -- replaces your CASE: 1 for ticker; 2 for simpleName
    stock_id ... NOT NULL,
    PRIMARY KEY (name),  -- for the uniqueness
    INDEX(stock_id)   -- for maintenance
) ENGINE=InnoDB -- to get clustering on name

Then insert 1 or 2 (maybe more?) rows for each stock.

Fetching:

SELECT stock_id
    FROM Lookup
    WHERE name LIKE 'MO%'   -- filled in dynamically
    ORDER BY precedence, name
    LIMIT 5;

This will be faster, avoids ticker:simpleName dups, etc.