Overview for Searching a Social Network Site with PHP and MySQL
Searching a Social Network Site with PHP and MySQL – Part 1
Large Website of HTML Files
Forward: In this part of the series, I give you an overview for Searching a Social Network Site with PHP and MySQL.
By: Chrysanthus Date Published: 13 Feb 2013
Introduction
Prerequisite
Here, I talk about what you should have studied in order to understand this series. This series is part of my Web Development Course with PHP and MySQL. Click the link, “Web Development Course” to know how this series fits into the course and to know what you should have already covered.
User Requirements
- The user is in his house or office with his browser connected to the Internet.
- There is a website with a database at a web server.
- The website is a social network site consisting mainly of articles in HTML format.
- When the user wants to search for a particular kind of articles, he would type at his browser, the search phrase in a form text field in any of the web pages of the site and then clicks the Search Button.
- The search phrase goes to the server, and after a while, the user would have a return web page indicating whether or not any articles where found.
- If articles where found, he would see a list of possible articles to his interest.
- When he clicks the title of an article found, the actual article would be downloaded from server and displayed at his browser.
Technical Requirements
- The web server has a home directory with sub directories of the home directory.
- In the home directory, you have the index HTML file and a few other HTML files.
- Each subdirectory to the home directory belongs to a writer of articles and the subdirectory has all the articles of that particular writer.
- The articles are HTML files.
- Each HTML file in the home page and each HTML file in a subdirectory has a form text field in which the user can type in a new search phrase to search for a particular kind of articles.
- The subdirectory name, article HTML file name, title of article, first name of writer, keywords and the forward for each article are kept in the database.
- The search process at the server, searches the database and not the HTML article files.
- When relevant data related to the articles are found, hyperlinks to the articles are formed with associated information and placed in a web page and sent to the user’s browser.
- The link the user can click to obtain an article has the title of the article as link text.
- The client script is ECMAScript, the server script is PHP script and the database is MySQL.
At the client computer, there is a web page with an HTML Form having one text field and one button with the title, “Search This Site”. The form has a supporting ECMAScript. At the server, you have a PHP script. This PHP script receives the search phrase, searches the database, prepares the feedback page and sends the feedback page to the user’s browser. So far as the search is concerned, the database has three tables, which are called, series, articles and writers.
The series table has the following columns: a column of subdirectory names, a column of article HTML file names, a column of titles for articles, a column of forwards for each article and a column of keywords for each article. In the column of keywords, each cell has a number of keywords for the corresponding article. The writers table has a column for the first name of writers. I talk about the articles table and the other two tables in more detail below.
The Search Form
The search form is on each article (HTML file) and on each of the HTML files of the home directory. The controls in the form are the text field to type in the search phrase and the button to send the search phrase to the server. The code is:
<form action='searchThisSite.php' method='post' style='display:inline; float:right'>
<span id='S1' style='display:none'><b>Please Wait . . . </b></span>
<input type='search' size='40' name='searchStr'>
<button type='submit' onclick="document.getElementById('S1').style.display = 'inline'">Search this Site</button>
</form>
There are actually three HTML elements in the form. You have the span element, the input text element and the button element. The span element has the content, “Please Wait . . .” This span element is displayed only when the user has clicked the button, which is the search button. Note that the value of the control type attribute for the text field is ‘search’ and not ‘text’. If you type this today, some browsers will give you a better looking text field, better than if you typed, “text”.
The ECMAScript is the button’s onclick event’s code. This very short script displays the span element, and the user sees, “Please Wait . . .” when the button is clicked. The form uses the HTTP POST method to send the search phrase to the server. At the server, the search phrase is received by the PHP file, searchThisSite.php. You can call a PHP file in this context, a PHP script or server script. Above is all the code for the form.
The actual database has many tables, but only three of them are used for the search. The three tables are called, series, articles and writers. The table notation for the series table is:
series(articleID, title1, keywords, forward, directoryName, htmlfilename, …)
The table notation for the articles table is:
articles(articleID, writerID, …)
The table notation for the writers table is:
writers(writerID, firstname, ...)
In this application, the articles table serves to link the series table to the writers table (with the ON clause of the SQL select query). The use of the writers table is to obtain the first name of the writer. So the query will know the articleID from the series table. From there it will use the articleID to know the writerID from the articles table. From there it will use the writerID to know (obtain) the first name of the writer in the writers table. The data we want are in the series and writers table.
The PHP Script
The PHP script is at the server. It receives the search phrase and removes the non-keywords with regular expression techniques. It places the keywords of the phrase in an array still using regular expression techniques. The script goes on to form the MySQL Select query in PHP terms. Then it sends the query to the MySQL database.
The purpose of the query is to obtain a resultset for series.directoryName, series.htmlfilename, series.title1, series.forward, and writers.firstname. From the selected rows of these headings, the result list in HTML to be displayed to the user’s browser is produced still by the PHP script. After formation, this same PHP script sends the result HTML list to the browser.
The same PHP script is placed in the home directory and in each subdirectory belonging to a writer. This is because the value of the form action attribute above, does not have a preceding URL path.
The PHP file uses the PHP regular expression in its normal code, and the MySQL regular expression in the MySQL Query statement, written as a PHP string (PHP terms).
PHP uses its regular expression techniques to place the keywords in a PHP array. In the query string formed by PHP, MySQL’s regular expression is used (typed) to search all the cells of the keywords column of the series table. Each cell of the keywords column has a number of keywords for the corresponding article; these cell keywords are separated by commas. For each cell, the query checks if all the keywords of the search phrase are found in the cell. If all the keywords are not found, the row and corresponding article are not selected.
This means that the more keywords in the search phrase the less the number of articles (article rows) selected. The query uses a combination of MySQL regular expression technique and SQL logic to achieve this – see details in the next part of the series.
Non-Keywords
The server PHP script (file) has to remove the non-keywords from the search phrase. So, what are non-keywords. In this section I give you the English non-keywords that I know, in the categories that I know: you have the preposition category, the grammatical article category and a few others.
Prepositions
Prepositions that I know for now are: about, along, among, before, after, by, for, in, from, on, of, since, to until, till, up, with, between. Some of these prepositions convey more meaning than the others, but not as much meaning as the other grammatical words (nouns, adjectives, etc.).
Grammatical Articles
The grammatical articles are: an, a, the.
Conjunctions
Conjunctions that I know are: while, whereas, since, as, for, therefore, but, and, or.
Pronouns
Pronouns that I know for now are: I, you, he, she, we, they, me, him, her, us, them, my, your, his, her, our, their, mine, yours, hers, ours, theirs.
Words of Quantity
Words of quantity that I know for now are: some, few, many, much, little
And so, the PHP script at the server removes all the prepositions, articles, conjunctions, pronouns and words of quantity from the search phrase. These non-keywords should not be searched for, because in an article, they are many and do not indicate to the user what the article is about.
Wow! At this point I guess you are hungry to see the code. You already have the code for the form at the client web page. You next have to see the PHP code and the database query. Rendezvous in the next part of the series.
Chrys
Related Links
Major in Website DesignWeb Development Course
PHP Course
NEXT