Searching MySQL with multiple criteria

mazdaboi

Hello all,

I'm working on a PHP/MySQL project, and wanted to get some advise... I need to search a MySQL database based upon what is entered in about 7 different text input's... Last name, first name, phone number, etc.... Is there a good way to modify the query depending on if the text inputs are blank or not? I was going to do something like

$query = "SELECT * FROM table WHERE col1 = '$value1' AND col2 = '$value2';

etc etc...

But what if they don't put anything in the text input with the name value2? so it turns into

$query = "SELECT * FROM table WHERE col1 = '$value1' AND col2 = '';

Is there some efficient way to write an if/else clause that detects which input tags are blank, and changes the SELECT clause accordingly? Or do I just need to write a huge if/else that takes into account the different combinations of inputs...

Thanks,
Brad

sarahk

Hey, this isn't too bad.

Yes you do need a fancy string builder. I'd do it using a function to replicate the logic more easily but that's just me.

$sql = "select * from mytable where "
if (!empty($txt1)) $sql .= " field1 = '$txt1' and ";
if (!emtpy($txt2)) $sql .= " field2 = '$txt2' and ";

$sql = substr($sql, 0, -4); \ to knock off the last and

then you can add
$sql .= " order by field1, field2"

and other stuff.

Get Access and play around with sql too if you need to investigate things like UNIONS and the use of brackets and OR clauses.

mazdaboi

Thanks for the tip! That's a good way to do it. For some reason I just had a major brain block and couldnt figure out an efficient way to do that... Thanks again!! It works great.

pxfisher

sarahk;629680 wrote:
$sql = "select * from mytable where "
if (!empty($txt1)) $sql .= " field1 = '$txt1' and ";
if (!emtpy($txt2)) $sql .= " field2 = '$txt2' and ";

$sql = substr($sql, 0, -4); \ to knock off the last and

then you can add
$sql .= " order by field1, field2"

(If you're still around) I understand what you're suggesting. But I use DreamWeaver and incorporating your tip into the way it builds the code preceding where I would incorporate your suggestion has proven difficult for me. I am wondering if you would look at my code and make a suggestion?

dagon

If your editor is stopping you write proper php, then use a different editor.