PHP MySQL drop down list search

Question

Been searching all over for this but cant find an answer so thought I would ask here.

I have 5 drop down 'option box' lists whose data is populated from a database. The database information is about a user selecting a computer manufacture from the first drop down list, a computer type from the second, a computer colour from the third and finally a minimum and maximum price from the fourth and fifth drop down lists.

These user selected variables are then posted to a php 'search' page and a query is run on them. My question is, if (for example) a user only selected a computer 'manufacture' and 'make' and left all the other option boxes blank, how would I run this on my SQL search? Would I need to set then 'non set' variables as wildcards?

Any help would be appreciated!

You do not delete your question after you have an answer to it. You leave it for posterity, so that others may benefit too. — Jonathan Leffler
– Jonathan Leffler, Commented Mar 25, 2012 at 20:08

Mark Byers · Accepted Answer · 2012-03-24 21:59:55Z

2

You can build your query dynamically, something like this:

$sql = "SELECT ... FROM ... WHERE 1=1 ";
if ($manufacturer != "") {
  $sql .= " AND manufacturer = ...";
}
if ($computerType != "") {
  $sql .= " AND computerType = ...";
}
// etc...

edited Mar 24, 2012 at 21:59

answered Mar 24, 2012 at 21:54

Mark Byers

844k202 gold badges1.6k silver badges1.5k bronze badges

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

4 Comments

Touy Over a year ago

Hi Mark,Thanks very much. Just one question im adding some pagination to my page, is it a simple case of me adding this to the end of the query? i.e: if ($computerType != "") { $sql .= " AND computerType = ..."; }"LIMIT $startRecord, $noOfRecords";

Mark Byers Over a year ago

@Tuoy: You must have an ORDER BY otherwise the results can come in a different order when you move to the next page. There are also some other things to be aware of, but this comment box is too small to go into those details. I suggest you search for tutorials on how to implement paging in MySQL.

Touy Over a year ago

Ok Mark thank you. I have a kind of working pagination script, can you just confirm that the "LIMIT $startRecord, $noOfRecords'" has been included within the query? Many many thanks again

Touy Over a year ago

Got it working forgot to add the LIMIT to the sql! Thanks again all.

Taha Paksu · Accepted Answer · 2012-03-24 22:02:23Z

1

SELECT * FROM TABLE WHERE 
COLUMN_NAME_1 = IF('".$select_result_1."'='',COLUMN_NAME_1,'".$select_result_1"') AND
COLUMN_NAME_2 = IF('".$select_result_2."'='',COLUMN_NAME_2,'".$select_result_2"') AND
COLUMN_NAME_3 = IF('".$select_result_3."'='',COLUMN_NAME_3,'".$select_result_3"') AND
COLUMN_NAME_4 = IF('".$select_result_4."'='',COLUMN_NAME_4,'".$select_result_4"') AND
COLUMN_NAME_5 = IF('".$select_result_5."'='',COLUMN_NAME_5,'".$select_result_5"');

answered Mar 24, 2012 at 22:02

Taha Paksu

15.6k2 gold badges50 silver badges83 bronze badges

Collectives™ on Stack Overflow

PHP MySQL drop down list search

2 Answers 2

4 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related