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?
stock_ticker
in the WHERE clause so you do not get any rows matching only onstock_simpleName
.