Men have search php sf. Safe and convenient search in MySQL
You have a JavaScript blocked in your browser. Allow JavaScript to work the site!
Search by site taking into account the morphology of the Russian language on PHP + site map
By creating your site with time you think about the need to make it a convenient universal search on it. There is a simple solution: fasten search from search engines, for example: search from Yandex or search from Google. The general lack of such a solution - only those pages that the search engine asked to add to the search index will be the search engine. In other words, part of your site will not be "sort to".
Sad, looking for another solution. Yes, here it is: Yandex.Server is a product to search for your site, taking into account the morphology of the Russian language. Loading. In the Unix Yandex. Server works as a daemon, and on the MS Windows platform - as a service. Those. It can only work when root - access to the server. When working the site on virtual hosting is not suitable. :-( The second drawback is no settings. Only one button "Run / stop".
We begin to "dig" the Internet. How so? Everyone has a search on your website. Somehow, people do it. For example, the frontal decision, which has long been described by me: Contextual search on the site, which does not take into account the declination of words and does not index the words on the pages. But the further deeper into the task, the more you realize that the task is completely nondrivial.
First, you need to scan the entire site and choose all words from it. Well, I already know how - the site map generator has long been successfully coping with this task.
Secondly, you need to get the original form of all words. There are several options here, for example, you can use streamerwhich cuts off the prefix, suffix and the end of the words. Or a more complex system using dictionaries.
Thirdly, it all needs to be downloaded to the database and index to search for a minimum of time.
Having spent two weeks of its time, having tried a large number of different options and algorithms I stopped at the following: 1. For scanning, I use a simplified parser, which, with the help of a regular expression, cuts all href from the page:
// Get unique links from $ HTML \u003d File_Get_contents ($ URL); if (preg_match ("| (.*?)
To highlight the words in the text with light or bold font, you need to do only the following:
$ highlight \u003d STR_REPLACE ("", "|", $ good);
Spaces (and they have a single word in our words, and nowhere else does not meet anywhere, besides, we also cut them out from the ends), it is enough to replace the vertical trait - the separator of options in regular expressions. "Bad" words we do not highlight, because in the database they are not looking for :). In the code that displays the text we write:
After writing the issue, I rushed, it was, writing and myself "backlight". It was not here! I have HTML tags in my text, so I had to think a lot ... It turned out such a thing (a string with words for highlighting is):
$ text \u003d eregi_replace ("\u003e ([^<]*)($words)", ">\\1\\2", $ text);
You have to watch, there is no word in the tag. However, there is a problem of the resource-intensity of such a replacement (my K6-266 over the text in 5 kilobytes thought as much as seven seconds). Sad.
Applying such techniques, it is possible, firstly, to limit the freedom of the user's action and not give it a) to learn the software structure of the site b) to cause server overload (for example, sending a megabyte of the text consisting of words in three letters long (the phrase turned out to be ambiguous, but rewrite I will not :) so that the script 250 times climb into the database) c) see an error message as a result of hitting in the line of special query language. Secondly, some convenience for the user is a tractor and illumination.
I remember the "Safe and Convenient Search" article there was such a phrase
Part 2. Briefly about relevance
Oleg Yusov
To display the search results for relevance, it is necessary:
The required VARCHAR fields, or any of the TEXT field varieties (Smalltext, MediumText, etc.) make FullText keys:
ALTER TABLE TABLE ADD FULLTEXT (FIELD)
Further - even easier:
$ Query \u003d "Select *, Matchword Against (" $ SearchWords ") AS RELEV from Table Order By Relev Desc"
Notes:
By default, the search for words containing at least 4 characters. It is necessary to install #define min_word_len 4 in the source ft_static.c, although in my opinion it is not necessary to edit it.
The% symbols in the search form are not available, the words in the search field are packed using the partition list.
The list of word separators will rule in the source ft_static.c.
It is necessary to at least dozen entries in the table to start calculating the relevance.
It is impossible to use the RELEV field in Where Clause:
SELECT *, MATCH FIELD AGAINST ("$ SearchWords") AS RELEV From Table WHERE RELEV\u003e 0 Order by RELV DESC
although you can:
Select *, Match Field Against ("$ SearchWords") AS RELEV From Table Where Match Field Against ("$ SearchWords")\u003e 0 Order by RELEV DESC
The speed is high enough - even in some cases faster Like search
All of the above works starting with the version MYSQL 3.23.23
When creating FullText indexes, 2 options are possible for several fields:
Select *, Match Field1, Field2 Against ("$ SearchWords") AS RELEV From Table Order by RELV DESC
relevance is calculated from all fields immediately. In the second case, such a request will give an error. Here we calculate the relevance as follows:
Select *, Match Field1 Against ("$ SearchWords") + Match Field2 Against ("$ SearchWords") AS RELEV From Table Order by RELEV DESC
The second option is somewhat more complicated in queries, however, in my opinion it is better, because Increases search flexibility - to each of the fields can be set, for example, the ratio of importance and when the fields are summarized by fields to multiply them to this coefficient. Search phrase will be "more" to be in the fields with a large coefficient. For example, if we search for indexed resource directory pages, the page name field is usually specified with a large coefficient than the fields of meta tag descriptions or keywords.
Part 3: Exercises with Relevance
First how to add a fulltext index:
MySQL\u003e ALTER TABLE ARTICLEA ADD FULLTEXT (ZTEXT); Error 1073: BLOB COLUMN "ZTEXT" CAN "T BE Used in Key Specification with the USED Table Type MySQL\u003e Alter Table Articlea Type \u003d Myisam; Query OK, 36 Rows Affectedd (0.60 SEC) Records: 36 Duplicates: 0 Warnings: 0 MySQL \u003e ALTER TABLE ARTICLEA ADD FULLTEXT (ZTEXT); Query OK, 36 Rows Affectedd (10.00 SEC) Records: 36 Duplicates: 0 Warnings: 0
Text indexes can only be done in MYISAM Types. Texts are taken from the table and drop into the index file, and the base is growing. Regarding requests. It is impossible to use the RELEV field in Where Clause:
SELECT *, MATCH FIELD AGAINST ("$ SearchWords") AS RELEV From Table WHERE RELEV\u003e 0 Order by RELV DESC
Although you can:
Select *, Match Field Against ("$ SearchWords") AS RELEV From Table Where Match Field Against ("$ SearchWords")\u003e 0 Order by RELEV DESC
The calculated field, of course, cannot be used in WHERE on all the syntax rules, but can be used in Having:
SELECT *, MATCH FIELD AGAINST ("$ SearchWords") AS RELEV From Table Having Release\u003e 0 Order by RELV DESC
Search through Match, as Oleg wrote, is done only by the word. ... However, on relevance, you can only sort, and choose Like (this, of course, will affect performance, I don't even know how much).
Remove the condition "RELEV\u003e 0", we leave sorting. The rest, as before, rub the resulting string and turn into a request with several LIKE operators:
Select *, Match Field Against ("$ SearchWords") AS RELEV From Table WHERE Field Like "% $ Word1%" OR Field Like "% $ Word2%" Order by RELEV Desc, Datefield Desc
Part 4: Propening Started
I continue to search the topic with sorting on the relevance in the MYSQL database.
MySQL offers in the latest versions of the database to use FullText indexing and Match Field Against design. However, not all servers are the latest version of MySQL, and not all hosting providers want to update the software for the reasons of the reliability of the system.
At one time I assumed that the search for sorting on relevance would need to do in several requests, and, therefore, it is better not to take it at all. The thoughts that the relevance can be counted at the very query divergently visited me, but I was afraid and submit such a design.
However, the employee of one of the site-building firms N-SKA spacked me the search system that they apply on their sites. I definitely remember the request, I will try to reproduce it so:
SELECT TITLE, DATE_FORMAT (MATERIAL_DATE, "% E.% C.% Y") AS DATE1, IF (Text Like "% Word1 Word2 Word3%", 3 * 10, 0) + if (Text Like "% Word1%" 9, 0) + if (Text Like "% Word2%", 9, 0) + if (text like "% Word3%", 9, 0) AS RELEVANCE FROM Table WHERE TEXT LIKE "% WORD1%" OR TEXT LIKE " % Word2% "Or Text Like"% Word3% "Order by Relveance Desc, Material_Date Desc
It looks terribly, but it works even on the old versions of MySQL. I tried to compare the speed of work with this request:
Select Title, Date_Format (Material_Date, "% E.% C.% Y") AS DATE1, MATCH TEXT AGAINST ("Word1 Word2 Word3") AS RELEVANCE from Table WHERE Text Like "% Word1%" Or Text Like "% Word2% "Or Text Like"% Word3% "Order by Relveance Desc, Material_Date Desc
On average, the speed of the universal request is two times less than that using new designs. What is quite logical - the more versatility, the greater the resource intensity.
Let's try to build such a request automatically. Sut off the long string, as well as all the wrong characters and short words. Draw a request.
$ Query \u003d "SELECT TITLE, DATE_FORMAT (MATERIAL_DATE,"% E.% C.% Y ") AS DATE1, IF (Text Like"%. $ good_words. "%", ". (Substr_Count ($ good_words," " ) + 1). "* 10, 0) + if (Text Like"%. Str_replace ("", "%", 9, 0) + if (Text Like "%", $ good_words). "%", 9, 0) AS RELEVANCE From Table Where Text Like "%". Str_replace ("", "%" or Text Like "%", $ good_words). "%" ORDER by RELEVANCE DESC, MATERIAL_DATE DESC ";
Not very difficult. For reliability and protection against floods, you can limit the number of words in the query.
Some additions to previous publications
Total number of lines found in the table. To display the search results, of course, you need to use the Limit statement (so as not to write every time the formation of this parameter, use ready-made functions). If no grouping operations are done in the query, it is better to calculate the number of rows immediately in the query - Count (*), and not through the PHP MYSQL_NUM_ROWS () function. You can check on large tables. If group operations are produced, we make a request from Count (Distinct (<поле, по которому группируем>)), but without GROUP BY.
Illumination. If there are no HTML tags in the texts, it's easier to live
If the tags are used in the text, that is, three options a) not to draw backlight b) because the user does not see the user (except that a very curious user), then you can make the field of the index in which there will be no tags and the characters [^ \\ W \\ x7f- \\ XFF \\ s] will be replaced by spaces (these characters are cut from the search string at the very beginning, so that the search is not performed). Search and backlight in this case, to make exactly the index. c) make the backlight of the text from the usual field, having previously cutting the tags with the SRIP_TAGS () function.
The full version of the search code, as always, in the list of files.
There are still questions or something incomprehensible - welcome to our
11.1K.
One of the most popular and necessary functions on any site is a search implemented using a special form. This functionality allows visitors to quickly find their content on the site.
Today we want to tell how to make a site search using a special form that will interview the 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.) - Special language for collecting, recordings and modifying information contained in databases. Before you start, we recommend that you download project files.
What you need
Tool to work with MySQL databases.
Local or remote server with PHP support.
Text editor.
Create a database
If you are not quite sure that you can deal with the database on your hosting, contact the hoster to obtain relevant instructions or assistance. After 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 enough for our current manual.
Creating a table
Our table must be created in the following format:
Column Name.
Data Type
Length.
NULL OR NOT NULL
PRIMARY KEY?
AUTO INCREMENT.
Id
Int.
1
NOT NULL
Yes
Yes
FirstName.
Varchar.
50
NOT NULL
No.
No.
LastName.
Varchar.
50
NOT NULL
No.
No.
Email
Varchar.
50
NOT NULL
No.
No.
PhoneNumber.
Varchar.
15
NOT NULL
No.
No.
The database table consists of columns and strings, as in Excel. The first column allows you to identify data by name. Next comes the Data Types column (data type), which indicates the type of data contained in the column. The Length field indicates the maximum amount of memory (storage) for the 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 reserved place will be occupied.
And among these personnel can not be empty values \u200b\u200b( nULL, Empty). The first line is highlighted in yellow, because the ID column is our main key. The main key in the database ensures that each entry is unique. This column also applies autocrem, which means that each entry in our database will be assigned a unique number automatically.
We introduce staff representatives to the table
As soon as you figure it out with the table, start filling it with data. 6 records are enough to fix the procedure in mind. Below I offer you your own example:
Column ID.
FirstName.
LastName.
Email
PhoneNumber.
2
Ryan.
Butler.
[Email Protected]
417-854-8547
3
Brent.
Callahan
[Email Protected]
417-854-6587
Development of form
To create a search form from Google, open any suitable text editor. I recommend using free PSPAD. You can use any text editor where the syntax backlight is provided. This greatly facilitates the process of writing and debugging PHP code. By creating a page for a search form, do not forget to save it in the .php format, otherwise the PHP code will not be processed properly. As soon as you save the document, copy the following markup to it: