Page 1 of 1

Building an article Search Engine with MySql Rate Topic: ***** 1 Votes

#1 no2pencil  Icon User is online

  • Toubabo Koomi
  • member icon

Reputation: 5191
  • View blog
  • Posts: 26,901
  • Joined: 10-May 07

Posted 14 January 2009 - 02:01 AM

Building an article Search Engine with MySql

In this tutorial, we will build a 3 page Search Engine. It will offer an interface to add keywords & point them to relevant articles, as well as a smaller admin page for creating & showing the entire database.

Setup seems like a good place to start.

Attached Image

1st, we need to get connected to our database. This is standard database php connection code, nothing fancy.

Setup.php
<?php
$db_host="localhost";
$db_user="your_user";
$db_password="password";
$db="content";

$cid = mysql_connect($db_host,$db_user,$db_password);



From now on we can check for the existance of $cid to see if we do in fact have our database connection.

With our connection setup, lets go ahead & check for any values that were passed in.

Table : Database Table
Column : This will be either the keyword (search word) or the Article, depending on which form they fill out
Row : Row points to the key, wordID or ArticleID, of the table



$err=0;
if(empty($_GET['table'])) {
	$err=1;
}
if(empty($_GET['column'])) {
	$err=1;
}
if(empty($_GET['row'])) {
	$err=1;
}
if($err==0) {
	 Delete_Record();
}



Here is our functions section. Basically we have four simple function.

Attached Image

show_all : This shows all keywords & articles (pictured above)
Create_New : This creates our database
Delete_Record : This deletes the selected record
Delete_ : This deletes the entire database. It would be wise to simply remove this once you are happy with the setup. :)

/* Define Functions */
function Show_all() {
	$db="content";
	if(mysql_select_db($db) == false) {
	   printf("Manually trying to use %s<br>",$db);
	   $query2 = "USE $db;";
	   mysql_query($query2) or die(mysql_error());
	}
	$query = "select * from searchWords";
	$result = mysql_query($query) or die(mysql_error());
	while($row = mysql_fetch_row($result)) {
		echo "<form action=\"setup_content_search.php\" method=\"GET\">";
		echo "<p>$row[0] : $row[1] : $row[2] : $row[3] </p>";
		echo "<input type=hidden name=table value=searchWords>";
		echo "<input type=hidden name=column value=articleIds>";
		echo "<input type=hidden name=row value=".$row[0].">";
		echo "<input type=hidden name=action value=Delete>";
		echo "<input type=submit value=\"Delete\"></form>";
	}
	echo "<hr>";
	$query = "select articleId, title, content from articles";
	$result = mysql_query($query) or die(mysql_error());
	while($row = mysql_fetch_row($result)) {
		echo "<form action=\"setup_content_search.php\" method=\"GET\">";
		echo "<p>$row[0] : $row[1] : $row[2] : $row[3]</p>";
		echo "<input type=hidden name=table value=articles>";
		echo "<input type=hidden name=column value=articleID>";
		echo "<input type=hidden name=row value=".$row[0].">";
		echo "<input type=hidden name=action value=Delete>";
		echo "<input type=submit value=\"Delete\"></form>";
	}
}

function Create_New() {
	if($cid) {
		$db="content";
	}
	$query = "create database $db;";
	$result = mysql_query($query) or die(mysql_error());
	$query = "use $db;";
	$result = mysql_query($query) or die(mysql_error());
	$query = "create table articles (articleId int auto_increment,title varchar(50) not null,content text,primary key(articleId),url varchar(255), unique id(articleId));";
	$result = mysql_query($query) or die(mysql_error());
	$query = "create table searchWords (wordId int auto_increment,word varchar(50),articleIds varchar(255),primary key(wordId),unique id(wordId));";
	$result = mysql_query($query) or die(mysql_error());
	
	echo "Completed<br>$db was successfully created.";
}

function Delete_Record() {
	if(!$cid) {
		$db_host="localhost";
		$db_user="username";
		$db_password="pass";
		$db="content";
		
		$cid = mysql_connect($db_host,$db_user,$db_password);
	}
	$db="content";
	if(mysql_select_db($db) == false) {
		printf("Manually trying to use %s<br>",$db);
		$query2 = "USE $db;";
		mysql_query($query2) or die(mysql_error());
	}
	
	$query = "DELETE FROM ".$_GET['table']." WHERE ".$_GET['column']." = ".$_GET['row'].";";
	$result = mysql_query($query); // or die(mysql_error());
	if(!$result) {
		echo $query . " Failed!<br>";
		die(mysql_error());
	}
	
	echo "Completed<br>".$_GET['row']." was successfully removed from ".$_GET['table'];
}
	
function Delete_() {
	if($cid) {
		$db="content";
	}
	if(mysql_select_db($db) == false) {
		printf("Manually trying to use %s<br>",$db);
		$query2 = "USE $db;";
		mysql_query($query2) or die(mysql_error());
	}
	
	$query = "drop DATABASE $db;";
	$result = mysql_query($query); // or die(mysql_error());
	if(!$result) {
		echo $query . " Failed!<br>";
		die(mysql_error());
	}
	
	echo "Completed<br>$db was successfully removed.";
}
?>



Add to search

Attached Image

Our add to search page will function as an admin panel to allow you to add both keywords & articles to the database. Once added, you can pass show_all to the previous page to view all entries.

Add.php
<?php

/* Start From Here */

$Keyword=strip_tags($_GET['Keyword']);
$Article=strip_tags($_GET['Article']);
$URL=strip_tags($_GET['URL']);
$Title=strip_tags($_GET['Title']);
$err=0;
$use=1;
if(empty($Keyword)) {
	$err=1;
}
if(empty($Article)) {
	$err=1;
}
if(!empty($Title)) {
	$use=2;
	$err=0;
}
if($err==0) {
	if($use==1) { // By default we will check the keyword
		$query = "insert into searchWords (word, articleIds) values('$Keyword', '$Article');";
		$result = mysql_query($query) or die(mysql_error());
	
	}
	if($use==2) { // If Title has a value, then we are using article, & not keyword
		$query = "insert into articles (title, content, url) values('$Title','$Article','$URL');";
		$result = mysql_query($query) or die(mysql_error());
	}
	echo $Keyword." added";
}
else {
	echo '
				<form id="Keyword" method="GET" action="add_to_search.php">
					<div>
						<p>KeyWord : <input type="text" name="Keyword" id="s" size="15" value="" /></p>
						<p>Article :	<input type="text" name="Article" id="s" size="15" value="" /></p>
						<!-- <p>Page :	<input type="text" name="URL" id="s" size="15" value="" /></p> -->
						<input type="submit" value="Insert Keyword" />
					</div>
				</form>
				<hr>
				<form id="Article" method="GET" action="add_to_search.php">
					<div>
						<p>Title :	<input type="text" name="Title" id="s" size="15" value="" /></p>
						<p>Article :	<input type="text" name="Article" id="s" size="15" value="" /></p>
						<p>Page :	<input type="text" name="URL" id="s" size="15" value="" /></p>
						<input type="submit" value="Insert Article" />
					</div>
				</form>
	';
}

?>



Lastly is the search page itself.

Search.php
<?php
$err_text_code=0;

/* Define functions */
function doSearch($search_keywords) {
  $arrWords = explode(" ", $search_keywords);
  $db_host="localhost";
  $db_user="username";
  $db_password="pass";
  $db="content";

  $cid = mysql_connect($db_host,$db_user,$db_password);
  if($cid) {
	if(mysql_select_db($db) == false) {
	   printf("Manually trying to use %s<br>",$db);
	   $query = "USE $db;";
	   mysql_query($query) or die(mysql_error());
	}



Once we've got our connection secured we will loop through the arrwords variable with a for loop, checking the database for entries in the searchwords table.

	 for($i=0; $i<sizeof($arrWords); $i++) {
		$query = "select articleIds from searchWords where word = '{$arrWords[$i]}'";
		$result = mysql_query($query) or die(mysql_error());
		$num_rows = mysql_num_rows($result);
		if($num_rows > 0) {
		   $row = mysql_fetch_array($result);
		   $arrIds = explode(",", $row[0]);
		   $arrWhere = implode(" OR articleId = ", $arrIds);
		   $aQuery = "select articleId, title, left(content, 100) as summary, url from articles where articleId = " . $arrWhere;
		   $aResult = mysql_query($aQuery);
		   $count = 0;
		   $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"],
			  "url" => $aRow["url"]
			   );
			   $count++;
			 }
		  }
		  if(isset($articles)) {
			$err_text_code=1;
			$articles = array_unique($articles);
			echo "<h1>" . sizeof($articles);
			echo (sizeof($articles) == 1 ? " result" : " results");
			echo " found:</h1>";
			foreach($articles as $a => $value) {
		$err_text.='<a href="'.$articles[$a]["url"].'">';		// print a link to the url
		$err_text.='<b><u>'.$articles[$a]["title"].'</u></b>';   // Bold the Title
		$err_text.='</a><br>'.$articles[$a]["summary"].'...';	// 1st 100 words of the article
		$err_text.='<br><a href="?articleId='.$articles[$a].'">';
		$err_text.='</a><br><br>';
		echo $err_text;										 // Show the link & article title
		  }
	   }
	 }
	 else {
		 $err_text_code=2;
		 $err_text="<h1>None!</h1>"; // No results found in the database
		 echo $err_text;
	 }
	}
  }
}
?>



** The Search Engine code can be referenced from here, whereas all of the admin utilities were added by myself

Is This A Good Question/Topic? 0
  • +

Page 1 of 1