英文:
Mysql query with optional where condition
问题
我有一个表单，用于搜索数据库。该表单包括四个字段，包括两个日期字段：（Col1，Col2，Datefm，DateTo）。用户可以选择填写这些字段中的值。他们可以填写一个字段，两个字段，三个字段或所有字段的值。基于这些值，我的查询必须搜索数据库。
我创建了一个查询，用于检索数据，但仅当所有字段中的值都可用时才有效。我希望它排除用户未填写的条件。我该如何实现这一点？
我尝试了以下代码。
SELECT *
FROM Table1
WHERE tcol1=$col1
AND tcol2=$col2
AND tDateto=$Dateto
AND tdatefm=$Datefm
英文:
I have a form which is used to search the database. The form consists of four fields, including two date fields: (Col1, Col2, Datefm, DateTo). Users have the option to fill the values in these fields. They may fill the value in one field or two field or three field or all fields. Based on these values my query has to search the database.
I made a query which fetches the data, but it works only when all the values in the fields are available. I want it to exclude the where conditions which are not filled by the user. How can i achieve that?
I tried the following code.
SELECT *
FROM Table1
WHERE tcol1=$col1
AND tcol2=$col2
AND tDateto=$Dateto
AND tdatefm=$Datefm
答案1
得分: 2
如ysth在上面的评论中指出的，您应该根据填充了哪些表单字段来动态构建您的WHERE子句。
以下是一个适用于所提出的用例的非常简单的示例（基于mysqli，因为您的问题最初标记为mysqli）：
$where = [];
$params = [];
if (isset($_GET['Col1']) && !empty($_GET['Col1'])) {
$where[] = 'tcol1 = ?';
$params[] = $_GET['Col1'];
}
if (isset($_GET['Col2']) && !empty($_GET['Col2'])) {
$where[] = 'tcol2 = ?';
$params[] = $_GET['Col2'];
}
if (isset($_GET['Datefm']) && !empty($_GET['Datefm'])) {
$where[] = 'tdatefm = ?';
$params[] = $_GET['Datefm'];
}
if (isset($_GET['Dateto']) && !empty($_GET['Dateto'])) {
$where[] = 'tDateto = ?';
$params[] = $_GET['Dateto'];
}
$whereClause = count($where) ? ' WHERE ' . implode(' AND ', $where) : '';
$stmt = $mysqli->prepare('SELECT * FROM Table1' . $whereClause);
$stmt->execute($params);
英文:
As ysth has pointed out in the comments above, you should build your WHERE clause dynamically based on which form fields are populated.
Here's a very simple example which will work for the presented use case (mysqli based as your question was originally tagged with mysqli):
$where = [];
$params = [];
if (isset($_GET['Col1']) && !empty($_GET['Col1'])) {
$where[] = 'tcol1 = ?';
$params[] = $_GET['Col1'];
}
if (isset($_GET['Col2']) && !empty($_GET['Col2'])) {
$where[] = 'tcol2 = ?';
$params[] = $_GET['Col2'];
}
if (isset($_GET['Datefm']) && !empty($_GET['Datefm'])) {
$where[] = 'tdatefm = ?';
$params[] = $_GET['Datefm'];
}
if (isset($_GET['Dateto']) && !empty($_GET['Dateto'])) {
$where[] = 'tDateto = ?';
$params[] = $_GET['Dateto'];
}
$whereClause = count($where) ? ' WHERE ' . implode(' AND ', $where) : '';
$stmt = $mysqli->prepare('SELECT * FROM Table1' . $whereClause);
$stmt->execute($params);
答案2
得分: 0
你可以尝试使用这个 OR 函数，就像下面的示例一样：
SELECT *
FROM Table1
WHERE ($col1 IS NULL OR tcol1 = $col1)
AND ($col2 IS NULL OR tcol2 = $col2)
AND ($Dateto IS NULL OR tDateto = $Dateto)
AND ($Datefm IS NULL OR tdatefm = $Datefm);
英文:
You can try using this OR function. like the one below
`SELECT *
FROM Table1
WHERE ($col1 IS NULL OR tcol1 = $col1)
AND ($col2 IS NULL OR tcol2 = $col2)
AND ($Dateto IS NULL OR tDateto = $Dateto)
AND ($Datefm IS NULL OR tdatefm = $Datefm);`
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论