I have this code for search in mysql but it doesent run and i dont know why.
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<fieldset>
<legend>search Employees</legend>
<form name="search" method="post" action= <?=$PHP_SELF?>>
Αναζήτηση για: <input type="text" name="find" /> στο
<Select NAME="field">
<Option VALUE="fname">First Name</option>
<Option VALUE="lname">Surname</option>
<Option VALUE="phone">Phone</option>
<Option VALUE="address">Address</option>
</Select>
<input type="hidden" name="searching" value="yes" />
<input type="submit" name="search" value="Search" />
</form>
</fieldset>
<?
//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
mysql_connect("localhost", "root", "123") or die(mysql_error());
mysql_select_db("ergasia2") or die(mysql_error());
// 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 EMPLOYEES WHERE upper($field) LIKE'%$find%'");
//And we display the results
while($result = mysql_fetch_array( $data ))
{
echo $result['fname'];
echo " ";
echo $result['lname'];
echo "<br>";
echo $result['info'];
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;
}
?>
Most probably because your query is wrong. You cannot use UPPER on column names like this. Also, not sure if your field name is like FIELD (full uppercase) or like Field (capitalize first letter only). And don't forget to escape your input!
Try this:
$escaped_field = mysql_real_escape_string($field);
$field_name = strtoupper($escaped_field); // if you want FIELD
$field_name = ucfirst($escaped_field); // if you want Field
$data = mysql_query("SELECT * FROM EMPLOYEES WHERE `$field_name` LIKE '%$find%'");
I might add, if your table names/column names really are full uppercase (e.g.: FIELD), you might want to change this. Uppercase keywords are generally reserved keywords, so it can be confusing.
Your code assumes that register_globals is turned on (new installs of php this is defaulted to off) this should be off as it is a security risk. I would suggest reading a tutorial for creating a SQL Search which shows security etc.
Instead of going into what is wrong, I wrote this tutorial up, perhaps it will help you out: Simple SQL Search