Wondering if there's a better way to do this query?

misteraven

I'm trying to code a search form for an event calendar script I'm working on. I know enough php/mysql to get into trouble, but am far from being a decent coder on my own. anyhow, I was wondering if there was a better way to submit a form to do a mysql query since this seems a little sloppy to me. The form has to have multiple options from text fields, to dropdowns, to radio buttons. I'd like it to work whether you select and submit one option, or select several to better refine the search. Anyhow, this is what I have on my test form with 3 options... (each form element is a numbered searchstring).

if ($searchstring OR $searchstring2 OR $searchstring3)
	{
	$sql="SELECT * FROM $table WHERE $searchtype LIKE '%$searchstring%' AND $searchtype2 LIKE '%$searchstring2%' AND $searchtype3 LIKE '%$searchstring3%' ORDER BY eventname, syear, smonth, sday ASC";

Thanks in advance for any help...

Rodders

For this kind of situation, I 'build' my SQL statement as I work through the form fields...

Let's say you have a form with 2 fields:


<INPUT TYPE=TEXT NAME=Field1>

<SELECT NAME=Field2>
<OPTION VALUE=''>Please choose...</OPTION>
<OPTION VALUE='x'>Value X</OPTION>
<OPTION VALUE='y'>Value Y</OPTION>
<OPTION VALUE='z'>Value Z</OPTION>
</SELECT>

In my PHP script that processes this I would do something like this......


$sql = "SELECT * FROM mytable WHERE 1 = 1 ";

if (isset($_POST['Field1'])) {
$sql .= "AND Field1 = '" . $_POST['Field1'] . "' ";
}

if (isset($_POST['Field2'])) {
$sql .= "AND Field2 = '" . $_POST['Field2'] . "' ";
}

You can of course add wildcards (%) as necessary. Let me know how you get on.

misteraven

Not sure why, but I couldn't get this code to work. I keep geting a parse error, but without any specifics. I'm guessing it has something to do with the rest of the code, but the error is at the line of the first sql statement.

Any ideas?

Rodders

If you could post your entire script........using the tags of course........and also copy & paste the exact error message text then I (or someone else) will be able to find the cause of this problem...it's probably a typo

Rich