Help with search form using multiple fields

vjordan

I have a search form on a page that i am having trouble with. i can not seem to get the search string correct. In the form i have firstname, lastname, homephone, and email. I would like to upon search return data put in form. for example if i put "john" in firstname i want all rows where firstname is john to return. or if "john" is entered in firstname and "smith" is entered in lastname it will return all rows containing john smith. I have a frame set to display the data on the same page as the search page. I would also loke to data to print as a hyperlink pointing to userdata.php?custid={the PK ID of custid }. also if no text is entered and just search is pressed it will return all data in table asc by lastname.

could someone please help with this?

Thanks

tsinka

Hi,

please post some code ...

Thomas

vjordan

this is not the exact code, i can not get to the file right now but basicly here is what ive got

</php
...mysql_connect...
...mysql_select_db...
?>
<html>
<form action=$PHP_SELF method=post>
<input type=text id=firstname>
<input type=text id=lastname>
<input type=text id=homephone>
<input type=text id=email>
</form>
<frame>
<?
$query = mysql_query("select * from customerinfo where firstname like '%"$POST['firstname']"%', or '%"$POST['lastname']"%', or '%"$POST['homephone']"%', or '%"$POST['email']"%'");

tsinka

Hi,

you need to use AND in the query if I understood you right.
What problem(s) do you have exactly since you also might need to tweak the HTML code a bit if you're using frames.

Thomas

TheDefender

Build your query before executing it...

$query = SELECT * FROM customerinfo";

IF(isset($POST['firstname'])) {
$query .= " WHERE firstname like '%".$POST['firstname']."%'";

} elseif(isset($_POST['lastname'])) {
$query .= " WHERE lastname like......";

}

Then run the query:
$qry = mysql_query($query);

(Obviously, you will need to play with the if/else to make sure you cover all of the criteria, but this should get you started.)

If you are trying to search on multiple fields, your query WHERE clause may look something like...

WHERE firstname LIKE like '%".$POST['firstname']."%' AND lastname LIKE '%".$POST['lastname']."%'

tsinka

Hi,

when using AND in the query and the column names in the table are the same as the POST variable's names you could do something like:

<?PHP
$blnFirst = true;
$dataSubmitted = false;
$arrFields = array('firstname','lastname','homephone','email');
$where = "";
for ($cnt=0;$cnt<count($arrFields);$cnt++) {
  if (isset($_POST[$arrFields[$cnt]])) {
    $where .= (!$blnFirst ? "AND " : "").$arrFields[$cnt]." LIKE '%".$_POST[$arrFields[$cnt]]."%' ";  

    $dataSubmitted = true;
    $blnFirst = $blnFirst ? false : $blnFirst;
  }
}

$sql = "SELECT * FROM customerinfo".($dataSubmitted ? " WHERE $where" : "");
?>

This might look a little bit overkill but if you have a certain number of inputs on your page this might be worth a try.

If the column names differ you can simply use a mapping array to map the POST variables to column names.

I hope the code is correct, I can't test it now.

Thomas

vjordan

does this look as if it would be valid? if not am i heading in the right direction?

<html>

<head>

</head>

<body>

first name: <input type=text name=firstname> <br />

last name: <input type=text name=lastname> <br />

home phone : <input type=text name=homephone> <br />

email : <input type=text name=email> <br />

</form>

</body>

</html>

<?
$query = "SELECT * FROM customerinfo";

IF(isset($POST['firstname']))
{
$query .="WHERE firstname like '%".$POST['firstname']."%'";
}
IF(isset($POST['lastname']))
{
$query .="WHERE lastname like '%".$POST['lastname']."%'";
}
IF(isset($POST['homephone']))
{
$query .="WHERE lastname like '%".$POST['homephone']."%'";
}
IF(isset($POST['email']))
{
$query .="WHERE lastname like '%".$POST['email']."%'";
}

$qry = mysql_query($query);

$get_data = mysql_fetch_array($qry);

<html>

<body>

<frame>

<table>

<? php print {$_POST['firstname']}; ?>

</td><td>

<? php print {$_POST['lastname']}; ?>

</td><td>

<? php print {$_POST['homephone']}; ?>

</td><td>

<? php print {$_POST['email']}; ?>

</td></tr>

</frame>

</body>

</html>

TheDefender

At a quick glance, I see the following little things needing correction. No time to peruse the entire thing right now, but I will look later.

Always quote your attributes (for everything)...

and on tags that don't have a closing tag, conform them to the XHTML standards...

<input type="text" name="homephone" /> (Note the / before the > symbol.)

dnast

<?
$query = "SELECT * FROM customerinfo";

IF(isset($_POST['firstname']))
{
$query .="WHERE firstname like '%".$_POST['firstname']."%'";
}
IF(isset($_POST['lastname']))
{
$query .="WHERE lastname like '%".$_POST['lastname']."%'";
}
IF(isset($_POST['homephone']))
{
$query .="WHERE lastname like '%".$_POST['homephone']."%'";
}
IF(isset($_POST['email']))
{
$query .="WHERE lastname like '%".$_POST['email']."%'";
} 
?>

^ Your query string would end up like SELECT * FROM customerinfo WHERE firstname like '%foo%'WHERE lastname like '%bar%'...but you've got the basic idea, I see. 🙂

It should be more like:

<?
$query = "SELECT * FROM customerinfo WHERE";

IF(isset($_POST['firstname']))
{
$query .="firstname LIKE '%".$_POST['firstname']."%' AND ";
}
IF(isset($_POST['lastname']))
{
$query .="lastname like '%".$_POST['lastname']."%' AND ";
}
IF(isset($_POST['homephone']))
{
$query .="WHERE lastname LIKE '%".$_POST['homephone']."%' AND ";
}
IF(isset($_POST['email']))
{
$query .="WHERE lastname LIKE '%".$_POST['email']."%' AND ";
} 
$query = substr($query, 0, -4); //chop off the last AND - wherever that may be
?>