Page 1 of 1

search engine help

Posted: Fri Jan 05, 2007 11:53 am
by boo_lolly
i have a small search form with a few fields (one of them is required). i'm having trouble figuring out how to make it work. for instance, if there is an input field that doesn't have data, how would i populate a MySQL query to not query that field? i've got two text input fields (one of which is required) and 3 drop down menus. here's my code.

Code: Select all

<form method="POST" action="index.php?action=simplesearch">

First Name:<input type="text" name="fname"><BR>

Last Name:<input type="text" name="lname"><FONT COLOR="FF0000" SIZE="-1">(required)</FONT><BR>

<TABLE><TR>
<TD>
<SELECT NAME="event_month">
<OPTION VALUE="">select a month
<OPTION VALUE="1">January
<OPTION VALUE="2">February
<OPTION VALUE="3">March
<OPTION VALUE="4">April
<OPTION VALUE="5">May
<OPTION VALUE="6">June
<OPTION VALUE="7">July
<OPTION VALUE="8">August
<OPTION VALUE="9">September
<OPTION VALUE="10">October
<OPTION VALUE="11">November
<OPTION VALUE="12">December
</SELECT>
</TD>
<TD>
<SELECT NAME="event_day">
<OPTION VALUE="">select a day
<OPTION VALUE="1">01
<OPTION VALUE="2">02
<OPTION VALUE="3">03
<OPTION VALUE="4">04
<OPTION VALUE="5">05
<OPTION VALUE="6">06
<OPTION VALUE="7">07
<OPTION VALUE="8">08
<OPTION VALUE="9">09
<OPTION VALUE="10">10
<OPTION VALUE="11">11
<OPTION VALUE="12">12
<OPTION VALUE="13">13
<OPTION VALUE="14">14
<OPTION VALUE="15">15
<OPTION VALUE="16">16
<OPTION VALUE="17">17
<OPTION VALUE="18">18
<OPTION VALUE="19">19
<OPTION VALUE="20">20
<OPTION VALUE="21">21
<OPTION VALUE="22">22
<OPTION VALUE="23">23
<OPTION VALUE="24">24
<OPTION VALUE="25">25
<OPTION VALUE="26">26
<OPTION VALUE="27">27
<OPTION VALUE="28">28
<OPTION VALUE="29">29
<OPTION VALUE="30">30
<OPTION VALUE="31">31
</SELECT>
</TD>
<TD>
<SELECT NAME="event_year">
<OPTION VALUE="">select a year
<OPTION VALUE="2002">2002
<OPTION VALUE="2003">2003
<OPTION VALUE="2004">2004
<OPTION VALUE="2005">2005
<OPTION VALUE="2006">2006
<OPTION VALUE="2007">2007
<OPTION VALUE="2008">2008
<OPTION VALUE="2009">2009
<OPTION VALUE="2010">2010
</SELECT>
</TD>
</TR>
</TABLE>
<CENTER>
<input type="SUBMIT" value="Search">

                <?php
                $fname = $_POST['fname'];
                $lname = $_POST['lname'];
                $event_day = $_POST['event_day'];
                $event_month = $_POST['event_month'];
                $event_year = $_POST['event_year'];
                ?>
</CENTER>
</form>
here's my query so far... which barely works...

Code: Select all

$result = mysql_query("SELECT * FROM my_search_table
                                             WHERE brideLname LIKE '%". $lname ."%' OR groomLname LIKE '%". $lname ."%'
                                             AND event_month ='". $event_month ."'
                                             AND event_day = '". $event_day ."'
                                             AND event_year = '". $event_year ."'")
                                             or die(mysql_error());
i don't know what to do. or even where to start... any suggestions? it is posting to the same page, btw.

Posted: Fri Jan 05, 2007 12:15 pm
by aaronhall
First, you MUST use mysql_real_escape_stringon user input to protect against injection attacks.

You could use a series of IF statements to check if the variables are !empty, and append the AND's to the WHERE clause for each non-empty field.

Posted: Fri Jan 05, 2007 2:51 pm
by boo_lolly
this is how i did it. in theory it should work beautifully.

Code: Select all

<?php
	if($lname == NULL && $fname == NULL && $event_day == NULL && $event_month == NULL && $event_year == NULL){
		echo "<FONT COLOR=\"FF0000\">You must enter at least one input field. Please try again.</FONT>\n";
		exit;
	}else{
		$query_array = array();
		if($lname != NULL){
			$query_array[] = "brideLname LIKE '%". $lname ."%' OR groomLname LIKE '%". $lname ."%'";
		}
		if($fname != NULL){
			$query_array[] = "brideFname LIKE '%". $fname ."%' OR groomFname LIKE '%". $fname ."%'";
		}
		if($event_day != NULL){
			$query_array[] = "event_day LIKE '%". $event_day ."%'";
		}
		if($event_month != NULL){
			$query_array[] = "event_month LIKE '%". $event_day ."%'";
		}
		if($event_year != NULL){
			$query_array[] = "event_year LIKE '%". $event_year ."%'";
		}

		$query_string = "";
		foreach($query_array as $key => $val){
			$query_string .= " ". $val ." AND";
		}
		$query_string = substr($query_string, 0, 4);

		$result = mysql_query("SELECT * FROM my_search_table WHERE ". $query_string ."") OR die(mysql_error());
	}
?>
but it gives me this error:

Code: Select all

evenUnknown column 'even' in 'where clause' even
why is it doing this? i think it has something to do with the "event_day/month/year" part, but why would it take off the 't'??? what's going on here?


EDIT: upon further review, i concluded there's something terribly wrong with my substr() function. i'm not defining the parameters correctly. how would i cut off the last 4 characters of the $query_string?

Posted: Fri Jan 05, 2007 3:02 pm
by boo_lolly

Code: Select all

$query_string = substr($query_string, 0, -4);
is how you do it. i'm having some other issues, but i don't think it has to do with this new code. i think it's old code that's messed up. as i said before... it never worked properly.

Posted: Fri Jan 05, 2007 5:08 pm
by aaronhall
Try implode() instead of the for loop and empty() instead of the null comparison.

EDIT: I didn't explain why you shouldn't use NULL to compare against get/post values. These values are always initialized strings, empty or not, and are never equal to NULL:

Code: Select all

$variable = 0;
echo var_dump($variable == NULL); // (bool)true

$variable = "";
echo var_dump($variable == NULL); // (bool)true

$variable = "";
echo var_dump((string)$variable == NULL); // (bool)false

$_POST['some_val']; // an empty post value submitted from form
echo var_dump($_POST['some_val'] == NULL); // (bool)false
This is probably the reason your script isn't working as expected.

Posted: Fri Jan 05, 2007 7:20 pm
by Ollie Saunders
You are on the right track boo_lolly. Aaron's advise is good.
In code it is best to reduce duplication as much as possible. For instance I can see here you could do with a function a bit like this:

Code: Select all

function constructLikeClause($fields, $value = null)
{
    if (empty($value)) {
        return '';
    }
    $out = '';
    foreach ((array)$fields as $field) {
        $value = mysql_real_escape_string($value);
        // construct SQL here
    }
    return $out;
}
Then you can do something like this:

Code: Select all

$sql.= constructLikeClause(array('plum', 'apple'), $_POST['fruit']);
$sql.= constructLikeClause('person', $_POST['name']);

Posted: Sun Jan 07, 2007 11:02 pm
by boo_lolly
thank you very much arron for your advice. i will definitely use empty() and implode(). much better than my code.

ole, man i'm tryin to be really good, but i cannot understand your code. it is beyond my skills. can you explain what the code does? and how i would use it in replace of my own code?

Posted: Mon Jan 08, 2007 6:49 am
by Ollie Saunders
ole, man i'm tryin to be really good, but i cannot understand your code. it is beyond my skills. can you explain what the code does? and how i would use it in replace of my own code?
Don't worry actually. I think you would need a class to do what I suggested anyway. I just tried it now and a function alone was too limiting.