使用 php 简化可以有多个输入选项的 MySQL 查询 [英] Simplify a MySQL query that can have multiple input options using php

问题描述

我有一个脚本可以查询我的数据库，并且需要能够使用四个选项中的一个或全部进行查询.选项是开始 sku、结束 sku(这是一个选项，因为必须有一个搜索范围)、创建者和创建日期.我知道我可以使用许多 if..elseif 语句来做到这一点，但我必须认为有一种更好、更简单的方法.到目前为止，这是我的代码:

$start =mysql_real_escape_string ($_POST['skuStart']);$end = mysql_real_escape_string($_POST['skuEnd']);$source = mysql_real_escape_string($_POST['source']);$processDate = mysql_real_escape_string($_POST['processDate']);if(!empty($start) && !empty($end) && empty($source) && empty($processDate)){$result = $conn->query("Select * from inventory where sku >= $start and sku <= $end");} elseif (empty($start) && empty($end) && !empty($source) && empty($processDate)){$result = $conn->query("Select * from inventory where created_by = '$source'");} elseif (empty($start) && empty($end) && empty($source) && !empty($processDate)) {$result = $conn->query("Select * from inventory where date_process = '$processDate'");} elseif(!empty($start) && !empty($end) && !empty($source) && empty($processDate)){$result = $conn->query("Select * from inventory where sku >= $start and sku <= $end and created_by = '$source'");} 别的 {$result = $conn->query("Select * from inventory where sku >= '$start' and sku <='$end' and created_by = '$source' and date_process = '$processDate'");}while($row = $result->fetch_assoc()){$skuArray[$x] = $row['sku'];$isbnArray[$x] = $row['isbn13'];$qtyArray[$x] = $row['quantity'];$defectArray[$x] = $row['defect_id'];$sourceArray[$x] = $row['source_id'];$featureArray[$x] = $row['feature_id'];$locationArray[$x] = $row['location_id'];$processDateArray[$x] = $row['date_process'];$bookTypeArray[$x] = $row['book_type_id'];$createdByArray[$x] = $row['created_by'];$modifiedByArray[$x] = $row['modified_by'];$x++;

这种非常粗糙的形式是有效的，但有没有办法简化 if elseif ?

注意:我知道我应该使用 PDO 来防止 sql 注入，但我还没有完全了解这一点，所以我正在使用它.

解决方案

搁置您已经意识到的问题 - 切换到 PDO 并使用绑定参数 - 您可以通过以下方式使您的代码更清晰:

>

query($query);while($row = $result->fetch_assoc()){$skuArray[$x] = $row['sku'];$isbnArray[$x] = $row['isbn13'];$qtyArray[$x] = $row['quantity'];$defectArray[$x] = $row['defect_id'];$sourceArray[$x] = $row['source_id'];$featureArray[$x] = $row['feature_id'];$locationArray[$x] = $row['location_id'];$processDateArray[$x] = $row['date_process'];$bookTypeArray[$x] = $row['book_type_id'];$createdByArray[$x] = $row['created_by'];$modifiedByArray[$x] = $row['modified_by'];$x++;

I have a script that will query my database and needs to be able to query using one or all of four options. The options are start sku, end sku (this is one option as there has to be a range to search), created by and date created. I know I can do this using MANY if..elseif statements, but I have to think there is a better and easier way. Here is what I have for code so far:

$start =mysql_real_escape_string ($_POST['skuStart']);
$end = mysql_real_escape_string($_POST['skuEnd']);
$source = mysql_real_escape_string($_POST['source']);
$processDate = mysql_real_escape_string($_POST['processDate']);

if(!empty($start) && !empty($end) && empty($source) && empty($processDate)){
$result = $conn->query("Select * from inventory where sku >= $start and sku <= $end");

 } elseif (empty($start) && empty($end) && !empty($source) && empty($processDate)){
$result = $conn->query("Select * from inventory where created_by = '$source'");

 } elseif (empty($start) && empty($end) && empty($source) && !empty($processDate)) {
$result = $conn->query("Select * from inventory where date_process = '$processDate'");

} elseif(!empty($start) && !empty($end) && !empty($source) && empty($processDate)){
$result = $conn->query("Select * from inventory where sku >= $start and sku <= $end and created_by = '$source'");

} else {
$result = $conn->query("Select * from inventory where sku >= '$start' and sku <= '$end' and created_by = '$source' and date_process = '$processDate'");
}

while($row = $result->fetch_assoc())
    {

        $skuArray[$x] = $row['sku'];
        $isbnArray[$x] = $row['isbn13'];
        $qtyArray[$x] = $row['quantity'];
        $defectArray[$x] = $row['defect_id'];
        $sourceArray[$x] = $row['source_id'];
        $featureArray[$x] = $row['feature_id'];
        $locationArray[$x] = $row['location_id'];
        $processDateArray[$x] = $row['date_process'];
        $bookTypeArray[$x] = $row['book_type_id'];
        $createdByArray[$x] = $row['created_by'];
        $modifiedByArray[$x] = $row['modified_by'];

        $x++;

This very crude form is working, but is there a way to get the if elseif simplified?

NOTE: I know that I should be using PDO to prevent sql injection, but I have not fully learned that yet, so I am using this.

解决方案

Setting aside the issues you're already aware of - switching to PDO and using bound parameters - You could make your code a little clearer with the following:

<?php

$start          = mysql_real_escape_string ($_POST['skuStart']);
$end            = mysql_real_escape_string($_POST['skuEnd']);
$source         = mysql_real_escape_string($_POST['source']);
$processDate    = mysql_real_escape_string($_POST['processDate']);
$where          = array();

if(!empty($start))
{
    $where[] = 'sku >= ' . $start;
}

if(!empty($end))
{
    $where[] = 'sku <= ' . $end;
}

if(!empty($source))
{
    $where[] = 'created_by = \''.$source .'\'';
}

if(!empty($processDate))
{
    $where[] = 'date_process = \''.$processDate .'\'';
}

$query = 'SELECT * FROM inventory';

if(count($where))
{
    $query .= ' WHERE ' . implode(' AND ', $where);
}

$result = $conn->query($query);

while($row = $result->fetch_assoc())
    {

        $skuArray[$x] = $row['sku'];
        $isbnArray[$x] = $row['isbn13'];
        $qtyArray[$x] = $row['quantity'];
        $defectArray[$x] = $row['defect_id'];
        $sourceArray[$x] = $row['source_id'];
        $featureArray[$x] = $row['feature_id'];
        $locationArray[$x] = $row['location_id'];
        $processDateArray[$x] = $row['date_process'];
        $bookTypeArray[$x] = $row['book_type_id'];
        $createdByArray[$x] = $row['created_by'];
        $modifiedByArray[$x] = $row['modified_by'];

        $x++;

这篇关于使用 php 简化可以有多个输入选项的 MySQL 查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！