Two SQL statements used in PHP Development

During the two days of project development, some practical functions need to be implemented. I used two SQL statements and summarized them. I am afraid I will forget them next time.

1. Retrieve the content that matches the submitted content in the database

For example, if the submitted data is "swimming", the words "I like swimming" in the database are matched, but this is not enough, for example, I submitted "go swimming on weekends", and the database contains "swimming" content. In fact, the content is similar, but I can't find it using like, so I thought of the following SQL statement, it has been encapsulated into a function:

Function getRelationTags ($ tagTitle, $ cols = "*")
{
$ TitleFeildStrLen = 24; // 3*8 four Chinese characters or 24 characters.
If ("" = $ tagTitle) return false;

$ SQL = "select $ cols from". $ TableName. "where title! = ''And (LOCATE (title, '$ tagtitle') or (issystem = 1 or LENGTH (title) <= $ titleFeildStrLen) and title like' % ". $ tagTitle. "% ') order by LENGTH (title )";
$ Data = & $ db-> getAll ($ SQL );
If (DB: isError ($ data )){
Return $ this-> returnValue ($ data-> getMessage ());
} Else {
Return $ data;
}
}

Check SQL:

Select $ cols from ". $ TableName." where title! = ''And (LOCATE (title, '$ tagtitle') or (issystem = 1 or LENGTH (title) <= $ titleFeildStrLen) and title like' % ". $ tagTitle. "% ') order by LENGTH (title)

In fact, it is two matches, one is positive match, that is, the submitted tag matches the tag in the database, and the second is to match the tag in the database with the submitted tag.

The key lies in the LOCATE () function and also limits the length, because the encoding in mysql is:

Set names 'utf8'

It is utf8, so a Chinese character occupies 3 bytes, and the character only occupies 1 byte, so the above:

$ TitleFeildStrLen = 24;

That is, the tags of the 8 Chinese characters and the 24 character range are matched.


2. Similar sorting

Such content in the database:

Beijing 1023 1

Tianjin 2301 1

Shanghai 3450 1

Tianjin 4520 1

Beijing 3902 1

Then I want to extract all the city data and sort the total number of each city data with the total number of other cities.

The function code is as follows:

Function getMostCity ($ num)

{

$ SQL = "select count (id) as num, city from". $ TableName. "where city! = ''Group by city order by num desc limit 0, $ num ;";

$ Data = & $ db-> getAll ($ SQL );

If ($ db-> isError ($ data ))

Return false;

Else

Return $ data;

}

Let's take a look at the preceding SQL statement:

Select count (id) as num, city from ". $ TableName." where city! = ''Group by city order by num desc limit 0, $ num

The core is group by city, which sorts similar cities in ascending order.