MySQL search query with potentially empty search parameters

[mysql_dude]

I'm creating a search script in PHP for a database table that has 25 columns, thus requiring 25 entry fields in the search form. BUT! i'd like for it to be possible to omit those that have been left empty by the user (say, he entered something in 2 fields of those 25).

How can i have null fields omitted in my query?

This is what i use for the moment (sorry for the length and the formatting), but empty fields are searched as such and thus any table field containing a space is returned (which pretty much means all the lines in the D😎.

$query = "SELECT DISTINCT id, embranchement, s_embranchement, classe, ordre, section, famille, ensemble_noms_latins, territoire_lettres, toponyme_of, localite, latitude_deg, latitude_min, longitude_deg, longitude_min, habitat, date11, date12, date13, recolteur, acronyme, no_acquisition, numero, lat_map, long_map FROM recolte WHERE((id LIKE '%$POST['id']%') OR (embranchement LIKE '%$POST['embranchement']%') OR (s_embranchement LIKE '%$POST['s_embranchement']%') OR (classe LIKE '%$POST['classe']%') OR (ordre LIKE '%$POST['ordre']%') OR (section LIKE '%$POST['section']%') OR (famille LIKE '%$POST['famille']%') OR (ensemble_noms_latins LIKE '%$POST['ensemble_noms_latins']%') OR (territoire_lettres LIKE '%$POST['territoire_lettres']%') OR (toponyme_of LIKE '%$POST['toponyme_of']%') OR (localite LIKE '%$POST['localite']%') OR (latitude_deg LIKE '%$POST['latitude_deg']%') OR (latitude_min LIKE '%$POST['latitude_min']%') OR (longitude_deg LIKE '%$POST['longitude_deg']%') OR (longitude_min LIKE '%$POST['longitude_min']%') OR (habitat LIKE '%$POST['habitat']%') OR (date11 LIKE '%$POST['date11']%') OR (date12 LIKE '%$POST['date12']%') OR (date13 LIKE '%$POST['date13']%') OR (recolteur LIKE '%$POST['recolteur']%') OR (acronyme LIKE '%$POST['acronyme']%') OR (no_acquisition LIKE '%$POST['no_acquisition']%') OR (numero LIKE '%$POST['numero']%') OR (lat_map LIKE '%$POST['lat_map']%') OR (long_map LIKE '%$_POST['long_map']%'))";

Any help would be appreciated!

Thanx!

[tha_mink]

Here's a dirty hack at it...(i would consider re-thinking how you are designing this but hey...)

$query = "SELECT DISTINCT id, embranchement, s_embranchement, classe, ordre, section, famille, ensemble_noms_latins, territoire_lettres, toponyme_of, localite, latitude_deg, latitude_min, longitude_deg, longitude_min, habitat, date11, date12, date13, recolteur, acronyme, no_acquisition, numero, lat_map, long_map FROM recolte WHERE ";

foreach($_POST as $varName => $value) {
     if ($value != "") {
         $query .= "(" . $varName . " LIKE '%" . $value . "%') OR ";
     }
}
// this bit added to deal with the trailing "OR"
// you set "id" to whatever your primary key is so it never returns a record due to this bit.
$query .= "id=null";

[mysql_dude]

Thanks for the tip, i'm gonna give it a try. But as you say, i should rethink how i do this and i did this weekend. i believe i may have a solution... if i don't i'll be right back here askin' for more help from the pros!

😉

Thanks a bunch.