This is my first tutorial. I hope some of you find it useful.
In this tutorial you will make 3 pages:
config.php - contains database information
functions.php - contains search functions
search.php - the page that searches.
Please note that words after two slashes, //, are comments. Meant to help you understand the process.
config.php
<?php
//config.php
$host="localhost";
$user="db_user"; //Database user
$password="db_pw"; //DB User's Password
$conn = mysql_connect($host,$user,$password)
or die(mysql_error());
mysql_select_db("d_base", $conn) //d_base is the database name
or die(mysql_error());
?>
Now that you can connect to your database you will need functions that open it up and look through it. For this tutorial you will make 3 functions:
searchPhrase() - Returns results that contain an exact phrase
searchAny() - Returns results with any of the words in the phrase
searchAll() - Returns results with all the words in the phrase
<?php
//functions.php
function searchPhrase($term)
{
$sql = "SELECT * FROM messages WHERE body LIKE '%$term%'"; // Look into the table messages where the column body contains the search term.
$result = mysql_query($sql) or die(mysql_error());
while ($row = mysql_fetch_assoc($result))
{
echo $row['subject'] , ' : ' , $row['body'] , '<hr><br><br>'; //Print out "subject : body"
}
}
?>
That is the simplest function. The next is a bit more complex.
<?php
function searchAll($term)
{
$search_array = explode(" " , $term); //Makes an array(basically a matrix) of the search term splitting it between spaces.
$size_search = sizeof($search_array); //Gets the number of units in the array
for($i = 0; $i < $size_search; $i++)
{
$search_array[$i] = "'%" . $search_array[$i] . "%'"; //Creates proper syntax for a sql statement
}
$search_term = implode(" ", $search_array); //Puts the search term back into a single string
$sql = "SELECT * FROM smf_messages WHERE body LIKE $search_term";
$result = mysql_query($sql) or die(mysql_error());
while ($row = mysql_fetch_assoc($result))
{
echo $row['subject'] , ' : ' , $row['body'] , '<hr><br><br>';
}
}
?>
Now the 3rd and most difficult function.
<?php
function searchAny($term)
{
$search_array = explode(" " , $term);
$size_search = sizeof($search_array);
for($i = 0; $i < $size_search; $i++)
{
if($i == ($size_search - 1))
$search_array[$i] = "'%" . $search_array[$i] . "%'"; //Prevents OR from being added to the last term
else
$search_array[$i] = "'%" . $search_array[$i] . "%'" . " OR"; //Adds an OR to the end of each term.
}
$search_term = implode(" ", $search_array);
$sql = "SELECT * FROM smf_messages WHERE body LIKE $search_term";
$result = mysql_query($sql) or die(mysql_error());
while ($row = mysql_fetch_assoc($result))
{
echo $row['subject'] , ' : ' , $row['body'] , '<hr><br><BR><BR>';
}
}
?>
Now by this time I am sure you are wondering when you will actually be able to search for something.
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" />
<title>MySQL Search Page</title>
</head>
<body>
<p>
<?Php
include('config.php'); //Gives this page access to the database.
include('functions.php'); //Gives this page access to the functions you just made.
if($_POST) //Makes sure to execute the code AFTER the form has been submitted.
{
switch($_POST['req']) //Depending on what the user selects, different functions will be executed.
{
case "All": echo searchAll($_POST['term']); break;
case "Any": echo searchAny($_POST['term']); break;
case "Phrase": echo searchPhrase($_POST['term']); break;
}
}
?>
</p>
<form id="form1" name="form1" method="post" action="search.php">
<p>
<input name="term" type="text" id="term" />
</p>
<p>
<label>
<input type="radio" name="req" value="All" />
All</label>
<br />
<label>
<input name="req" type="radio" value="Any" checked="checked" />
Any</label>
<br />
<label>
<input type="radio" name="req" value="Phrase" />
Phrase</label>
<br />
<br />
<input type="submit" name="Submit" value="Submit" />
</p>
</form>
<p> </p>
</body>
</html>
Bet that didn't make much sense!
and thats it.... If you have any questions, I'll try my best to answer them....
