Simple application of mysql search_PHP tutorial
How to get better full-text search results in MySQL
Author: Techrepublic.com.com
2006-04-03 11:14:53
Many Internet applications all provide full-text search functions, and users can use a word or word fragment as a query item to locate matching records. Behind the scenes, these programs use the LIKE statement in a SELECT query to perform this query. Although this method works, it is an extremely inefficient method for full-text search, especially when processing large amounts of data. .
MySQL provides a solution to this problem based on the built-in full-text search method. Here, developers simply mark the fields that require full-text search, and then use special MySQL methods to run searches on those fields, which not only improves performance and efficiency (because MySQL indexes these fields to optimize searches) , and enables higher-quality searches because MySQL uses natural language to intelligently rate results to eliminate irrelevant items.
This article will tell you how to perform full-text search in MySQL.
1. Set up the basic table
Start by creating the example table, use the following SQL command:
mysql> CREATE TABLE reviews (id INT(5) PRIMARY KEY NOT NULL AUTO_INCREMENT, data TEXT);
The above command creates a simple music album database (mainly the entire text), and then adds some records to this table:
mysql> INSERT INTO `reviews` (`id`, `data`) VALUES
(1, 'Gingerboy has a new single out called Throwing Rocks. It's great!');
mysql> INSERT INTO `reviews` (`id`, `data`) VALUES
(2, 'Hello all, I really like the new Madonna single.
One of the hottest tracks currently playing... I've been listening to it all day');
mysql> INSERT INTO `reviews` (`id`, `data`)
VALUES (3, 'Have you heard the new band Hotter Than Hell?
They have five members and they burn their instruments when they play in concerts.
These guys totally rock! Like, awesome, dude!');
Verify the correct entry of data:
mysql> SELECT * FROM reviews;
+----+---------------- ----------------------------+
| id | data +---------------------------------------------+
| 1 | Gingerboy has a new single out called... |
| 2 | Hello all, I really like the new Madon... |
| 3 | Have you heard the new band Hotter Than... |
+----+----------------------------- ---------------+
3 rows in set (0.00 sec)
2. Define the full-text search field
Next , define the fields you want to index for full-text search
mysql> ALTER TABLE reviews ADD FULLTEXT INDEX (data);
Query OK, 3 rows affected (0.21 sec)
Records: 3 Duplicates: 0 Warnings: 0
Use the SHOW INDEXES command to check that the index has been added:
mysql> SHOW INDEXES FROM reviews;
+--- ------+---------------+--------+------+---------- -+---------+
| Table | Column_name | Packed | Null | Index_type | Comment |
----------+-- -------------+--------+------+----------------+------ --+
| reviews | id | NULL | BTREE |
| reviews | data | NULL | YES | FULLTEXT |
+----- ----+---------------+--------+------+------------+ ---------+
2 rows in set (0.01 sec)
3. Run full-text search
When you have the data and index, You can use MySQL's full-text search. The simplest full-text search method is the SELECT query with the MATCH...AGAINST statement. The following is a simple example to find records containing the word "single":
mysql> SELECT id FROM reviews WHERE MATCH (data) AGAINST ('single');+----+
| id |
+----+
| 1 |
| 2 |
+----+
2 rows in set (0.00 sec)
Here, MATCH () compares the text in the field passed to it as a parameter with the parameters passed to AGAINST(). If there is a match, it is returned in the normal way. Note that you can pass more than one field to MATCH() - just comma-separate the list of fields.
When MySQL receives a full-text search request, it scores each record internally, with unmatched records getting a score of zero, and "more relevant" records getting a higher score than "less relevant" ones. recorded relatively higher scores.Relevance is determined by a series of MySQL distinction criteria. Check the MySQL user manual for more information.
To see the score of each record, just return the MATCH() method as part of the result set, as shown below:
mysql> SELECT id, MATCH (data) AGAINST ( 'rock') FROM reviews;
+----+--------------------------------- +
| id | MATCH (data) AGAINST ('rock') |
+----+---------------- ---------------+
| 1 | 0 |
| 2 | 0 | 1.3862514533815 |
+----+----------------------------------+
3 rows in set (0.00 sec)
4. Use logical search modifiers (Boolean search modifiers)
You can also use logical search modifiers to conduct a more precise search, which is done in the AGAINST statement Add the special IN BOOLEAN MODE modifier to achieve this. In the following example, records containing the word "single" but not "Madonna" will be found:
mysql> SELECT id FROM reviews WHERE MATCH (data) AGAINST ('+single -madonna' IN BOOLEAN MODE);
+----+
| id |
+----+
| 1 |
+----+
1 row in set (0.00 sec)
This search feature is usually used to search for word fragments (instead of Complete word), this can be achieved by using the * (asterisk) operator in the IN BOOLEAN MODE statement. The following example shows how to find records containing "hot" in the word:
mysql> SELECT id FROM reviews WHERE MATCH (data) AGAINST ('hot*' IN BOOLEAN MODE);+----+
| id |
+----+
| 3 |
| 2 |
+----+
2 rows in set (0.00 sec)
You can also use This method finds at least one parameter passed to AGAINST. The following example finds records that contain at least one of the words "hell" and "rocks":
mysql> SELECT id FROM reviews WHERE MATCH ( data) AGAINST ('hell rocks' IN BOOLEAN MODE);
+----+
| id |
+----+
| 1 |
| 3 |
+----+
2 rows in set (0.00 sec)
These examples above It demonstrates a more effective method for full-text search compared to the traditional SELECT...LIKE statement. You can try this method the next time you need to write a MySQL database search interface.
http://www.bkjia.com/PHPjc/318360.html

To protect the application from session-related XSS attacks, the following measures are required: 1. Set the HttpOnly and Secure flags to protect the session cookies. 2. Export codes for all user inputs. 3. Implement content security policy (CSP) to limit script sources. Through these policies, session-related XSS attacks can be effectively protected and user data can be ensured.

Methods to optimize PHP session performance include: 1. Delay session start, 2. Use database to store sessions, 3. Compress session data, 4. Manage session life cycle, and 5. Implement session sharing. These strategies can significantly improve the efficiency of applications in high concurrency environments.

Thesession.gc_maxlifetimesettinginPHPdeterminesthelifespanofsessiondata,setinseconds.1)It'sconfiguredinphp.iniorviaini_set().2)Abalanceisneededtoavoidperformanceissuesandunexpectedlogouts.3)PHP'sgarbagecollectionisprobabilistic,influencedbygc_probabi

In PHP, you can use the session_name() function to configure the session name. The specific steps are as follows: 1. Use the session_name() function to set the session name, such as session_name("my_session"). 2. After setting the session name, call session_start() to start the session. Configuring session names can avoid session data conflicts between multiple applications and enhance security, but pay attention to the uniqueness, security, length and setting timing of session names.

The session ID should be regenerated regularly at login, before sensitive operations, and every 30 minutes. 1. Regenerate the session ID when logging in to prevent session fixed attacks. 2. Regenerate before sensitive operations to improve safety. 3. Regular regeneration reduces long-term utilization risks, but the user experience needs to be weighed.

Setting session cookie parameters in PHP can be achieved through the session_set_cookie_params() function. 1) Use this function to set parameters, such as expiration time, path, domain name, security flag, etc.; 2) Call session_start() to make the parameters take effect; 3) Dynamically adjust parameters according to needs, such as user login status; 4) Pay attention to setting secure and httponly flags to improve security.

The main purpose of using sessions in PHP is to maintain the status of the user between different pages. 1) The session is started through the session_start() function, creating a unique session ID and storing it in the user cookie. 2) Session data is saved on the server, allowing data to be passed between different requests, such as login status and shopping cart content.

How to share a session between subdomains? Implemented by setting session cookies for common domain names. 1. Set the domain of the session cookie to .example.com on the server side. 2. Choose the appropriate session storage method, such as memory, database or distributed cache. 3. Pass the session ID through cookies, and the server retrieves and updates the session data based on the ID.


Hot AI Tools

Undresser.AI Undress
AI-powered app for creating realistic nude photos

AI Clothes Remover
Online AI tool for removing clothes from photos.

Undress AI Tool
Undress images for free

Clothoff.io
AI clothes remover

Video Face Swap
Swap faces in any video effortlessly with our completely free AI face swap tool!

Hot Article

Hot Tools

SAP NetWeaver Server Adapter for Eclipse
Integrate Eclipse with SAP NetWeaver application server.

VSCode Windows 64-bit Download
A free and powerful IDE editor launched by Microsoft

SecLists
SecLists is the ultimate security tester's companion. It is a collection of various types of lists that are frequently used during security assessments, all in one place. SecLists helps make security testing more efficient and productive by conveniently providing all the lists a security tester might need. List types include usernames, passwords, URLs, fuzzing payloads, sensitive data patterns, web shells, and more. The tester can simply pull this repository onto a new test machine and he will have access to every type of list he needs.

Notepad++7.3.1
Easy-to-use and free code editor

Safe Exam Browser
Safe Exam Browser is a secure browser environment for taking online exams securely. This software turns any computer into a secure workstation. It controls access to any utility and prevents students from using unauthorized resources.