1

I am developing a search engine, and everything is fine. I am at the stage where the user can do an advance search.

This advance search has 2 combo-boxes and 3 check-boxes. So I started to creating if statements: IF 1st Combobox is not 0 then do this query If 1st Combobox is selexted then do this query and so on and so forth.

So all the combinations. I found that really bad approach and I am looking for a better approach ? For instance can I create a universal query that if a value is Null to not consider that restriction?

For example: Lets say that I am trying to do an advance search with the 1st combobox having a value, and all the others are blank.

SELECT ..... WHERE a = 1 and b = null and c = null and d = null and e = null

So null there because the user didn't select something, and therefore the condition with nulls should not be considered ?

Phrixus
  • 1,209
  • 2
  • 19
  • 36
  • I wouldn't use `= null` because that will never evaluate to true. Perhaps you should use `is null`. – Gordon Linoff Jun 18 '15 at 14:17
  • You mean to use `is null` in the Where clause ? – Phrixus Jun 18 '15 at 14:20
  • Perhaps use dynamic sql. Some good examples are here: http://stackoverflow.com/questions/190776/how-to-have-dynamic-sql-in-mysql-stored-procedure – mba12 Jun 18 '15 at 14:34
  • @mba12 That is a cool idea, I am working with PHP and already using prepared statements! If I don't set something in the bind_param then will not be consider as restriction ? – Phrixus Jun 18 '15 at 14:38
  • I have used dynamic sql in the past but you must be careful. The advantage is you can create sql on the fly. You can include or exclude whatever parameters you want with dynamic sql. The disadvantage is that sql in the stored proc will not have been compiled in advance and if your code throws an error in production that will obviously be a problem. Make sure you triple test any dynamic sql routine that goes out to production. – mba12 Jun 18 '15 at 14:46
  • OK thank you. You may now post that as an answer because I think will help – Phrixus Jun 18 '15 at 14:49

0 Answers0