Search Help Needed

[neiltheblue]

I am trying to dispaly results out of a search in which i am able to type in any character. For example i have a field in a database for Surname and one for Forename. I am trying to display the results on the account that i can type any of these in. It works if i try and search just on, for example Surname but im struggling with 2. I am hoping to be able to search and print out of approx 12 fields given i type in a word which is in one of these fields. I hope this makes sense here is the code i have produced so far. Am i going wrong somewhere?

<h1> Search Results</h1>
<a href="index.php?page=language/speakersnew/index.php">Back to the Speakers Database</a>
<p />
<div align="center">
<table border="1" cellpadding="0" cellspacing="0" width="90%" bordercolor="#009900">
<tr>
<td>

<SCRIPT>
<!--//
function moreInfo(id)
{
camera = window.open(id,"Telephone_Directory","location=0,titlebar=0,z-lock=1,status=0,toolbar=0,resizable=0,scrollbars=1,menubar=0,alwaysRaised=1,directories=0,width=500,height=500");
camera.focus();
}
//-->
</SCRIPT>
<?php

$host = "localhost";
$user = "username";
$pass = "password";
$db_name = "databasename";

$search = $_POST["speakerssearch"];

mysql_connect($host, $user, $pass) or die("could not create connection");

@mysql_select_db($db_name) or die( "Unable to select database");

$query="SELECT * FROM speakers where (Surname like '%search%') OR (Forname like '%$search%') order by id";

$result = mysql_query($query);
$num = mysql_num_rows($result);
mysql_close();

if ($search == "" ¦¦ $search=="Search!")

{
echo "<font><center><p><br>Please enter a language to search for! <a href=\"index.php?page=language/speakersnew/index.php\">Try Again</a></p><p />";
}
elseif ($num == "0")
{
echo "<font><center><p /><br>There are currently no results for <b>$search</b>. Please <a href=\"index.php?page=language/speakersnew/index.php\">Try Again</a></p><p />";
}
else
{
echo "</td></tr></table>";
echo "<table width=\"90%\" border=\"1\" bordercolor=\"#009900\"><tr><td>";
echo "<table width=\"100%\" border=\"1\" bordercolor=\"#009900\"><tr><td bgcolor=\"#b2e0af\" height=\"30\" class=\"text\"><center>Number of results for $search - $num.</td></tr></table>";

echo "<table width=\"99%\" border=\"0\" cellpadding=\"0\" cellspacing=\"0\" class=\"text\" align=\"center\" >";

echo "<tr><td align=\"center\" ><b>Surname</b></td>";

echo "<td align=\"center\"><b>Forename</b></td>";

echo "<td align=\"center\"><b>Language</b></td>";

echo "<td align=\"center\"><b>Level</b></td>";

echo "<td align=\"center\"><b>Details</b></td></tr>";

$i=0;
while ($i < $num) {

$Surname=mysql_result($result,$i,"Surname");
$Forename=mysql_result($result,$i,"Forename");
$Title=mysql_result($result,$i,"Title");
$Site=mysql_result($result,$i,"Site");
$Department=mysql_result($result,$i,"Department");
$TelNumber=mysql_result($result,$i,"Tel Number");
$BleepNumber=mysql_result($result,$i,"Bleep Number");
$JobTitle=mysql_result($result,$i,"Job Title");
$WorkingHours=mysql_result($result,$i,"Working Hours");
$Weekends=mysql_result($result,$i,"Weekends");
$Language=mysql_result($result,$i,"Language");
$Level=mysql_result($result,$i,"Level");
$id=mysql_result($result,$i,"id");

$colors = array('#dad9d9','#f4f4f4');
$numOfColor = count($colors);

echo "<tr><td height=\"30\" align=\"center\" bgcolor='".$colors[$i % $numOfColor]."'>$Surname</td>";

echo "<td align=\"center\" bgcolor='".$colors[$i % $numOfColor]."'>$Forename</td>";

echo "<td align=\"center\" bgcolor='".$colors[$i % $numOfColor]."'>$Language</td>";

echo "<td align=\"center\" bgcolor='".$colors[$i % $numOfColor]."'>$Level</td>";

echo "<td align=\"center\" bgcolor='".$colors[$i % $numOfColor]."'><a href=\"javascript:moreInfo('http://ittestbox/language/speakersnew/details2.php?id=$id')\">More Info</a></td></tr>";

$i++;
}

echo "</table>";
}

?>
</div>

</tr>
</td>
</table>

[mcibor]

You have a mistake in your sql query. It should be:

$query = "SELECT * FROM speakers WHERE Surname LIKE '%$search%'
OR Forename LIKE '%$search%' ORDER BY id";

You were searching for surname containing word "search" and the non existent field Forname.

Hope this solves your problem!

Best regards
Michal Cibor

PS. Next time don't post the whole code, just the part that isn't working (without javascript e.g.)

[neiltheblue]

Many thanks for replying.

That didnt work for me but when i take the last bit of the query out eg (OR Forname like '%$search%') then the search works as i am only searching on one word. This is the error i get when i try the code you posted.

mysql_num_rows(): supplied argument is not a valid MySQL result resource

Would they be a problem with the script trying to find the number of rows then trying to return them?

All help is very much appreciated.

[neiltheblue]

Works a treat now thanks mcibor

Another success for the forum.

[mcibor]

Glad to be of help!
See you round!
Michal