Code for Searching a Social Network Site with PHP and MySQL

Searching a Social Network Site with PHP and MySQL - Part 2

Large Website of HTML Files

Forward: In this part of the series I explain the code of the PHP file used in Searching a Social Network Site with MySQL.

By: Chrysanthus Date Published: 13 Feb 2013

Introduction

This is part 2 of my series, Searching a Social Network Site with PHP and MySQL. In this part of the series I explain the code of the PHP file used in Searching a Social Network Site with MySQL. The code is my design. I gave you an overview of this topic in the previous part of the series. You should be reading the parts of this series in the order given; this is a continuation of the previous part. The interface code and the database query are in one PHP file. I gave you the client web page Form code in the previous part of the series and that will not be repeated here.

The PHP File Segments
There are three major code segments in the file. The first segment obtains the search phrase sent from the client. Remember, the PHP file is at the server. The next major segment, removes the non-keywords from the search phrase, places the keywords in a PHP array and then prepares the SQL query in string form. The third major segment connects to the database, sends the SQL query to the database, receives the result from the database and then prepares the HTML code for the result and send to the client’s browser. The result is the feedback seen by the client. The second and third code segments are further divided into smaller segments.

The First Code Segment
The first major code segment is quite short. It is:

    //obtain the search string
    $searchStr = $_POST['searchStr'];

It gets the search phrase from the HTTP POST variable and assigns to the ordinary PHP variable, $searchStr.

The Second Code Segment
The skeleton of the second major code segment is:

    if ($searchStr == "")
        {
            echo "<strong>Search string is empty!</strong>";
        }
    else
        {
            - - - - - -
        }

The if-condition checks if the search string was empty. If it was, the if-block informs the user so. Under this condition, the SQL Select query is not effectively form and no further information is developed and sent to the user. In your commercial project, you should use a better algorithm that will prevent the query from being sent to the database, when the search string is empty. My algorithm does not do that.

If the search string is not empty, the else part of the if-construct is executed. This else part consists of smaller code segments. The first small code segment here removes the non-keywords from the search string. So, the ultimate search phrase is void of non-keywords. The second small segment here places the keywords into an array. The third small segment here, forms the WHERE clause for the database SQL query. The fourth and last small code segment here prepares the complete SQL query string in PHP terms.

The small segment to remove the keywords from the search phrase is:

            //remove the non-keywords using regex
            $nonKeywords = array("about", "along", "among", "before", "after", "by", "for", "in", "from", "on", "of", "since", "to", "until", "till", "up", "with", "between", "the", "a", "an", "while", "whereas", "since", "as", "for", "therefore", "but", "and", "or", "I", "you", "he", "she", "we", "they", "me", "him", "her", "us", "them", "my", "your", "his", "her", "our", "their", "mine", "yours", "hers", "ours", "theirs", "some", "few", "many", "much", "little");

            $arrLength = count($nonKeywords); // no. of elements in the array
            $newSearchStr;  //search string after removing non-keywords

            for ($i=0; $i<$arrLength; ++$i)
                {
                    $newSearchStr = preg_replace("/\b$nonKeywords[$i]\b/", "", $searchStr);
                    $searchStr = $newSearchStr;
                }

It begins with an array of all non-keywords. I analyzed these non-keywords in the previous part of the series. Then the length of the array is determined and assigned to the variable, $arrLength. The variable to hold the search phrase after removing the non-keywords is declared as, $newSearchStr. The for-loop removes the non-keywords from the search phrase using the PHP preg_replace() regex (regular expression) function. If you have covered the prerequisite for this series, then understanding the code of this series should not be a problem.

The second small code segment is:

            //place each word of search string into an array
            $searchStrArr;
            preg_match_all("/\b\w+\b/i", $newSearchStr, &$searchStrArr);

At this point the search phrase no longer has the non-keywords. This segment has just two statements. The first one is the declaration of the array variable that will hold the different keywords of the search phrase; each keyword per array cell. The next statement uses the PHP preg_match_all() regex function to remove the keywords from the new search phrase and place them in the array. The regex here is, “/\b\w+\b/i” meaning any word - case insensitive.

The third small code segment is:

            //form the WHERE clause of SQL select statement
            $numberWords = count($searchStrArr[0]); //no. of keywords
            $firstKeyword = $searchStrArr[0][0];
            $whereStr = " WHERE (series.keywords rLike \"$firstKeyword\")";
            if ($numberWords > 1)
                {
                    for($j=1; $j<$numberWords; ++$j)
                        {
                            $temp = $searchStrArr[0][$j];
                            $whereStr .= " AND (series.keywords rLike \"$temp\")";
                        }
                }

It begins by counting the number of elements in the new search phrase array and assigning to the variable, $numberWords. The first keyword of the array is then assigned to the variable, $firstKeyword. The first part of the WHERE clause is determined and assigned to the variable, $whereStr – see detail explanation below.

If the new search phrase in the array consisted of more than one keyword, then the if-construct builds up the rest of the WHER clause, otherwise the WHERE clause would have only one keyword. The for-loop is what actually builds the rest of the WHERE clause and string-append to the variable, $whereStr. In the last statement of the for-loop, the variable $temp is used. This is because the PHP string would not accept the cell variable, $searchStrArr[0][$j] of the 2D PHP array.

The last small code segment is:

            //complete SQL select statement
            $selectStr = "select series.directoryName AS directoryName, series.htmlfilename AS filename, series.title1 AS title, series.forward AS forward, writers.firstname AS firstname FROM series inner join articles inner join writers on series.articleID=articles.articleID AND articles.writerID=writers.writerID" . $whereStr;

This statement assigns the complete database SQL query in PHP terms, to the variable, $selectStr. It string-appends the complete WHERE clause to the first part of the database SQL query.

The Database SQL Query
An example of the MySQL Select Query without PHP, for this project is:

select series.directoryName AS directoryName, series.htmlfilename AS filename, series.title1 AS title, series.forward AS forward, writers.firstname AS firstname FROM series inner join articles inner join writers on series.articleID=articles.articleID AND articles.writerID=writers.writerID WHERE
(series.keywords rLike "relationship") AND (series.keywords rLike "man") A
ND (series.keywords rLike "woman");

This Select query looks into the database for article data (rows) of articles that are based on “relationship between man and woman”. This quotation is the search phrase the user would type. The PHP file would remove the preposition, “between”, leaving you with an array consisting of the keywords, “relationship”, “man” and “woman”.

You should be able to understand the part of the Select query before the WHERE clause. The WHERE clause is:

    WHERE (series.keywords rLike "relationship") AND (series.keywords rLike "man") AND (series.keywords rLike "woman");

So, for each cell in the keywords column of the series table in the database, the query checks if “relationship” is present and if “man” is present and if “woman” is present. In the statement, “rLike” is of MySQL regular expression technique. The two same Boolean logic operators of “AND” are of SQL. Because of the ANDs, all three keywords, "relationship",  "man" and "woman" must be present in the cell. If any of these words is absent, the row is not selected. Remember, a cell of the keywords column of the series table, has article keywords separated by commas.

So, the more keywords the user has in his search phrase, the less the number of relevant rows selected.

I hope at this point, you can go back to the third small code segment above and understand how the WHERE clause is produced. Note, the user can type more than three keywords or less than three keywords. Above, there is an array that holds the keywords typed. The number of keywords in the search phrase determines the array length and so the length (number of ANDs) of the WHERE clause.

The Third Code Segment
The skeleton of the third major code segment of the PHP file is:

        //connect to DB and obtain resultset

        $link = mysql_connect('localhost', 'username', 'psswrd');

        if (!$link)
         {
                echo "<h3>Connection could not be made! Check email and/or password, and try again.</h3>";
         }
        else
         {

                //select database
                $db_selected = mysql_select_db('dbase', $link);

                - - - - - - - - - - -

         //for no result found
         if ($resultFound == false)
                {
                 echo "<h3>No result found.</h3>";
                }

                mysql_close($link);

         }

This segment begins by attempting the connection. If that fails it sends an error message to the user and no database search is done. The else part of the if-construct here is what does the search. At the end of the segment, if no rows match the search phrase (keywords) the user is told so. The connection (link) is then closed.

Note, the feedback page sent to the client’s browser is formed by the PHP file using a number of echo constructs.

The part of the code that does the searching and sends the resultset HTML list to the user’s browser consists of two small code segments.

The first small code segment is:

                $result = mysql_query($selectStr,$link);

                $resultFound = false;

There are two statements here. The first one sends the query to the database. The resultset (rows from the database) is assigned to the variable, $result. The second one declares and initializes the variable, $resultFound. If the resultset is empty, the value of this variable is false; if it is not empty, the value of the variable is true.

The second small code segment is:

                if (mysql_num_rows($result) > 0)
                 {
                        $resultFound = true;

                        echo "<h3>Data Found</h3>";

                        while ($row = mysql_fetch_assoc($result))
                         {
                                $directoryName = $row['directoryName'];
                                $filename = $row['filename'];
                                $title = $row['title'];
                                $forward = $row['forward'];
                                $firstname = $row['firstname'];

                                echo "<p>";
                                echo "<a href=\"http://www.somesite.com/$directoryName/$filename\"><strong>$title </strong></a><br>";
                                echo $forward;
                                echo "<br>";
                                echo "<i>by: $firstname</i>";
                                echo "</p>";

                         }

                        mysql_free_result($result);

                 }

This segment only operates if the resultset has some rows (more than zero). This is tested in the if-condition using the PHP function, mysql_num_rows(). If that is true, the first thing the if-block does is that it assigns the value of true to the variable, $resultFound, indicating that rows (result) have been found. The next line sends the heading of the result list to the user’s browser.

After that you have a while-loop. The while-loop fetches the rows of the resultset. The body of the while-loop is in two sections. The body of the while-loop deals with one resultset row at a time. The first section obtains from the row of the resultset, the directory name, file name, title, forward, and writer first name of the corresponding article. These values are assigned to variables. The next section uses the variables to prepare the HTML list item for the user and sends to the browser. Note that here, the title of the article is sent as a hyperlink. The href value of the hyperlink uses the address of the site, which the programmer should already know, the name of the subdirectory from $directoryName and the value of the filename from $firstname. Remember, a PHP variable such as $directoryName in a double quoted string expands (is replaced by) to its value.

At the end of the small code segment, the area of the computer memory holding the resultset is freed by the function call, mysql_free_result($result).

Regular Expression Techniques used
The PHP file uses regular expression technique in three places. In two of these places PHP regular expression functions are use. In the first place, a function is used to remove the non-keywords from the search phrase. In the second place, the keywords of the new search phrase are placed in an array by a function. In the third place, PHP “wraps” a MySQL regular expression; this happened at the formation of the MySQL Select Query.

Database to be created
As you can see from this and the previous parts of the tutorial series, the database has to be created. For each HTML article, rows must be inserted into the series, articles, and writers tables. So, you or someone else has to create the database.

Wow, impressive, and not as difficult as it seemed, prerequisite haven accomplished! Time to take a break. See you in the next part of the series.

Chrys

Broad Network

Related Articles

Code for Searching a Social Network Site with PHP and MySQL

Searching a Social Network Site with PHP and MySQL - Part 2

Large Website of HTML Files

Introduction

Related Links

Comments