Developing A Site Search Engine With PHP And MySQL - The doSearch function
(Page 5 of 7 )
The doSearch function is responsible for querying the database and returning those articles that match the keywords entered into the text box by the user. Because the doSearch function is a tad long, we will examine it piece by piece. Remember that you can download the entire source code for this project from the support material link on the conclusions page.
function doSearch($search_keywords)
{
$arrWords = explode(" ", $search_keywords);
if(sizeof($arrWords) == 0 || $search_keywords == "")
{
echo "You didn't enter any keywords<br>";
echo "<a href='searchdocs.php'>Go Back</a>";
}
elseOur doSearch function starts by splitting the keywords into an array using the split function. Whenever the split function sees a space in the $search_keywords variable, it will add the value up to that space as a new index in the $arrWords array, so if we had
cat dog horse
... then the split function would return an array with three indexes. The first would contain the string "cat", the second "dog", and the third "horse".
If the user didn't enter any search terms, we tell them so, and provide them with a link back to the search form:
{
// Connect to the database
$dServer = "localhost";
$dDb = "content";
$dUser = "admin";
$dPass = "password";
$s = @mysql_connect($dServer, $dUser, $dPass)
or die("Couldn't connect to database server");
@mysql_select_db($dDb, $s)
or die("Couldn't connect to database");If the user entered at least one keyword, then we connect to the database server as well as our content database. All of the database variables are defined as variables, which makes our script look cleaner and appear more logically.
for($i = 0; $i < sizeof($arrWords); $i++)
{
$query = "select articleIds from searchWords where word = '{$arrWords[$i]}'";
$result = mysql_query($query);We've reached the main loop of our doSearch function. This main for loop uses a select statement to retrieve the articleIds from the searchWords table for every record that matches a keyword entered by the user. In our example, if I entered "mysql" as the keyword, then articles with articleId 1 and 2 would be returned, because the record in the searchWords table looks like this:
+--------+--------+------------+
| wordId | word | articleIds |
+--------+--------+------------+
| 1 | MySQL | 1,2 |
+--------+--------+------------+
if(mysql_num_rows($result) > 0)
{
// Get the id's of the articles
$row = mysql_fetch_array($result);
$arrIds = explode(",", $row[0]);
$arrWhere = implode(" OR articleId = ", $arrIds);If the keyword exists in the searchWords table, then the articleIds field is split into the array $arrIds using the explode function. Next, we use the opposite of explode, which is implode, to merge the contents of an array into a string. The $arrWhere example will be used to generate the where part of an SQL query to get the details of each matching article from the articles table. The $arrWhere variable would look like this if we searched for "mysql":
1 OR articleId = 2
At this point we have the where part of a MySQL query, so we append it to a select statement which will return the articleId, title, and first one-hundred characters of the content field from the articles table:
$aQuery = "select articleId, title, left(content, 100) as summary from articles where articleId = " . $arrWhere;
$aResult = mysql_query($aQuery);
$count = 0;Each article returned from the select query is added to an associative array called $articles:
$articles = array();
if(mysql_num_rows($aResult) > 0)
{
while($aRow = mysql_fetch_array($aResult))
{
$articles[$count] = array (
"articleId" => $aRow["articleId"],
"title" => $aRow["title"],
"summary" => $aRow["summary"]
);
$count++;
}
}At this point, the $articles associative array will contain all of the articles returned from the search.
if(isset($articles))
{
$articles = array_unique($articles);
echo "<h1>" . sizeof($articles);
echo (sizeof($articles) == 1 ? " article" : " articles");
echo " found:</h1>";
foreach($articles as $a => $value)
{
?>
<a href="article.php?articleId=<?php echo $articles[$a]["articleId"]; ?>">
<b><u><?php echo $articles[$a]["title"]; ?></u></b>
</a>
<br><?php echo $articles[$a]["summary"] . "..."; ?>
<br>
<a href="article.php?articleId=<?php echo $articles[$a]; ?>">
http://www.mysite.com/article.php?articleId=<?php echo $articles[$a]["articleId"]; ?>
</a>
<br><br>
<?php
}
}The foreach loop above runs through each record in the $articles associative array and displays the results in the browser. The array_unique function strips any duplicate indexes from the $articles array, ensuring that no duplicate records will be shown in our search results.
The URL for each article is http://www.mysite.com/article.php?articleId=xxx, where xxx is the articleId of the article. The details for displaying the contents of each article is out of the scope of this article.
else
{
echo "No results found for '$search_keywords'<br>";
echo "<a href='searchdocs.php'>Go Back</a>";
}
}
}
}
?>If no results were found for the keywords entered by the user, then we tell them so, and provide them with a link back to the search records.
Next: Testing our search script >>
More MySQL Articles
More By Mitchell Harper
/ 91 
