Php+mysql the function _php technique of realizing multi-keyword and multi-field generating SQL statements

This article illustrates the method of Php+mysql to realize the function of multiple-keyword and multi-field generating SQL statements. Share to everyone for your reference. The implementation methods are as follows:

First look at the example:

Copy Code code as follows:

$keyword = "1 2 3";
echo $sql =search ($keyword, "Enter_gongyin_pic", "a+b+c"); function generation, no limit, no order by

Generated:

Copy Code code as follows:

SELECT * from ' enter_gongyin_pic ' WHERE ' a ' like '%1% ' or ' a ' as '%2% ' or ' a ' like '%3% ' or ' B ' as '%1% ' or ' B ' like ' %2% ' or ' B ' like '%3% ' or ' C ' as '%1% ' or ' C ' like '%2% ' or ' C ' as '%3% '

$keyword is obtained by post or get. You can find multiple fields by separating them by space.

The implementation functions are as follows:

Copy Code code as follows:

function Search ($keyword, $table, $field)
{
//========================================================

Formal parameter Description:
Keyword is the key word, such as "Beijing Capital Direction Train". With spaces or without
Table name, such as Enter_gongyin_pic.
field is a combination of fields, such as finding a field and writing name.
If you are looking for more than two use Name+picdir
//========================================================
First determine field
$new _field=explode ("+", $field); Press + Peel
$field _count=count ($new _field); Number of results obtained


$newstring =explode ("", $keyword); Split by Space
$newstring 2=array ();
To remove a string from an empty Geshuzu element
$i = 0;
foreach ($newstring as $key => $value) {
if ($value!= "")
{
$newstring 2[$i]= $value;
$i + +;
}
}
Remove the empty Geshuzu element from the string,

$result _count=count ($newstring 2); Number of results obtained

The following generates the SQL statement


if ($field _count==1)//Find 1 fields START ****************************
if ($field _count==1)//Find 1 fields
{
if ($result _count==1)//judgment if it is a critical segment
{
$newstring _search= $newstring 2[0];
$sql = "SELECT *
From ' $table '
WHERE ' ". $new _field[0]." ' Like '% $newstring _search% ' ";
}

if ($result _count>1)//judgment if multiple key segments
{

$sql = "SELECT *
From ' $table '
WHERE ";
$sql _add= "";
foreach ($newstring 2 as $key => $value)
{
if ($key ==0)
{
$sql _add= $sql _add. " '. $new _field[0]. " ' Like '% '. $value. " %'";
}
Else
{
$sql _add= $sql _add. " OR ' ". $new _field[0]." ' Like '% '. $value. " %'";

}

}

$sql = $sql. $sql _add;
}

}

if ($field _count==1)//Find 1 Field End ****************************


if ($field _count>1)//Find multiple fields START ****************************
if ($field _count>1)//Find multiple fields, this time $new_field is an array. Have more than one field
{
if ($result _count==1)//judgment if it is a critical segment
{
$newstring _search= $newstring 2[0]; $newstring _search is the key word
$sql = "SELECT *
From ' $table '
WHERE ";
$sql _add= "";//Add Field
foreach ($new _field as $key => $value)
{
if ($key ==0)
{
$sql _add= $sql _add. " ". $value." ' Like '% '. $newstring _search. " %'";
}
Else
{
$sql _add= $sql _add. " OR ' ". $value." ' Like '% '. $newstring _search. " %'";
}
}
$sql = $sql. $sql _add;
}
if ($result _count>1)//Judging if it is multiple key segments (multiple keywords) ==========================
{
$sql = "SELECT *
From ' $table '
WHERE ";
$sql _add= "";//Add Field
foreach ($new _field as $key => $value)
{
if ($key ==0)//encounter $new_field[0] Time example: ' A ' like '%1% ' or ' a ' like '%2% ' or ' a ' like '%3% '
{//nested foreach
foreach ($newstring 2 as $key 2 => $value 2)
{
if ($key 2==0)
{
$sql _add= $sql _add. " ". $value." ' Like '% '. $value 2. " %'";
}
Else
{
$sql _add= $sql _add. " OR ' ". $value." ' Like '% '. $value 2. " %'";
}
}
Nested foreach
}
Else
(If it is a multiple-field, such as a name+picdir table), start a foreach continuous loop, executing else $new _field[1] $new _field[2] $new _field[3].
The corresponding value is $value
{
Nested foreach (multiple fields and multiple keywords)
foreach ($newstring 2 as $key 2 => $value 2)
{
if ($key 2==0)
{
$sql _add= $sql _add. " OR ' ". $value." ' Like '% '. $value 2. " %'";
}
Else
{
$sql _add= $sql _add. " OR ' ". $value." ' Like '% '. $value 2. " %'";
}
}
Nested foreach
}

}//foreach ($new _field as $key => $value) End
$sql = $sql. $sql _add;
}//if ($result _count>1) end
}//if ($field _count>1) end
if ($field _count>1)//Find more than one field end ****************************
return $sql;
}

I hope this article will help you with your PHP program design.