How to make a multi-word search algorithm in PHP from a database. How to make a search algorithm for several words in PHP from a database Which man search php author id

In this article, you'll learn how to create a database content search for your site. This algorithm supports searching for several keywords. The algorithm will select rows of the database table that contain all the 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:

HTML code listing:

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 this article.

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 the 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. Otherwise, we assign the value of -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.

See also additional articles about MVC

One of the most popular and necessary functions on any website is search, implemented using a special form. This functionality allows visitors to quickly find content that interests them on the site.

Today we want to tell you how to search the site using a special form that will query database tables and display information about current managers on the site. You will learn how to create database tables that will contain information about current personnel.

Develop search forms using PHP, and also get acquainted with SQL (Structured Query Language) - a special language for collecting, recording and modifying information contained in databases. Before you begin, we recommend that you download the project files.

What you will need

• Tool for working with MySQL databases.
• Local or remote server with PHP support.
• Text editor.

If you are not entirely sure that you can understand the database on your hosting, then contact the hoster for appropriate instructions or assistance. Once the database is created, you will need to connect it, create a table and write the necessary data into it.

The most popular tool for managing MySQL is PHP My Admin. This tool will be sufficient for our tutorial today.

Our table should be created in the following format:

A database table consists of columns and rows, just like Excel. The first column allows you to identify the data by name. Next comes the Data types column, which tells us the type of data contained in the column. The Length field specifies the maximum amount of memory (storage) for a table column. We use variables that give more flexibility. In other words, if the length of the full name is less than 50 characters, then only part of the allotted space will be occupied.

And among the personnel data there cannot be empty values ​​(null, empty ). The first row is highlighted in yellow because the ID column is our primary key. The master key in the database ensures that each record is unique. Auto-increment is also applied to this column, which means that each record in our database will be assigned a unique number automatically.

Once you understand the table, start filling it with data. 6 notes are enough to fix the procedure in your mind. Below I offer you my own example:

To create a site search form via Google, open any suitable text editor. I recommend using the free PSPad. You can use any text editor that provides syntax highlighting. This will greatly facilitate the process of writing and debugging PHP code. When creating a page for your search form, be sure to save it in .php format, otherwise the PHP code will not be parsed properly. Once you save the document, copy the following markup into it:

Search contacts:

Detailed contact search

You can search by first name or last name

If you are familiar with the HTML language, then everything here should be clear to you at least until the opening form tag. Inside this tag is the most important element of the entire code - the action attribute. For our form action, we specified the name of our file and then applied the query string “go” to it.

When a user enters a first or last name and then clicks the submit button, the form passes the data to itself and adds the query string “go” at the end. At this point we check for the presence of the go query string. If the result is positive, we display the search results.

Before displaying the requested results, we need to double-check: (1) was the form submitted, (2) did the query string contain the value go, (3) was the search term entered in lower or upper case? If none of the checks yield a positive result (true), then we are not required to perform any action.

First, let's add a small block of PHP site search code after the closing tag:

First, we open a block of PHP code with the “” tag.

Any PHP code inside this tag pair will be executed by the server. Then we check if the form has been submitted:

We'll use the built-in isset function, which returns a bool, and put the $_POST array into it. A Boolean expression in programming allows us to get either true or false.

Therefore, if the function returns true, then the form has been submitted and we need to continue executing the code further. If the function returns false , then we will print an error message. Save all the typed code in the search_submit.php file.

We're putting another conditional boolean expression inside the main one, but this time we're using the $_GET array along with the "go" value. Save the changes to the search_go.php file.

Now we need to make sure that visitors can only enter the first letter of the query string in uppercase or only in lowercase. We also need to provide a way to take into account the search criteria entered by the visitor. The best way to validate visitor input is to use a regular expression:

We put another conditional logical expression inside our two. This time we are using a regular expression to validate the input. We use the built-in preg_match function with two parameters: a regular expression, and the form field to which the validation should be applied.

In our case, this will be the “Name” field (name). To retrieve the search parameters specified by the visitor, we create a $name variable and bind it to a POST value with the name of the form field that will be used in the SQL query. We have now implemented: (1) the form data is submitted, (2) the query string includes the value go, and (3) the visitor has entered either an uppercase or lowercase first letter. And all these checks happen before any changes are made to the database. Save all changes.

To get data from a table, you first need to connect to the server in the site search script. To do this we use the following code:

We create a variable $db and bind it to the built-in MySQL function mysql_connect, which takes three parameters: the server with the database (localhost if you are working locally), login and password.

After that, we run the built-in PHP function die, which stops further code execution if there is no connection to the database. And we display information about the error by running the built-in MySQL function mysql_error , which will return the cause of the error. Save the search_connectdb.php file.

We create a variable called mydb and bind it to the built-in MySQL function mysql_select_db, and then specify the name of the database that we created earlier. Next, we query the database table using an SQL query with the variable name, which contains the search parameters entered by the visitor:

When querying a database table, we create a $sql variable and bind it to a string containing the SQL query. We use the SELECT statement to retrieve values ​​from the id columns and the first and last name columns from the contacts table. We then use the WHERE clause along with the first and last name values ​​to narrow the search.

Together with the LIKE operator, we use the percent sign (%) - a special character that returns 0 or more characters, as well as the name variable from the search string. As a result, LIKE (in combination with the special character ) finds any matching name in the database table. You can describe the whole process as follows: “We select the first and last name from the contacts table that match the ones entered by the visitor.” Save the search_query.php file.

We create a $result variable and assign it the value of the mysql_query() function by adding it to $query. Now our query is stored in the result variable. To output the result in PHP, we create a loop and then output the data in an unordered list:

First we create a while loop, inside it we create a variable called row, and initialize it with the return value of the mysql_fetch_array function, which takes the result variable, which contains our SQL query. Inside the while loop, we assign each column value to the value of a variable with the same name. We then output the values ​​inside the unordered list.

It is important to pay attention to two points here: (1) inside the while loop you do not need to assign values ​​to the row array variables, since the values ​​can be taken directly from the row array; (2) the anchor tag, which we use in the name of our file along with the id and primary key. The reason for this is that many search elements don't initially show anything.

Since we're only displaying first and last names, with an ID at the end of our anchor tag, we can use the ID for an additional query that will return additional information about the staff. Save the file and test the PHP site search form (search_display.php).

The results are displayed as an unordered list, but the point is that we don't need tabs. To get rid of it, add the following CSS rule to the very beginning of your file in head:

ul li( list-style-type:none; )

Implementing a letter search only requires a few additional lines of code. Let's add this convenient functionality for visitors. This way, they will be able to find staff representatives by the letters contained in the first or last name.

Add the following line of code after the closing form tag:

A | B | K