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

AI Hentai Generator
Generate AI Hentai for free.

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 8.4 brings several new features, security improvements, and performance improvements with healthy amounts of feature deprecations and removals. This guide explains how to install PHP 8.4 or upgrade to PHP 8.4 on Ubuntu, Debian, or their derivati

One of the major changes introduced in MySQL 8.4 (the latest LTS release as of 2024) is that the "MySQL Native Password" plugin is no longer enabled by default. Further, MySQL 9.0 removes this plugin completely. This change affects PHP and other app

Visual Studio Code, also known as VS Code, is a free source code editor — or integrated development environment (IDE) — available for all major operating systems. With a large collection of extensions for many programming languages, VS Code can be c

This tutorial demonstrates how to efficiently process XML documents using PHP. XML (eXtensible Markup Language) is a versatile text-based markup language designed for both human readability and machine parsing. It's commonly used for data storage an

A string is a sequence of characters, including letters, numbers, and symbols. This tutorial will learn how to calculate the number of vowels in a given string in PHP using different methods. The vowels in English are a, e, i, o, u, and they can be uppercase or lowercase. What is a vowel? Vowels are alphabetic characters that represent a specific pronunciation. There are five vowels in English, including uppercase and lowercase: a, e, i, o, u Example 1 Input: String = "Tutorialspoint" Output: 6 explain The vowels in the string "Tutorialspoint" are u, o, i, a, o, i. There are 6 yuan in total

If you are an experienced PHP developer, you might have the feeling that you’ve been there and done that already.You have developed a significant number of applications, debugged millions of lines of code, and tweaked a bunch of scripts to achieve op

CMS stands for Content Management System. It is a software application or platform that enables users to create, manage, and modify digital content without requiring advanced technical knowledge. CMS allows users to easily create and organize content

Arrays are linear data structures used to process data in programming. Sometimes when we are processing arrays we need to add new elements to the existing array. In this article, we will discuss several ways to add elements to the end of an array in PHP, with code examples, output, and time and space complexity analysis for each method. Here are the different ways to add elements to an array: Use square brackets [] In PHP, the way to add elements to the end of an array is to use square brackets []. This syntax only works in cases where we want to add only a single element. The following is the syntax: $array[] = value; Example
