What I would like to do is search a MySQL table for anywhere from one to 12 seprate fields, depending on what is submitted from a search form(has 12 different things that a user can search -- its an "AND" search only)

What I am unsure of is how to write the php code for multiple(possible combinations of) terms without having huge amount of lines of code to account for every combination.

my first thoughts are to add if statements, like this:

if (isset($fname)) { $sql_fname = " `fname` == '$fname'" ; } else { $sql_fname = "";  }

And then add to the query, like this:

$query = "SELECT * FROM tblsearch WHERE ".$sql_fname." && <and_so_on___more_search_terms_will_be_here>";

so that if the particular field isn't "POST"ed, then it is defined as "" and won't show up in the query.

My question is simply how the heck can I add the "&&" without knowing which fields the user will use in a search?

ALSO - does anyone know of a "grid" type script where I can just load the table into and filter the(multiple "AND"s) search terms via ajax?


Recommended Answers

All 3 Replies

if (isset($fname)) { $sql_fname = " && `fname` == '$fname'" ; } else { $sql_fname = "";  }


Thanks for the reply.

One issue though (I already thought of trying that) - if there are no terms BEFORE that, then the query would be:

SELECT * FROM tblsearch WHERE && `fname`== '$fname'

see how that may be a problem?

I found a solution..

For anyone who may encounter similar issues, heres what I did:

I started the code by setting an arbitrary variable = 0 ($before=0) and looped through each form field from POST, if there was a search term in the field, I set the variable = 1, if the variable was already set to 1, it meant that a search term came before and required that a && was present before the sql for that particular field

if (!($idnumber == '')) 
			if ( $before == "0")
					$sql_idnumber = " `idnumber` == '$idnumber'";
					$before = "1";
						$sql_idnumber = " && `idnumber` == '$idnumber'";	

I then pieced together all the little sql codes in to a query

$query = "SELECT * FROM tblsearch WHERE ".$sql_fname.$sql_idnumber.$sql_program.$sql_referral.$sql_startdate.$sql_finishdate.$sql_intake.$sql_gedcomplete.$sql_programcomplete.$sql_employmentlooking.$sql_hasredflags;

Don't know if you can follow my logic, but it appears to work :)

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, learning, and sharing knowledge.