PHP MySQL根据变量更改选择查询

Question

我有 4 个变量(年、月、项目代码和类型)。

因此，如果此人提交年份并将其他 3 个变量留空，则选择查询必须是select * from table where year(trandate) = $var
但是，如果用户提供年份和月份，则查询必须是 select * from table where year(trandate) = $var and month(trandate) = $var1

如果用户选择年、月和项目代码并将类型留空，则查询必须是select * from table where year(trandate) = $var and month(trandate) = $var1 and projcode = $var3

等等。我该如何对此进行编程，否则我会有很多组合？

希望问题清楚。

例如，这是我目前所拥有的，但我可以看到组合太多:

if (empty($ej1year) && empty($ej1month) && empty($ej1proj) && empty($ej1type)) {
$rows = mysql_query("SELECT a.employee
,a.trandate
,CONCAT(a.workdone, '-', wc.BriefDescription) as WorkCodeActivity
,CONCAT(a.custname, '-', cl.ShortName) as clientdet
,a.qty
,a.rate
,a.amount
,a.ref
,a.projcode
,a.type
,a.qty*a.rate as costrate
FROM transaction as a
LEFT JOIN workcodes as wc ON a.workdone=wc.WorkCodeNo
LEFT JOIN clients as cl On a.custname=cl.EntityNo");
} elseif (empty($ej1year) && !empty($ej1month)) {
$rows = mysql_query("SELECT a.employee
,a.trandate
,CONCAT(a.workdone, '-', wc.BriefDescription) as WorkCodeActivity
,CONCAT(a.custname, '-', cl.ShortName) as clientdet
,a.qty
,a.rate
,a.amount
,a.ref
,a.projcode
,a.type
,a.qty*a.rate as costrate
FROM transaction as a
LEFT JOIN workcodes as wc ON a.workdone=wc.WorkCodeNo
LEFT JOIN clients as cl On a.custname=cl.EntityNo
where month(trandate) = '$ej1month'");

} elseif

Answer 1

像这样的东西应该可以工作:

<?php
$where = array();
$binds = array();

if ($_POST['month'] !== '') {
    $where[] = 'month = ?';
    $binds[] = $_POST['month'];
}
if ($_POST['year'] !== '') {
    $where[] = 'year = ?';
    $binds[] = $_POST['year'];
}
...

$query = 'select * from table where ' . implode(' AND ', $where);
$db->execute($query, $binds);

您想要添加检查以查看是否设置了任何变量。如果你不介意全部为空，你可以改变

$where = array();

到

$where = array(1);

这将在查询中以“where 1”结束，有效地选择所有内容。

编辑:我看到您正在使用 mysql_ 函数，这不像 they are deprecated 那样理想。 .您应该尽快更新到 PDO 或 mysqli。这是一个可以与 mysql 一起使用的版本_

<?php
$where = array();

if ($_POST['month'] !== '') {
    $where[] = "month = '" . mysql_real_escape_string($_POST['month'])  . "'";
}
if ($_POST['year'] !== '') {
    $where[] = "year = '" . mysql_real_escape_string($_POST['year'])  . "'";
}
...

$query = 'select * from table where ' . implode(' AND ', $where);
$result = mysql_query($query);