php实现数据库全文搜索功能

---

---

提示：以下是本篇文章正文内容，下面案例可供参考

一、php连接数据库

新建一个linksql.php文件

<?php 
	$server = "localhost"; //本地服务器名
	$user = "root"; //用户名
	$password = "root"; //密码
	$db = "shoping";  //数据库名（要链接的数据库）
	function connect($servername,$username,$password,$dbname){
	    // 创建连接
	    $conn = mysqli_connect($servername, $username, $password, $dbname);
	    // 检测连接
	    if ($conn->connect_error) {
	        die("连接失败: " . $conn->connect_error);
	    }else{
	        // echo 'cg';
			return $conn;
	    }
	};
	
?>

二、查询所有数据库表

新建一个getTable.php文件

<?php
 function getTableAll($conn){
    $sql = 'SHOW TABLES';
    //执行数据库语句
    $result = mysqli_query($conn,$sql);
    if ($result->num_rows > 0) {
       // 输出数据
       $data = [];
       while($row = $result->fetch_assoc()) {
           $data[] = $row;
       }
       return $data;
   } else {
       echo "0 结果";
   }
 }

?>

三、查询对应表中的所有字段名

新建一个selectColum.php文件

<?php
    function selectCol($conn,$table){
        $sql = "SHOW COLUMNS FROM $table";
        $result = mysqli_query($conn,$sql);
        
        if ($result->num_rows > 0) {
            // 输出数据
            $col = [];
            while($row = $result->fetch_assoc()) {
                $col[] = $row['Field'];
            }
            return $col;
        } else {
            echo "0 结果";
        }
        $conn->close();
    }
?>

四、通过搜索关键字查询数据

查询接口

<?php
    // SELECT * from goods where name like '%128g%' or tagr like '%128g%'
    //  全表模糊查询
    header('Content-Type: text/html;charset=utf-8');
    header('Access-Control-Allow-Origin:*'); // *代表允许任何网址请求   
    //倒入刚才准备的文件
    include('../mysql/linksql.php');
    include('../mysql/getTable.php');  
    include('../mysql/selectColum.php');
    //链接数据库
    $conn =  connect($server,$user,$password,$db );
    //查询一条
    function selectWithOne($conn,$table,$where,$keyWords){
        $col = explode(" like ",$where)[0];
        $sql = "SELECT id ,name, $col from $table where $where";
        $result = mysqli_query($conn,$sql);
        if ($result->num_rows > 0) {
            // 输出数据
            $searchGoodsList = [];
            while($row = $result->fetch_assoc()) {
                foreach( $row as $k => $v ){
                  
                    if($k == 'tagr'){
                        // echo $k . '$k--';
                        $vv = json_decode($v,true);
                        foreach($vv as $ky => $val){
                            foreach( $val as $a => $b ){
                                $newval = json_encode($b);
                                if(strpos($newval,$keyWords) !== false){
                                    $row[$k] = json_decode($newval);
                                }
                            }
                        }
                    }
                }
                $searchGoodsList[] = $row;

            }
            return $searchGoodsList;
        } 
    }
    //查询全部
    function searchIntableAll($conn,$table,$colArr,$keyWords){
        $newKeyWords = "'%".$keyWords."%'";
        $newArr = [];
        foreach($colArr as $v){
            $arr = [];
            $arr[] = $v;
            $arr[] = $newKeyWords;
            $onSearch = ['id','imgs','controlstate','user','create_time'];
            if(!in_array($v,$onSearch)){
                $newArr[] =  implode(" like ",$arr);
            }
        }
        return $newArr;
    }
   // 查询所有
    function searchDataAll($conn,$table,$keyWords){
        $colArr =  selectCol($conn,$table);
        $WHERE =  searchIntableAll($conn,$table,$colArr,$keyWords);
        $Arr = [];
        foreach( $WHERE as $v ){
            if(is_array(selectWithOne($conn,$table,$v,$keyWords))){
                $Arr[] = selectWithOne($conn,$table,$v,$keyWords);
            }
        }
        return $Arr;
    } 
    // $data = searchDataAll($conn,'goods','128');
    $data =  searchDataAll($conn,'goods',$_POST['key']);
    // print_r($data);
    echo json_encode($data);
?>