围绕可选搜索参数构建 SQL 查询 [英] Constructing an SQL query around optional search parameters

问题描述

我正在尝试围绕 PHP 中的一些可选搜索参数构建 MySQL 查询.有 3 个搜索字段链接到我的数据库中的三列.用户应该能够单独搜索每一列，如果他们想缩小结果范围，则最多可以搜索所有 3 列.

I'm attempting to construct an MySQL query around some optional search parameters in PHP. There are 3 search fields linking to the three columns in my database. The user should be able to search each column individually, or up to all 3 if they want to narrow down their results.

我的 PHP 代码是这样构建的，如果搜索字段之一为空，则无关紧要，它将继续在其他两个字段中进行搜索.但是，由于我的 where 子句已经在 $whereclauses 数组中指定，我正在努力弄清楚如何构建我的 SQL 查询之后.我在用户进行的任何搜索中都返回相同的列，这样应该会使 SQL 查询变得非常简单，并且意味着它只需要定义一次.

My PHP code is built so that the if one of the search fields is empty, it doesn't matter and it will continue the search in the other two fields. However, I'm struggling to get my head around how to construct my SQL query afterwards as my where clauses have already been specified in the $whereclauses array. I am returning the same columns in any search the user makes so that should make the SQL query quite simple and mean it only needs to be defined once.

我的 PHP 代码:

<?php
require '../db/connect.php';
$whereclauses = array();
$subsets = false;

// for every field
if(!empty($_POST['name']))
  {
  $subsets = true;
  $whereclauses[] = " ARTIST = ". mysql_real_escape_string(trim($_POST['name']));
  }

if($subsets)
  {
  $whereclauses = implode(", ". $whereclauses);
  }
else
  {
  $whereclauses ="";
  }

PHP 中的 SQL 查询

 if(!empty($whereclauses)) {
 $sql = "SELECT 
            `ARTIST`, `TRACKTITLE`, `DATE`, `LOCATION`
        FROM 
            `table 3`";
 };    
 $result = mysql_query($sql);
 $data = array();
 while ($array = mysql_fetch_assoc($result)) {
 $data[] = $array;
 }
 header('Content-type: application/json');
 echo json_encode($data);

如何将 $whereclauses 数组添加到我的 $sql 查询中?

How can I add the $whereclauses array into my $sql query?

推荐答案

<?php
require '../db/connect.php';
$whereclauses = array("where 1=1");
$subsets = false;

// for every field
if(!empty($_POST['name']))
  {
  $subsets = true;
  $whereclauses[] = " ARTIST = '". mysql_real_escape_string(trim($_POST['name']))."'";
  }

只需在 sql 中内爆数组

Just implode the array in sql

$sql = "SELECT 
            `ARTIST`, `TRACKTITLE`, `DATE`, `LOCATION`
        FROM 
            `table 3` ".implode(" AND ",$whereclauses);

这篇关于围绕可选搜索参数构建 SQL 查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！