home > Internet > Questions for a man search php view. How to make a multi-word search algorithm in PHP from a database

Questions for a man search php view. How to make a multi-word search algorithm in PHP from a database

I have already been asked several times to write an article on how to implement search on a website using PHP. This is not an easy task, I would even say very difficult, since there are a huge number of nuances and obstacles. In this article I will analyze the search algorithm on the site.

Let's assume that our website has a lot of different materials (articles, news, notes, etc.). All this stuff is in the database. And our task is to implement search on the site. The simplest algorithm is the following:

Create an HTML form with a search bar and a "Submit" button. Users will enter a search query in the text field and then click on the button.

Receive a search query (usually transmitted GET method, but sometimes POST is also used), and also, in order to protect against XSS, pass it through the htmlspecialchars() function.

Make a selection from the corresponding tables (with articles, news, notes, etc.) of those records that contain the search query. I show an example SQL query for such cases: SELECT * FROM articles WHERE `text_article` LIKE %search% Accordingly, the search string is substituted for search.

Having received the records, in the right form We display them, preferably by relevance. For example, I did this on my website: where there are the most matches, that article is the most relevant, therefore, I put it first. Most likely, this method of assessing relevance will also suit you.

Many of you will say that there is nothing complicated here. And they will be partly right, however, let’s look at this example of a search string: “I’m looking for this text.” The question arises: " What exactly are you looking for?". Either the exact occurrence of the text "I'm looking for this text" is being searched. Or, perhaps, a text is being searched where all three words are present, but which may not follow each other. Or, perhaps, a text is being searched where at least one is present from these words.

And this is where the task becomes significantly more complicated. It is possible to make a complex syntax system (as in search engines), for example, the exact occurrence is searched if the query is specified in quotes. And you can give users a choice of exactly how they want to conduct the search (using radio buttons). This is how it was done on my website. Therefore, one more point is added to the previous algorithm: drawing up an SQL query. Here is an example of an SQL query when you need to pull out all materials that contain at least one word from the query “I’m looking for this text”:

SELECT * FROM articles WHERE (`text_article` LIKE "%looking for%" OR `text_article` LIKE "%this%" OR `text_article` LIKE "%text%")

Accordingly, in the search script you must generate similar SQL queries, send them to the database, receive a response and display it. This becomes even more complicated if you display records by relevance, since it is difficult to immediately say what should be more relevant: 3 exact occurrences of the query, or 10 occurrences of parts of the query. On my site, preference is always given to exact occurrences, but this point is already quite controversial. Of course, this is difficult, and if you are doing this for the first time, you will definitely spend several hours. I hope that my algorithm for implementing website search via PHP will help you.

The best way to keep a user on a site is to let him find what he is looking for. If you create a convenient system for this, then the level of preference for your site will grow and the user will definitely return to find what interests him.

I'll show you how to create a simple, but functionally effective search form that will be used to search for articles on the site. The results will appear on the page without any reloads, which is undoubtedly the best way supply of information.

I will create 2 files: search.php, which will contain HTML and JavaScript. The second file, do_search.php will contain the PHP code. Let's start creating the first file:

PHP, jQuery search demo $(function() ( $(".search_button").click(function() ( // get what the user wrote var searchString = $("#search_box").val(); // form a query string var data = "search="+ searchString; // if searchString is not empty if(searchString) ( // make an ajax request $.ajax(( type: "POST", url: "do_search.php", data: data, beforeSend: function(html) ( // will run before the request is called $("#results").html(""); $("#searchresults").show(); $(".word").html (searchString); ), success: function(html)( // will run after receiving the results $("#results").show(); $("#results").append(html); ) )); ) return false; )); )); Try entering the word ajax
Results for

In this file we have created a regular HTML form which sends POST request in the back end - to the do_search.php file.

The PHP code contains comments so you can easily understand how the script works. If there are matches in the database, you show them to your user, highlighting in bold the words that the user searched for.

Let's give it all a little CSS:

Body( font-family:Arial, Helvetica, sans-serif; ) *( margin:0;padding:0; ) #container ( margin: 0 auto; width: 600px; ) a ( color:#DF3D82; text-decoration: none ) a:hover ( color:#DF3D82; text-decoration:underline; ) ul.update ( list-style:none;font-size:1.1em; margin-top:10px ) ul.update li( height:30px; border-bottom:#dedede solid 1px; text-align:left;) ul.update li:first-child( border-top:#dedede solid 1px; height:30px; text-align:left; ) #flash ( margin- top:20px; text-align:left; ) #searchresults ( text-align:left; margin-top:20px; display:none; font-family:Arial, Helvetica, sans-serif; font-size:16px; color: #000; ) .word ( font-weight:bold; color:#000000; ) #search_box ( padding:4px; border:solid 1px #666666; width:300px; height:30px; font-size:18px;-moz- border-radius: 6px;-webkit-border-radius: 6px; ) .search_button ( border:#000000 solid 1px; padding: 6px; color:#000; font-weight:bold; font-size:16px;-moz- border-radius: 6px;-webkit-border-radius: 6px; ) .found ( font-weight: bold; font-style: italic; color: #ff0000; ) h2 ( margin-right: 70px; )

So you have learned how to create a simple search form that works without reloading the page. I hope you enjoyed the lesson.

In this article, you'll learn how to create a database content search for your site. This algorithm supports multi-keyword search. The algorithm will select rows of the database table that contain all entered keywords.

There is a table “news”, which contains the following fields: id, title and content:

We need to search the content field in our database, which consists of the following columns, see the example in the picture above. Let's start implementation. First, let's create a page to test the work. It will contain a form with a keyword input field and a “find” button:

Listing html code:

Database Search

In the form attributes we specify the path to the handler containing the algorithm and the post transmission method.

We use a session to transfer an array of selected elements.

To do this, we launch it at the very beginning of the page.

For output we will use the print_r() function.

To prevent the result from being displayed a second time after the page is reloaded, we kill the session using unset.

Let's create a search.php handler. First, start a session and connect to the database:

For more information about connecting to a database via PDO, see.

The operation of the algorithm is shown in the diagram:

Let’s analyze the sample based on the first word, using the search query “how to make a website” as an example:

First, we get the string from the form using the POST method into the $str variable. We split this line into words separated by spaces using the expode function and count the number of words. We carry out a query in which we check for the presence of the first word in the content column. We create an empty array and write into it the values obtained as a result of sampling on request. We record the number of received elements in $id_count.

This part of the algorithm works on the principle of “weeding out”. Let's say there are ten articles in the database. After selecting the first word, we get the id of articles that contain the word “how”; there were six such articles. Next, we search for the second word among these six articles, thereby narrowing the search. This iteration leaves four articles that include both the words “how” and “do.” In the last iteration, among the remaining four articles, we look for the word “site”. After this pass, we get the id of one single article, which includes all the keywords.

The number of iterations is equal to the number of words in search query. The final number of ids received can be anything, depending on the request and the contents of the database table.

If, as a result of executing a query in a loop, we receive an id (temp variable) equal to one of the ids of the previous selection (id_mass), then we leave this id unchanged. IN otherwise we assign the value -1 to the id_mass[ j ] element, thereby excluding it from processing.

After the loops finish, we get an array of id in which the keywords and -1 are found. To pass only the required ids to the user, we use a loop that checks to discard all elements equal to -1. We transfer the remaining elements to the session array:

The header function is used to redirect the client to the search page.

As a result of the performed actions, we received a search function for the database table. With minor modifications, this algorithm can be used to retrieve and search any fields in any database.

Updated on April 30, 2016

I"m going to show you how to create simple search using PHP and MySQL. You"ll learn:

How to use GET and POST methods
Connect to database
Communicate with database
Find matching database entries with word given or phrase
Display results

Preparation

You should have Apache, MySQL and PHP installed and running of course (you can use for different platforms or WAMP for windows, MAMP for mac) or a web server/hosting that supports PHP and MySQL databases.

Let's create database, table and fill it with some entries we can use for search:

Go to phpMyAdmin, if you have server on your computer you can access it at http://localhost/phpmyadmin/
Create database, I called mine tutorial_search
Create table I used 3 fields, I called mine articles.
Configuration for 1st field. Name: id, type: INT, check AUTO_INCREMENT, index: primary

INT means it"s integer
AUTO_INCREMENT means that new entries will have other(higher) number than previous
Index: primary means that it"s unique key used to identify row

2nd field: Name: title, type: VARCHAR, length: 225

VARCHAR means it string of text, maximum 225 characters(it is required to specify maximum length), use it for titles, names, addresses
length means it can"t be longer than 225 characters(you can set it to lower number if you want)

3rd field: Name: text, type: TEXT

TEXT means it"s long string, it"s not necessary to specify length, use it for long text.

Fill the table with some random articles(you can find them on news websites, for example: CNN, BBC, etc.). Click insert on the top menu and copy text to a specific fields. Leave "id" field empty. Insert at least three.

It should look something like this:

Create a folder in your server directory and two files: index.php and search.php (actually we can do all this just with one file, but let"s use two, it will be easier)
Fill them with default html markup, doctype, head, etc.

Create a form with search field and submit button in index.php, you can use GET or POST method, set action to search.php. I used "query" as name for text field

GET - means your information will be stored in url (http://localhost/tutorial_search/search.php?query=yourQuery)
POST - means your information won"t be displayed it is used for passwords, private information, much more secure than GET

Ok, let's get started with php.