PHP development in the practical two of SQL

The two-day project development, need to implement some of the more practical functions, with two use of SQL, summed up, afraid to forget the next time.

1. Retrieving content from the database that matches the submitted content

For example: The data submitted is "swimming", then the database has "I like swimming" words, even if the match, but this is not enough, for example, I submitted a "weekend to swim", the database has "swimming" content, in fact, the meaning is similar, but use like not to find, so think of the following SQL, has been encapsulated as a function:

function Getrelationtags ($tagTitle, $cols = "*")
{
$titleFeildStrLen = 24; 3*8 four characters or 24 characters.
if ("" = = $tagTitle) return false;

$sql = "Select $cols from". $TableName. "Where title!=" and (LOCATE (title, ' $tagTitle ') or (IsSystem = 1 or LENGTH (tit Le) <= $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, is two times matching, one is a positive match, that is, the submission of the label with the database tags to match, the second is the database in the label with the submitted tags to match.

The key is in the locate () function, which also limits the length, because the code in MySQL is:

Set names ' UTF8 '

Is the UTF8, then a Chinese character to occupy 3 bytes, the character occupies only 1 bytes, so the above:

$titleFeildStrLen = 24;

Is the 8 Chinese characters and the 24 character range that the label to match.

2. Similar sort

In a database such as this:

Beijing 1023 1

Tianjin 2301 1

Shanghai 3450 1

Tianjin 4520 1

Beijing 3902 1

So I'm going to extract all the city data and sort the total number of each city data compared to 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 the city ORDER by num desc limit 0, $num;";

$data =& $db->getall ($sql);

if ($db->iserror ($data))

return false;

Else

return $data;

}

Let's look at the above SQL statement:

Select COUNT (ID) as num,city from. $TableName. The Where City!= ' GROUP by the city ORDER by num desc LIMIT 0, $num

The core is the group by the city of similar cities to focus on the order of more than a few.

Author:heiyeluren

Writetime:2005-07-01 14:35