Here's a simple search in MySQL and PHP.
Cheers
M;-)
Search.php3:
<html>
<head><title>Searching the Guest Book</title>
</head>
<body bgcolor=#ffffff>
<h1>Searching the Database</h1>
<form method="post" action="srch.php3">
<table width=90% align=center>
<tr><td>search for:</td><td><input type=text name='search' size=60
maxlength=255></td></tr>
<td></td><td><input type=submit></td></tr>
</table>
</form>
<?php include ('links.x');?>
</body>
</html>
Srch.php3:
<?
if ($search) // perform search only if a string was entered.
{
mysql_connect() or die ("Problem connecting to Database");
$query = "select * from visitors WHERE Name='$search'";
$result = mysql_db_query("guest_book", $query);
if ($result)
{
echo "Here are the results:<br><br>";
echo "<table width=90% align=center border=1><tr>
<td align=center bgcolor=#00FFFF>Visit time and date</td>
<td align=center bgcolor=#00FFFF>User Name</td>
<td align=center bgcolor=#00FFFF>Last Name</td>
<td align=center bgcolor=#00FFFF>Email</td>
</tr>";
while ($r = mysql_fetch_array($result)) { // Begin while
$ts = $r["TimeStamp"];
$name = $r["Name"];
$last = $r["Last"];
$email = $r["email"];
$comment = $r["comment"];
echo "<tr>
<td>$ts</td>
<td>$name</td>
<td>$last</td>
<td>$email</td></tr>
<tr> <td colspan=4 bgcolor=\"#ffffa0\">$comment</td>
</tr>";
} // end while
echo "</table>";
} else { echo "problems...."; }
} else {
echo "Search string is empty. <br> Go back and type a string to search";
}
include ('links.x');
?>
Some explanations. This scripts performs the following tasks:
Checks whether a string was entered.
Retrieves all the records that match the search string.
Prints all the retrieved records in a formatted table.
Clearing all the mumbo jumbo, the actual code that we need to work on is:
$query = "select * from visitors WHERE Name='$search'";
Yes, this line does all the work. We will play with it a bit later.
Ok, this query gets all the records where the Name field is equal to the string
search. Please note that an exact match is needed.
Lets assume we want to search for a partial string match (i.e. where the search string
appears in the filed but as part of the string and not an exact match). We will have
to modify the script as follows:
$srch="%".$search."%";
$query = "select * from visitors WHERE Name LIKE' $srch'";
The LIKE comparison argument will return '1' if the Name field has a partial value of
$search. Note that I modified $search and added "%" on both ends. This allows to
search for the search to ignore the leading characters and the characters following
the search string.
Ok, now lets assume we want to search all the field of the table and not only the Name
field. In order to do that we need to choose the records with Name LIKE $srch or Last
LIKE $srch etc. The translation to MySQL query is:
$query = "select * from visitors WHERE Name LIKE '$srch' || Last LIKE '$srch' || email
LIKE '$srch' || comment LIKE '$srch'";
The complete srch.php3 script top to bottom should look like:
<?
if ($search) // perform search only if a string was entered.
{
mysql_connect() or die ("Problem connecting to DataBase");
$srch="%".$search."%";
$query = "select * from visitors WHERE Name LIKE '$srch' || Last LIKE '$srch' || email
LIKE '$srch' || comment LIKE '$srch'";
$result = mysql_db_query("guest_book", $query);
if ($result)
{
echo "Here are the results:<br><br>";
echo "<table width=90% align=center border=1><tr>
<td align=center bgcolor=#00FFFF>Visit time and date</td>
<td align=center bgcolor=#00FFFF>User Name</td>
<td align=center bgcolor=#00FFFF>Last Name</td>
<td align=center bgcolor=#00FFFF>Email</td>
</tr>";
while ($r = mysql_fetch_array($result)) { // Begin while
$ts = $r["TimeStamp"];
$name = $r["Name"];
$last = $r["Last"];
$email = $r["email"];
$comment = $r["comment"];
echo "<tr>
<td>$ts</td>
<td>$name</td>
<td>$last</td>
<td>$email</td></tr>
<tr> <td colspan=4 bgcolor=\"#ffffa0\">$comment</td>
</tr>";
} // end while
echo "</table>";
} else { echo "problems...."; }
} else {
echo "Search string is empty. <br> Go back and type a string to search";
}
include ('links.x');
?>
-----Original Message-----
FROM: [EMAIL PROTECTED]
TO: [EMAIL PROTECTED]
DATE: Thu 7/5/01 10:13
SUBJECT: web search form for a mysql database
I have mysql 3.23.38, php4.03, apache1.3.14. I have a small database of
dealer bulletins.
I have created a web page to allow easy input of new bulletins into the
database, and a second
web page to display those bulletins. On this page are links to allow
displaying the bulletins
in several differant sort orders. This all works just fine. I was asked to
also add a search field.
I have searched the online docs but cannot find an answer to this: how do I
get a user inputted
reqest into the mysql query string? Here's what I have tried most recently:
(lots of snipped code)
<form action="bulletin_sorter.php" name="form1">
Enter a search word(s): <input type="text" name="body">
<INPUT TYPE="hidden" NAME="orderby" VALUE="request"> </form>
(lots of snippage)
elseif
($orderby == 'request'):
$sql = "select * from bulletin where match (bulletin_subject,body) against '$request'";
(lots more snippage)
I have tried variations on the above query statement, none of which would work. I can
do this
without any problem from the mysql command line by entering the word I am interested in
searching on. The problem seems to be with the part after against, just how do I get
the user
input data into that field?
Chip Wiegand
Computer Services
www.simradusa.com
[EMAIL PROTECTED]
Simrad, Inc
Lynnwood, WA
425-712-1138
"There is no reason anyone would want a computer in their home."
--Ken Olson, president, chairman and founder of Digital Equipment Corp.,
1977
(-- Then why do I have nine? Somebody help me!)
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
