How can I structure this nested mysql query?

Question

I have a web form with multiple fields that may or may not be filled out by a user searching for data. The idea is that the user is searching for data between 2 or more parameters. E.G:

stock price 1 and stock price 2
market cap 1 and market cap 2 other
parameter 1 and other parameter 2 ...

Now the user may choose and combination of these (or all of them) to identify what they are looking for. All of my data is stored in one table. Now I can take any one of these sets of parameters and do a

"SELECT * FROM tbl_blah WHERE price BETWEEN stockp1 and stockp2"

which of course works fine.

But things get tricky when I have to nest multiple "WHERE x BETWEEN $1 and $2" type scenarios. I'm not even sure if this is the best approach. I've searched around but I just don't 'get' the statement structure I should be using.

you are trying to figure out what is the best option of searching when the user selects multiple search criteria? — Erick
– Erick, Commented Mar 27, 2016 at 13:28

Sahith Vibudhi · Accepted Answer · 2016-03-27 13:34:13Z

1

you can use multiple between in query.

example :

 SELECT *  FROM Chart_Of_Accounts
  WHERE (price BETWEEN '$stockp1' AND '$stockp2') 
  OR (marketcap BETWEEN '$cap1' AND '$cap2') 
  OR (anotherparam BETWEEN '$param1' AND '$param2');

Hope its helpful!

answered Mar 27, 2016 at 13:34

Sahith Vibudhi

5,2452 gold badges36 silver badges37 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Sahith Vibudhi Over a year ago

you can use AND in place of OR according to what your required output is.

user3515232 Over a year ago

I've just tried this with "AND" instead and that's working fine now. Thanks!

Naruto · Accepted Answer · 2016-03-27 13:36:59Z

0

Dynamically create a query based on what user fills data in.

so, it would be something like this,

$query = "SELECT * FROM tbl_blah WHERE";
if(isset['price'])
$query =  $query + "price BETWEEN stockp1 and stockp2";
if(isset['parameter2'])
$query =  $query + " AND parameter2 BETWEEN stockp3 and stockp4";

Now trick here is to check if u need AND or WHERE.

answered Mar 27, 2016 at 13:36

Naruto

4,3591 gold badge24 silver badges32 bronze badges

Collectives™ on Stack Overflow

How can I structure this nested mysql query?

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related