0

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?

3
  • Test your query in commandline or orther client with interactive response before putting it to php, that way you know where the error is.
    – jkavalik
    Nov 4, 2015 at 7:31
  • It is legal syntax, so it is a logic error. So hooray for me, now I how to be logical with it. But I am still tweaking it, every little bit. Nov 4, 2015 at 7:39
  • Then it becomes a question about php and your code if the AJAX call is giving you some error. But about the query - you only check the stock_ticker in the WHERE clause so you do not get any rows matching only on stock_simpleName.
    – jkavalik
    Nov 4, 2015 at 7:47

2 Answers 2

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. `

4
  • Why do you need the GROUP BY? Nov 4, 2015 at 7:48
  • You can add a WHERE stock_ticker LIKE 'MO%' OR stock_simpleName LIKE 'MO%', if you want only those results. Nov 4, 2015 at 7:49
  • I want the bias though. I want it to look for the ticker first then the simpleName. Nov 4, 2015 at 7:53
  • I meant ... and keep the ORDER BY CASE ... But really, the GROUP BY is probably useless, if not harmful. Do you have rows with same exact stock ticker and simpleName? Nov 4, 2015 at 7:54
0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.