Home arrow MySQL arrow Page 5 - Developing A Site Search Engine With PHP And MySQL
MYSQL

Developing A Site Search Engine With PHP And MySQL


The key to developing a successful web site is content. Sure, your site can be packed with 10,000 50-page articles, but what good are these articles if your visitors can't find what they're after? In this article Mitchell shows us how to create a site search engine with PHP and MySQL. He develops the MySQL database and PHP script that will allow us to enter several keywords to search dynamic content.

Author Info:
By: Mitchell Harper
Rating: 4 stars4 stars4 stars4 stars4 stars / 145
February 16, 2002
TABLE OF CONTENTS:
  1. · Developing A Site Search Engine With PHP And MySQL
  2. · Search Engine Theory
  3. · Creating the database
  4. · The searchdocs.php script
  5. · The doSearch function
  6. · Testing our search script
  7. · Conclusion

print this article
SEARCH DEVARTICLES

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>";

}

else


Our 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:

When no keywords are entered, this page is display

{

// 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.
blog comments powered by Disqus
MYSQL ARTICLES

- MySQL and BLOBs
- Two Lessons in ASP and MySQL
- Lord Of The Strings Part 2
- Lord Of The Strings Part 1
- Importing Data into MySQL with Navicat
- Building a Sustainable Web Site
- Creating An Online Photo Album with PHP and ...
- Creating An Online Photo Album with PHP and ...
- PhpED 3.2 More Features Than You Can Poke ...
- Creating An Online Photo Album with PHP and ...
- Creating An Online Photo Album with PHP and ...
- Security and Sessions in PHP
- Setup Your Personal Reminder System Using PHP
- Create a IP-Country Database Using PERL and ...
- Developing a Dynamic Document Search in PHP ...

Watch our Tech Videos 
Dev Articles Forums 
 RSS  Articles
 RSS  Forums
 RSS  All Feeds
Write For Us 
Weekly Newsletter
 
Developer Updates  
Free Website Content 
Contact Us 
Site Map 
Privacy Policy 
Support 

Developer Shed Affiliates

 




© 2003-2017 by Developer Shed. All rights reserved. DS Cluster - Follow our Sitemap
Popular Web Development Topics
All Web Development Tutorials