PHP+Mysql实现多关键字与多字段生成SQL语句的函数,mysqlsql
PHP+Mysql实现多关键字与多字段生成SQL语句的函数,mysqlsql
本文实例讲述了PHP+Mysql实现多关键字与多字段生成SQL语句的函数的方法。分享给大家供大家参考。具体实现方法如下:
先看实例:
复制代码 代码如下:
$keyword="1 2 3";
echo $sql=search($keyword,"enter_gongyin_pic","a+b+c"); //函数生成,没有LIMIT,没有ORDER BY
生成:
复制代码 代码如下:
SELECT * FROM `enter_gongyin_pic` WHERE `a` LIKE '%1%' OR `a` LIKE '%2%' OR `a` LIKE '%3%' OR `b` LIKE '%1%' OR `b` LIKE '%2%' OR `b` LIKE '%3%' OR `c` LIKE '%1%' OR `c` LIKE '%2%' OR `c` LIKE '%3%'
$keyword由POST或者GET获得.按空格分开 可以多字段去查找.
实现函数如下:
复制代码 代码如下:
function search($keyword,$table,$field)
{
//========================================================
//形参说明:
//keyword为关键字,如“北京首都 方向 火车”。带有空格或者不带
//table为表名,如enter_gongyin_pic。
//field为字段组合,如查找一个字段就写好 name
//如查找两个以上就用 name+picdir
//========================================================
//首先确定field
$new_field=explode("+",$field); //按+剥离
$field_count=count($new_field); //得到的结果数量
$newstring=explode(" ",$keyword); //按空格剥离
$newstring2=array();
//把字符串去掉没有用的空格叔祖元素
$i=0;
foreach ($newstring as $key => $value) {
if($value!="")
{
$newstring2[$i]=$value;
$i++;
}
}
//把字符串去掉没有用的空格叔祖元素,
$result_count=count($newstring2); //得到的结果数量
//下面生成SQL语句
//********************** if($field_count==1) //找1个字段 START ****************************
if($field_count==1) //找1个字段
{
if($result_count==1) //判断如果是一个关键段
{
$newstring_search=$newstring2[0];
$sql="SELECT *
FROM `$table`
WHERE `".$new_field[0]."` LIKE '%$newstring_search%'";
}
if($result_count>1) //判断如果是多个关键段
{
$sql="SELECT *
FROM `$table`
WHERE ";
$sql_add="";
foreach ($newstring2 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) //找1个字段 END ****************************
//********************** if($field_count>1) //找多个字段 START ****************************
if($field_count>1) //找多个字段,这个时候$new_field是一个数组。拥有多个字段
{
if($result_count==1) //判断如果是一个关键段
{
$newstring_search=$newstring2[0]; //$newstring_search是关键字
$sql="SELECT *
FROM `$table`
WHERE ";
$sql_add="";//新增加字段
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) //判断如果是多个关键段(多个关键字)==========================
{
$sql="SELECT *
FROM `$table`
WHERE ";
$sql_add="";//新增加字段
foreach ($new_field as $key => $value)
{
if($key==0) //遇到$new_field[0]时候 例:`a` LIKE '%1%' OR `a` LIKE '%2%' OR `a` LIKE '%3%'
{ //嵌套foreach
foreach ($newstring2 as $key2 => $value2)
{
if($key2==0)
{
$sql_add=$sql_add."`".$value."` LIKE '%".$value2."%'";
}
else
{
$sql_add=$sql_add." OR `".$value."` LIKE '%".$value2."%'";
}
}
//嵌套foreach
}
else
//(如果是多字段的比如查name+picdir表)开始FOREACH连续循环,每次执行ELSE $new_field[1] $new_field[2] $new_field[3]。
//对应的值为$value
{
//嵌套foreach(多字段与多关键字)
foreach ($newstring2 as $key2 => $value2)
{
if($key2==0)
{
$sql_add=$sql_add." OR `".$value."` LIKE '%".$value2."%'";
}
else
{
$sql_add=$sql_add." OR `".$value."` LIKE '%".$value2."%'";
}
}
//嵌套foreach
}
}//foreach ($new_field as $key => $value)结束
$sql=$sql.$sql_add;
}//if($result_count>1)结束
}//if($field_count>1) 结束
//********************** if($field_count>1) //找多个字段 END ****************************
return $sql;
}
希望本文所述对大家的PHP程序设计有所帮助。
你说的无限级别,不是树状无限吧,只是说类别的个数无限,级别只有两级,对吧
一般大类不会很多,可以使用一个字符来表示,例如用A~Z进行编码,A为新闻、B为体育、……
新闻的类别可以设置为字符类型,第一位表示大累,后面的表示小类,例如A001国际新闻、A002国内新闻、B001篮球、……
建立两个表,一个是新闻大类表,两个字段:大类代码、大类名称;另外一个类别表,也是两个字段:类别代码、类别名称
在你的新闻表里面,只使用一个字符类型的类别字段就可以了。
要产生你那样的树状目录输出,起始非常简单,只要你的SQL语句按照类别进行排序就行了,程序检测每一条数据是否和上面的类别相同,以确定前面是显示+还是|,仅此而已。
insert into tb_test (`use`) values ('test');
用这个点,就是上边数字键1左边的那个

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

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

SublimeText3 Chinese version
Chinese version, very easy to use

Zend Studio 13.0.1
Powerful PHP integrated development environment

Dreamweaver CS6
Visual web development tools

SublimeText3 Mac version
God-level code editing software (SublimeText3)

Hot Topics



PHP and Python each have their own advantages, and choose according to project requirements. 1.PHP is suitable for web development, especially for rapid development and maintenance of websites. 2. Python is suitable for data science, machine learning and artificial intelligence, with concise syntax and suitable for beginners.

PHP remains important in modern web development, especially in content management and e-commerce platforms. 1) PHP has a rich ecosystem and strong framework support, such as Laravel and Symfony. 2) Performance optimization can be achieved through OPcache and Nginx. 3) PHP8.0 introduces JIT compiler to improve performance. 4) Cloud-native applications are deployed through Docker and Kubernetes to improve flexibility and scalability.

PHP is a scripting language widely used on the server side, especially suitable for web development. 1.PHP can embed HTML, process HTTP requests and responses, and supports a variety of databases. 2.PHP is used to generate dynamic web content, process form data, access databases, etc., with strong community support and open source resources. 3. PHP is an interpreted language, and the execution process includes lexical analysis, grammatical analysis, compilation and execution. 4.PHP can be combined with MySQL for advanced applications such as user registration systems. 5. When debugging PHP, you can use functions such as error_reporting() and var_dump(). 6. Optimize PHP code to use caching mechanisms, optimize database queries and use built-in functions. 7

The reasons why PHP is the preferred technology stack for many websites include its ease of use, strong community support, and widespread use. 1) Easy to learn and use, suitable for beginners. 2) Have a huge developer community and rich resources. 3) Widely used in WordPress, Drupal and other platforms. 4) Integrate tightly with web servers to simplify development deployment.

MySQL's position in databases and programming is very important. It is an open source relational database management system that is widely used in various application scenarios. 1) MySQL provides efficient data storage, organization and retrieval functions, supporting Web, mobile and enterprise-level systems. 2) It uses a client-server architecture, supports multiple storage engines and index optimization. 3) Basic usages include creating tables and inserting data, and advanced usages involve multi-table JOINs and complex queries. 4) Frequently asked questions such as SQL syntax errors and performance issues can be debugged through the EXPLAIN command and slow query log. 5) Performance optimization methods include rational use of indexes, optimized query and use of caches. Best practices include using transactions and PreparedStatemen

PHP is still dynamic and still occupies an important position in the field of modern programming. 1) PHP's simplicity and powerful community support make it widely used in web development; 2) Its flexibility and stability make it outstanding in handling web forms, database operations and file processing; 3) PHP is constantly evolving and optimizing, suitable for beginners and experienced developers.

PHP and Python each have their own advantages and are suitable for different scenarios. 1.PHP is suitable for web development and provides built-in web servers and rich function libraries. 2. Python is suitable for data science and machine learning, with concise syntax and a powerful standard library. When choosing, it should be decided based on project requirements.

PHP is suitable for web development, especially in rapid development and processing dynamic content, but is not good at data science and enterprise-level applications. Compared with Python, PHP has more advantages in web development, but is not as good as Python in the field of data science; compared with Java, PHP performs worse in enterprise-level applications, but is more flexible in web development; compared with JavaScript, PHP is more concise in back-end development, but is not as good as JavaScript in front-end development.
