PHP Tutorials

Welcome to Dream.In.Code
	Become an Expert! Join 300,419 Programmers for FREE! Get instant access to thousands of experts, tutorials, code snippets, and more! There are 1,525 people online right now. Registration is fast and FREE... Join Now! Chat LIVE With a Expert

Building an article Search Engine with MySql

Building an article Search Engine with MySql

Options

no2pencil

14 Jan, 2009 - 01:01 AM

Post #1

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.

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

Setup.php

CODE

<?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

CODE

$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.

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.

CODE

/* 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

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

CODE

<?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>
                        
                        <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

CODE

<?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.

CODE

     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