Search Function

[jeffgman]

I have a PHP page which lists out the results of a query from a MySQL database into a table. I want to put a text input at the top of the table so the users can refine the search. Here is the code for my page. Any help would be greatly appreciated.

	// connnect to the database
	if (!($link=mysql_pconnect($hostName, $userName, $password))) {
		DisplayErrMsg(sprintf("error connecting to host %s by user %s", $hostName, $userName));
		exit();
	}

	// select the database
	if (!mysql_select_db($databaseName, $link)) {
		DisplayErrMsg(sprintf("Error in selecting %s database", $databaseName));
		DisplayErrMsg(sprintf("error:%d %s", mysql_errno($link), mysql_error($link)));
		exit();
	}

	// send the sql command
	if (!($result=mysql_query("SELECT * FROM FORMS ORDER BY NAME", $link))) {
		DisplayErrMsg(sprintf("Error in executing %s stmt", $stmt));
		DisplayErrMsg(sprintf("error:%d %s", mysql_errno($link), mysql_error($link)));
		exit();
	}

	// display the results of the search

	echo "Refine Your Search:";
	echo "<form action=\"/forms_listing.php\" method=\"post\" name=\"search_forms\">
			<table width=\"550\" border=\"1\">
			<TR>
				<TD width=\"50\">Name</TD>
				<TD width=\"430\" colspan=\"2\" align=\"center\"><input name=\"$sname\" type=\"text\" size=\"45\" maxsize=\"255\"></TD>
				<TD width=\"70\" align=\"center\" colspan=\"2\"><input name=\"submit\" type=\"submit\"></TD>
			</TR>
			</form>
			</table>";
	echo "<BR><BR>
		<DIV=\"center\">
		<TABLE width=\"550\" border=\"1\">
		<TR>
			<TD width=\"50\"><B>Category</B></TD>
			<TD width=\"105\"><B>Name</B></TD>
			<TD width=\"325\"><B>Description</B></TD>
			<TD width=\"50\"><B>File Download</B></TD>
			<TD width=\"20\"><B>Date Added</B></TD>
			</TR>\n";
	while (($row=mysql_fetch_array($result))) {
	$id=$row["ID"];
	$category=$row["Category"];
	$name=$row["Name"];
	$description=$row["Description"];
	$file=$row["File"];
	$date=$row["Date"];
	$filepdf=$row["FilePDF"];
	echo "<TR>
			<TD>$category</TD>
			<TD>$name</TD>
			<TD>$description</TD>
			<TD>";
	if ($file != "") {
			echo "<<A HREF=\"$file\">Download</A>>";
	}
	if ($filepdf != "") {
			echo "<<A HREF=\"$filepdf\">PDF</A>>";
	}
			echo "</TD>
			<TD>$date</TD>
	</TR>\n";
	}
	echo "</TABLE>
	      </DIV>";
	mysql_free_result($result);

[zeno430]

$szQuery="SELECT * FROM FORMS ";
if(isset($HTTP_POST_VARS['sname']))
$szQuery.=" WHERE Name Like '$sname%' ";
$szQuery.="ORDER BY NAME";

If you are performing the extract word search, then use Name= '$sname' instead

[jeffgman]

Great, thanks for the information. I will give it a try tomorrow. Does that do an exact word search, or just if the name contains the search term? What I mean, is if the $name field is "This is a game." Does the search need to say "This is a game." or can it just have "This" and/or "game" and it will match?

Thanks,
Jeff

[mcgruff]

If $value = 'buff';

LIKE '%" . $value . "%' - finds buff, buffy, rebuffed

RLIKE '[[:<:]]" . $value . "[[:>:]]' - buff only not buffy or rebuffed

With several search terms, turn them into an array then loop through the array to create a query to find each word separately - or stick them all in a single RLIKE query to find them as a whole phrase.

Take a look at http://forums.devnetwork.net/viewtopic.php?t=8772 for some notes on FULLTEXT searches (rank results, boolean searches).

[jeffgman]

I am trying to use this code:

$szQuery="SELECT * FROM FORMS ";
if(isset($HTTP_POST_VARS['sname']))
$szQuery.=" WHERE Name Like '$sname%' ";
$szQuery.="ORDER BY NAME";

but, can't seem to get it to work. I went to mysql.com to find the "isset" command, but it is not listed. How do I find out more information about that command? It is never using the "Where" line.

[The_Chancer]

Use not only the Mysql.com - but www.php.net also... an absolute mine of information

All you have to do is put www.php.net/isset and .....

Oh - your query is wrong as well - should be

WHERE name LIKE '%$name%'

[jeffgman]

I keep forgetting about PHP.net. Thanks for that. I just was not able to get the isset command to work. But, I did get something to work. I now just have this at the top:

	$qry="SELECT * FROM FORMS ";
	if($sname != "") {
	$qry.="WHERE NAME LIKE '%$sname%' ";
	}
	$qry.="ORDER BY NAME";

and the query is working great. But, I have a different database which has a little bit different of code, which is not working. Here is the code:

	$qry="SELECT * FROM POLICY ";
	if($sname != "") {
	$qry.="WHERE TYPE = '$tname' and NAME LIKE '%$sname%' ";
	}
	$qry.="ORDER BY NAME";

The sname is the same as the other database and that is working fine. But, the tname variable is set using a form with a select. I have three settings for that field. I want the first one and default one to be a value of "" so it will select all. But, it does not like that. How can I tell my query command to be "Where TYPE = '' and NAME LIKE '%test%'" ?

[jeffgman]

I can't seem to get the following code to work the way I want it to:

$qry="SELECT * FROM POLICY ";
if($sname != "") {
$qry.="WHERE TYPE = '$tname' and NAME LIKE '%$sname%' ";
}
$qry.="ORDER BY NAME";

The $tname variable is a form with three select options. I want the default option to be a "" for all possible options. But, it above does not work when $tname = "". How can I make it so if $tname = "*" to search for anything in that field?

Thanks,
Jeff

[mcgruff]

If you are not filtering a query by name, just cut the WHERE clause from the query.

[jeffgman]

Okay, never thought of that. I can put it in another if statement. I will give this a go tomorrow. Thanks for pointing me in the right direction.

Jeff