0 Replies - 6514 Views - Last Post: 14 January 2009 - 02:01 AM

#1 no2pencil  Icon User is offline

  • Professor Snuggly Pants
  • member icon

Reputation: 5860
  • View blog
  • Posts: 28,737
  • Joined: 10-May 07

Building an article Search Engine with MySql

Posted 14 January 2009 - 02:01 AM

This tutorial uses the deprecated mysql extension (as of PHP 5.5.0) which should not be used for writing new code as it will be removed in the future. The mysqli (MySQL Improved) or PDO extension should be used instead.

Nevertheless, it has been decided to keep this tutorial available until such time as it has been either updated or re-written. In the meantime, if you wish to take the tutorial, then it is recommended that you change the mysql-extension code to use mysqli or PDO. (If you do this successfully then perhaps you might consider submitting a revised tutorial, contributing to dream.in.code and earning some Kudos!)




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

This post has been edited by andrewsw: 30 January 2015 - 12:19 PM


Is This A Good Question/Topic? 0
  • +

Page 1 of 1