时间戳

首页 > 解决方案 > 来自 sql 的 PHP 3 多选查询

php - 来自 sql 的 PHP 3 多选查询

问题描述

所以，我使用了我的谷歌朋友，但我在任何地方都找不到解决方案。情况如下，给定 3 个多项选择，您可以从中分别选择多个选项并仅列出其中的选项。我的意思是在 SQL 查询中：

从名字像“%2%”、年龄像“%1%”、工作像“%2%”的人中选择*

这是代码：

<form action="" method="post">
    <select class="selectpicker" id="name" name="name[]" multiple data-live-search="true">
  <option value="1">1</option>
    <option value="2">2</option>
</select>
<select class="selectpicker" id="age" name="age[]" multiple data-live-search="true">
  <option value="1">1</option>
    <option value="2">2</option>
</select>
<select class="selectpicker" id="work" name="work[]" multiple data-live-search="true">
  <option value="1">1</option>
    <option value="2">2</option>
</select>
</form>

标签： phphtmlmysqlmultiple-select

解决方案

如果select菜单中的值如上所述 - 即：整数，那么假设我正确解释了问题/请求，我认为您可以尝试以下操作。

<?php
    if( $_SERVER['REQUEST_METHOD']=='POST' ){
        /*
            filter the POST values - ensur ethey are integers
        */
        $_POST['name']=filter_input( INPUT_POST, 'name', FILTER_SANITIZE_NUMBER_INT, FILTER_REQUIRE_ARRAY );
        $_POST['age']=filter_input( INPUT_POST, 'age', FILTER_SANITIZE_NUMBER_INT, FILTER_REQUIRE_ARRAY );
        $_POST['work']=filter_input( INPUT_POST, 'work', FILTER_SANITIZE_NUMBER_INT, FILTER_REQUIRE_ARRAY );
        
        # base sql query
        $sql='select * from `people`';
        
        # arrays to store `like` clauses for each selected option
        $a=$b=$c=array();
        
        /*
            process all selected options and add to respective array
        */
        if( !empty( $_POST['name'] ) ){
            foreach( $_POST['name'] as $value ){
                if( !empty( $value ) ) $a[]='`name` like "%'.$value.'%"';
            }
        }
        if( !empty( $_POST['age'] ) ){
            foreach( $_POST['age'] as $value ){
                if( !empty( $value ) ) $b[]='`age` like "%'.$value.'%"';
            }
        }
        if( !empty( $_POST['work'] ) ){
            foreach( $_POST['work'] as $value ){
                if( !empty( $value ) ) $c[]='`work` like "%'.$value.'%"';
            }
        }
        /*
            construct final sql
        */
        if( !empty( $a ) or !empty( $b ) or !empty( $c ) ) $sql.=' where ';
        if( !empty( $a ) )$sql.= sprintf('( %s )', implode( ' OR ', $a ) );
        if( !empty( $b ) )$sql.= ' AND ' . sprintf('( %s )', implode( ' OR ', $b ) );
        if( !empty( $c ) )$sql.= ' AND '. sprintf('( %s )', implode( ' OR ', $c ) );
        
        #what you do with it now is up to you... 
        exit( $sql );
    }
    
?>
<!DOCTYPE html>
<html lang='en'>
    <head>
        <meta charset='utf-8' />
        <title></title>
        <style>
            select{width:100px}
        </style>
    </head>
    <body>
    
        <form method='post'>
            <select class='selectpicker' name='name[]' multiple data-live-search='true'>
                <option value=1>1
                <option value=2>2
            </select>
            <select class='selectpicker' name='age[]' multiple data-live-search='true'>
                <option value=1>1
                <option value=2>2
            </select>
            <select class='selectpicker' name='work[]' multiple data-live-search='true'>
                <option value=1>1
                <option value=2>2
            </select>
            
            <input type='submit' />
        </form>
        
    </body>
</html>

推荐阅读