Search Script With Several Options

[Knuckles]

Hello PHPFreaks,

 

I am busy with creating a search script that will search the database. I got it working so far but i want it to search for not only one thing but several things so actually make options in the form that you you choose only to search on name or only on address. How do i do this?

 

The PHP code:

<?php
									mysql_connect ("localhost", "username","password")  or die (mysql_error());
									mysql_select_db ("database table");

										$term = $_POST['term'];

										$sql = mysql_query("select * from databasetable where naam like '%$term%'");

									while ($row = mysql_fetch_array($sql)){
										echo "<center>";
										echo "<table width='600px'>";
										echo "<tr>";
										echo "<td><b>Naam:</b> </td><td>".$row['naam'];
										echo "</td><td><b>Telefoon:</b> </td><td>".$row['telefoon'];
										echo "</td></tr><tr>";
										echo "<tr>";
										echo "<td><b>Adres:</b> </td><td>".$row['adres'];
										echo "</td><td><b>Mobiel:</b> </td><td>".$row['mobiel'];
										echo "</td></tr><tr>";
										echo "<tr>";
										echo "<td><b>Postcode:</b> </td><td>".$row['postcode'];
										echo "</td><td><b>E-mail:</b> </td><td>".$row['email'];
										echo "</td></tr><tr>";
										echo "<tr>";
										echo "<td><b>Woonplaats:</b> </td><td>".$row['woonplaats'];
										echo "</td></tr>";
										echo "</table>";
										echo "<br /><br />";
										echo "<table width='400px'>";
										echo "<tr>";
										echo "<td><b>Datum:</b> </td><td>".$row['datum'];
										echo "</td></tr><tr><td><b>Probleem:</b> </td><td>".$row['probleem'];
										echo "</td></tr><tr>";
										echo "<td><b>Mogelijke oplossing:</b> </td><td>".$row['oplossing1'];
										echo "</td></tr><tr><td><b>Oplossing:</b> </td><td>".$row['oplossing2'];
										echo "</td></tr><tr>";
										echo "<td><b>Wachtwoord:</b> </td><td>".$row['wachtwoord'];
										echo "</td><tr><td><b>Geschatte Prijs:</b> </td><td>".$row['prijs'];
										echo "</td></tr><tr>";
										echo "<td><b>Bijgeleverd:</b> </td><td>".$row['bijgeleverd'];
										echo "</td></tr>";
										echo "</table><br />";
										echo "---------------------------------------------------------------------------------------";
										echo "</center>";
										echo "<br/><br/>";
											}
								?>

 

HTML Form:

<form action="search.php" method="post">
									<b>RMA Zoeken:</b><br /> <input type="text" name="term" /><br /><br />
									<input type="submit" class="art-button" name="submit" value="Submit" />
								</form>

 

 

[joel24]

something like this

mysql_connect ("localhost", "username","password")  or die (mysql_error());
mysql_select_db ("database table");

$whereClause = 'WHERE ';

if (isset($_POST['term'])) {
$term = mysql_real_escape_string($_POST['term']);
$whereClause .= " AND naam like '%$term%'";
}

if (isset($_POST['address'])) {
$address = mysql_real_escape_string($_POST['address']);
$whereClause .= " AND address = '$address'";
}

//and continue doing this for all your form elements

$sql = mysql_query("select * from databasetable $whereClause");

[Knuckles]

First off all Thanks for the fast reply but shouldnt the next code be

 

$whereClause .= " AND address = '$address'";

 

$whereClause .= " AND address = '%$address%'";

 

Also how do i do it with the html code then?

[joel24]

that would end up searching for

"SELECT * FROM table WHERE address = '$address' AND address like '%$address%'"

which would return the same as "WHERE address = '$address'"

 

You want to search for any or all of the fields, then use the first script I posted and modify it to suit

You need a form with inputs for naam, address etc then use the isset() function to check if the user has put in any search terms into the textbox, if so add it to the where clause.

 

[Knuckles]

Oke, But how does this work with the HTML cause i only have term, Would this work if i use several forms or so?

[joel24]

oh, so term is just a general term?

I though you wanted to search form like

address:

name:

telephone:

etc

 

you want the single 'term' to be found in any of the fields??

[Knuckles]

something like this

mysql_connect ("localhost", "username","password")  or die (mysql_error());
mysql_select_db ("database table");

$whereClause = 'WHERE ';

if (isset($_POST['term'])) {
$term = mysql_real_escape_string($_POST['term']);
$whereClause .= " AND naam like '%$term%'";
}

if (isset($_POST['address'])) {
$address = mysql_real_escape_string($_POST['address']);
$whereClause .= " AND address = '$address'";
}

//and continue doing this for all your form elements

$sql = mysql_query("select * from databasetable $whereClause");

 

With this i get the following error:

 

Warning: mysql_fetch_array() expects parameter 1 to be resource, boolean given in C:\xampp\htdocs\search.php on line 88

[Knuckles]

I now have it like this.

 

                                                               <form action="search.php" method="post">
									<b>Zoeken op Naam:</b><br /> <input type="text" name="term" /><br /><br />
									<b>Zoeken op Postcode:</b><br /> <input type="text" name="postcode" /><br /><br />
									<b>Zoeken op Woonplaats:</b><br /> <input type="text" name="woonplaats" /><br /><br />
									<b>Zoeken op Probleem:</b><br /> <input type="text" name="probleem" /><br /><br />
									<input type="submit" class="art-button" name="submit" value="Submit" />
								</form>

[joel24]

so you would have something like

$whereClause = 'WHERE ';

if (isset($_POST['term'])) {
$term = mysql_real_escape_string($_POST['term']);
$whereClause .= " AND naam like '%$term%'";
}

if (isset($_POST['postcode'])) {
$postcode = mysql_real_escape_string($_POST['postcode']);
$whereClause .= " AND postcode = '$postcode'";
}

if (isset($_POST['woonplaats'])) {
$woonplaats = mysql_real_escape_string($_POST['woonplaats']);
$whereClause .= " AND woonplaats = '$woonplaats'";
}

if (isset($_POST['probleem'])) {
$probleem = mysql_real_escape_string($_POST['probleem']);
$whereClause .= " AND probleem = '$probleem'";
}

//ensure where clause has conditions added, if not then remove clause
if ($whereClause == 'WHERE ') {
$whereClause = '';
}

$sql = mysql_query("select * from databasetable $whereClause");

//now do the while loop etc to echo results

[Knuckles]

I keep on getting the same error:

 

Warning: mysql_fetch_array() expects parameter 1 to be resource, boolean given in C:\xampp\htdocs\search.php on line 95

[Knuckles]

Anyone know how to get this working?

[QuickOldCar]

I just briefly looked at your code and what you are doing.

 

My impression is you want a multiple where clause that you can then do advanced searches on in a few ways.

 

Make a form with different fields and insert that variable into the where clause of where to search, also make an all in the form select so the default can do a search in all fields if nothing was selected.

 

Here's some links to post here I wrote for doing multiple mysql queries with different advanced searches.

 

http://www.phpfreaks.com/forums/index.php?topic=327039.msg1539799#msg1539799

http://www.phpfreaks.com/forums/index.php?topic=319152.msg1504627#msg1504627

http://www.phpfreaks.com/forums/index.php?topic=318909.msg1503154#msg1503154

http://www.phpfreaks.com/forums/index.php?topic=318786.msg1502751#msg1502751

 

I'm sure there are other posts about searches in my profile.

 

Using LIKE is kinda slow, I'd recommend doing fulltext and making an index on every where or and clause are to be using.

[Knuckles]

Thanks for the reply but im not in need of whole new search im actually just in need of getting this one to work. Only problem i keep getting is the error.

[Knuckles]

The meaning is for the scipt to search only 1 option and not 3 things at once so shouldnt it be else then instead of if.

 

Still gives the same error if i change to else thought.

[Knuckles]

bump. anyone?

[joel24]

yes, if you want to search with one of the three you would change it to

if (isset($_POST['term'])) {
$term = mysql_real_escape_string($_POST['term']);
$whereClause = "WHERE naam like '%$term%'";
} else if (isset($_POST['postcode'])) {
$postcode = mysql_real_escape_string($_POST['postcode']);
$whereClause = " WHERE postcode = '$postcode'";
} else if (isset($_POST['woonplaats'])) {
$woonplaats = mysql_real_escape_string($_POST['woonplaats']);
$whereClause = "WHERE woonplaats = '$woonplaats'";
}

$sql = mysql_query("select * from databasetable $whereClause");

 

Just make sure the user knows only to search with one box, or have a select element where the user designates what they're searching for.

[PaulRyan]

Something like this may be what you're looking for.

 

<?PHP
  if(isSet($_POST['term'])) {
    $thisSearch = mysql_real_escape_string($_POST['term']);
    $statement  = "naam LIKE '%$thisSearch%'"; // Start of query
  } else {
    echo 'Please enter at least a term.'; // If the term is not present, then it will break the statement.
  }

  if(isSet($_POST['postcode'])) {
    $thisSearch = mysql_real_escape_string($_POST['postcode']);
    $statement  = " OR postcode LIKE '%$thisSearch%'"; // Optional extra criteria
  }

  if(isSet($_POST['woonplaats'])) {
    $thisSearch = mysql_real_escape_string($_POST['woonplaats']);
    $statement  = " OR woonplaats LIKE '%$thisSearch%'"; // Optional extra criteria
  }

  $finalStatement = "SELECT * FROM databasetable WHERE $statement";

?>

 

Regards, PaulRyan.