Member Avatar for verbob

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?

Thanks!

  • 2 Contributors
  • 3 Replies
  • 129 Views
  • 14 Hours Discussion Span
  • Latest Post Latest Post by verbob
Member Avatar for kevindougans 
if (isset($fname)) { $sql_fname = " && `fname` == '$fname'" ; } else { $sql_fname = "";  }

simples?

Member Avatar for verbob

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?

Edited by verbob because: n/a
Member Avatar for verbob

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";
				}
				else
					{
						$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 :)

Edited by verbob because: n/a
Be a part of the DaniWeb community

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