I have a MySQL database setup at my site
www.ex-brief.com. I have the PHP scripts to add information to the database, connect, etc. I will paste the code of everything I currently have. Here is where I am having trouble. I am not sure how to write a script that will search the MySQL database. I want it to be basically like the people search on Myspace where you can enter as much or as little information as you like and the searched are returned in order of relevance.
My database is named "exbriefc_profiles", table name is "contacts"
Fields are (so far, will be more possibly) "last" , "first" , "city" , "state"
Here is everything related to this site I have (PHP that is)...need to tie it together to make it work. Any help will be greatly appreciated.
NOTES: My other post for reference:
http://www.phpfreaks.com/forums/inde...tml#msg1093910
1) Form To Input Data Into Database (bare bones, not styled)
Code:
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html; charset=ISO-8859-1"
http-equiv="content-type">
<title></title>
</head>
<body>
<form action="insert.php" method="post">
First Name: <input name="first" type="text"><br>
Last Name: <input name="last" type="text"><br>
City: <input name="city" type="text"><br>
State: <input name="state" type="text"><br>
<br>
<input type="submit">
</form>
</body>
</html>
2) PHP Script to Add Information From Form To Database
Code:
<?php
include("dbinfo.inc.php");
$first=$_POST['first'];
$last=$_POST['last'];
$city=$_POST['city'];
$state=$_POST['state'];
mysql_connect(localhost,$username,$password);
@mysql_select_db($database) or die( "Unable to select database");
$query = "INSERT INTO contacts VALUES ('','$first','$last','$city','$state')";
mysql_query($query);
if($query) {
echo "Ex Brief Submitted Successfully.";
} else {
echo "The Ex Brief was not processed. Please review the information you entered for accuracy.";
}
mysql_close();
?>
3) PHP Script to Display Results from Database on webpage
Code:
<?
include("dbinfo.inc.php");
mysql_connect(localhost,$username,$password);
@mysql_select_db($database) or die( "Unable to select database");
$query="SELECT * FROM contacts";
$result=mysql_query($query);
$num=mysql_numrows($result);
mysql_close();
echo "<b><center>Database Output</center></b><br><br>";
?>
<table border="0" cellspacing="2" cellpadding="2">
<tr>
<th><font face="Arial, Helvetica, sans-serif">Name</font></th>
<th><font face="Arial, Helvetica, sans-serif">City</font></th>
<th><font face="Arial, Helvetica, sans-serif">State</font></th>
</tr>
<?
$i=0;
while ($i < $num) {
$first=mysql_result($result,$i,"first");
$last=mysql_result($result,$i,"last");
$city=mysql_result($result,$i,"city");
$state=mysql_result($result,$i,"state");
?>
<tr>
<td><font face="Arial, Helvetica, sans-serif"><? echo "$first $last"; ?></font></td>
<td><font face="Arial, Helvetica, sans-serif"><? echo "$city"; ?></font></td>
<td><font face="Arial, Helvetica, sans-serif"><? echo "$state"; ?></font></td>
<?
++$i;
}
echo "</table>";
?>
4) PHP Script to Search the database, it does not work, it just returns the form page, no results or error message
Code:
<h2>Search</h2>
<form name="search" method="post" action="<?=$PHP_SELF?>">
Seach for: <input type="text" name="find" /> in
<Select NAME="field">
<Option VALUE="first">First Name</option>
<Option VALUE="last">Last Name</option>
<Option VALUE="city">City</option>
<Option VALUE="state">State</option>
</Select>
<input type="hidden" name="searching" value="yes" />
<input type="submit" name="search" value="Search" />
</form>
<?
//This is only displayed if they have submitted the form
if ($searching =="yes")
{
echo "<h2>Results</h2><p>";
//If they did not enter a search term we give them an error
if ($find == "")
{
echo "<p>You forgot to enter a search term";
exit;
}
// Otherwise we connect to our Database
include("dbinfo.inc.php");
mysql_connect(localhost,$username,$password);
@mysql_select_db($database) or die( "Unable to select database");
// We preform a bit of filtering
$find = strtoupper($find);
$find = strip_tags($find);
$find = trim ($find);
//Now we search for our search term, in the field the user specified
$data = mysql_query("SELECT * FROM contacts WHERE upper($field) LIKE'%$find%'");
//And we display the results
while($result = mysql_fetch_array( $data ))
{
echo $result['first'];
echo " ";
echo $result['last'];
echo "<br>";
echo $result['city'];
echo "<br>";
echo $result['state'];
echo "<br>";
echo "<br>";
}
//This counts the number or results - and if there wasn't any it gives them a little message explaining that
$anymatches=mysql_num_rows($data);
if ($anymatches == 0)
{
echo "Sorry, but we can not find an entry to match your query<br><br>";
}
//And we remind them what they searched for
echo "<b>Searched For:</b> " .$find;
}
?>
