我具有以下数据库结构，我想从特定用户和月份中选择所有内容。但是，它会返回所有月的特定用户数据。
我在此示例中选择的月份是2014-02或2014年2月
date |user | some more data |
----------------------------------------
2014-01-20 |user1 | more data | //will not be skipped
2014-02-01 |user1 | more data |
2014-02-01 |user2 | more data | //will be skipped
2014-02-02 |user1 | more data |
2014-05-02 |user1 | more data | //will not be skipped
我通过准备好的语句函数调用以下查询。LIKE运算子似乎无法运作。
$query = "SELECT * FROM table1 WHERE user = ? AND date LIKE ?"
$user = "user1"; //These 2 actually come from user input so:
$date = "2014-02%"; //$user = $username; $date = $y."-".$m."%";
//function that runs the prepared statement (simplified)
//this function works for all other queries.
$result = $database->runQuery($query, array($user, $date));
while($row = mysqli_fetch_array($result){
echo $row['date'];
echo $row['user'];
echo $row['some more data'];
echo "<br>";
}
此打印
2014-01-20 user1 more data //this line should not be printed but it does.
2014-02-01 user1 more data
2014-02-02 user1 more data
2014-05-02 user1 more data //also should not be printed.
所以我的问题是，我对LIKE运算符做错了什么？
我的实际查询。
//The system holds phone call data.
//dcontext is a variable that decides in which column the user is found.
//in case of dcontext = outgoing calls
//clid = username + extensions
//src = users phone number
//dst = to phone number (this can be extension from another user in the system)
//which wont be registered double in this case.
//when dcontext = incomming calls
//clid and src = the caller ID/phone number from the person calling.
//dst = the users phone number.
//when dcontext = internal call transfer
//clid = username + extension
//src = users phone number
//dst = transfered number
"SELECT * FROM table WHERE
(dcontext = ? AND (clid = ? OR src = ? OR dst = ? OR dst = ?)) OR
(dcontext = ? AND dst = ?) OR
(dcontext = ? AND (clid = ? OR src = ?)) AND
date LIKE ?"
date type = DateTime
我认为您应该CAST将日期string格式化，因此请尝试：
SELECT * FROM table1 WHERE user = ? AND CAST(date as char) LIKE ?
或使用DATE_FORMAT功能
SELECT * FROM table1 WHERE user = ? AND DATE_FORMAT(date, '%Y-%m-%d') LIKE ?
编辑：
过滤日期部分的另一种方法是对日期部分进行过滤extract：
SELECT * FROM table1 WHERE user = ? AND EXTRACT(year FROM date) = ? AND EXTRACT(month from date) = ?
但是您必须通过year和month作为单独的参数
编辑2：
我将查询的某些元素放在大括号中，以确保您的AND操作员能够正常工作
尝试使用以下查询：
SELECT * FROM table WHERE
((dcontext = ? AND (clid = ? OR src = ? OR dst = ? OR dst = ?)) OR
(dcontext = ? AND dst = ?) OR
(? AND (clid = ? OR src = ?))) AND (EXTRACT(year FROM date) = ? AND
(EXTRACT(month FROM date) = ? )
本文收集自互联网，转载请注明来源。
如有侵权，请联系[email protected] 删除。
我来说两句