search engine help

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
User avatar
boo_lolly
Forum Contributor
Posts: 154
Joined: Tue Nov 14, 2006 5:04 pm

search engine help

Post 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.
User avatar
aaronhall
DevNet Resident
Posts: 1040
Joined: Tue Aug 13, 2002 5:10 pm
Location: Back in Phoenix, missing the microbrews
Contact:

Post 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.
User avatar
boo_lolly
Forum Contributor
Posts: 154
Joined: Tue Nov 14, 2006 5:04 pm

Post 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?
User avatar
boo_lolly
Forum Contributor
Posts: 154
Joined: Tue Nov 14, 2006 5:04 pm

Post 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.
User avatar
aaronhall
DevNet Resident
Posts: 1040
Joined: Tue Aug 13, 2002 5:10 pm
Location: Back in Phoenix, missing the microbrews
Contact:

Post 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.
Last edited by aaronhall on Fri Jan 05, 2007 7:36 pm, edited 2 times in total.
User avatar
Ollie Saunders
DevNet Master
Posts: 3179
Joined: Tue May 24, 2005 6:01 pm
Location: UK

Post 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']);
User avatar
boo_lolly
Forum Contributor
Posts: 154
Joined: Tue Nov 14, 2006 5:04 pm

Post 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?
User avatar
Ollie Saunders
DevNet Master
Posts: 3179
Joined: Tue May 24, 2005 6:01 pm
Location: UK

Post 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.
Post Reply