Two practical SQL_PHP tutorials in PHP development

In the past two days of project development, some practical functions need to be implemented, and two sql are used. To summarize, I am afraid that I will forget it next time.

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

For example: if the submitted data is "swimming", then the words "I like swimming" in the database will be considered a match, but this is still not enough. For example, if I submit "go swimming on weekends", there is "swimming" in the database The content of actually means something similar, but I couldn’t find it using like, so I thought of the following sql, which 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;
}
}

Look at 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 forward matching, which is to match the submitted tags with the tags in the database, and the second time is to match the tags in the database with the submitted tags.

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

set names 'utf8'

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

$titleFeildStrLen = 24;

That is, matching 8 Chinese characters with labels in the 24-character range.


2. Similar sorting

For example, the content in the database is as follows:

Beijing 1023 1

Tianjin 2301 1

Shanghai 3450 1

Tianjin 4520 1

Beijing 3902 1

Then I need to extract all city data, compare the total number of each city data with the total number of other cities, and then sort.

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 pay attention to the above 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 collects similar cities and sorts them from most to least.