mysql select question 1

[safra]

hi,

I am pretty new with mysql. Can't figure out the most efficient way to do a mysql search in the next situation.

Have a form with about 6 fields among which 2 comboxes and one to directly search for a reference number. When the user doesnot choose to specify a value for the comboboxes the value is set to "all".

Is it possible to do the search from this form with one select statement?

I can't figure out how to get the correct search results search with the combobox values set to "all"? The same for empty form fields, how do I cancel them out for the search?

Hope anyone can help me with this?

thanks,
Ron

 

[AnakinPt]

as simple as having some if's

$array=array();
if($combo1!=0){
$array[]="field1=$combo1";
}
if($combo2!=0){
$array[]="field2=$combo2";
}
...

$query = "SELECT fields FROM table WHERE ".implode(" AND ",$array);

As simple as this.
It's a guide for you.
Anikin
Hugo Alexandre Dias
Web-Programmer
anikin_jedi@hotmail.com

 

[AnakinPt]

as simple as having some if's

$array=array();
if($combo1!=0){
$array[]="field1=$combo1";
}
if($combo2!=0){
$array[]="field2=$combo2";
}
...

$query = "SELECT fields FROM table WHERE ".implode(" AND ",$array);


As simple as this.
It's a guide for you.

P.S: I'm assuming the ALL option as value=0 Anikin
Hugo Alexandre Dias
Web-Programmer
anikin_jedi@hotmail.com

 

[safra]

Ah yes,

Was thinking about something similar but didn't know how to put it in the MySQL statement.

So the best way to deal with this sort of things is to, besides the comboxes, put the results of all form fields in an array if necessary, and use the array in the MySQL query like in your example.

Thanks Anikin!

Ron

 

[AnakinPt]

This is one way of work. Placing things in an array is a great way to simulate lists in PHP, cause you can do implodes, or even array_walks, for example.

implode returns a string with the elements of your array separated by the separator char.
array_walk applies the same function to all the elements of the array.

These are only two examples of the beneficts of arrays. When you use wisely they save you much work. Anikin
Hugo Alexandre Dias
Web-Programmer
anikin_jedi@hotmail.com