我用逗号分隔的字符串中的三列存储所有注册用户的可用性,如下所示:
user 1:
teaching: english
morning = '';
afternoon = 'Thrs,Fri,Sat,Sun';
evening = 'Thrs,Fri,Sat,Sun';
User 2
teaching: english
morning = 'Sat';
afternoon = 'Sat,Sun,Fri';
evening = 'Sat,Sun,Fri';
假设我搜索周六下午和晚上有空的导师。我以为查询会返回它们,因为用户的下午和晚上列都有"星期六"。但它返回空数组。
这是我的浏览器解释查询。我在phpmyadmin中运行了这个,但没有返回任何结果。
SELECT * , (3956 * 2 * ASIN(SQRT( POWER(SIN(('2.993518' - lat) * pi()/180 / 2), 2) +COS('2.993518' * pi()/180) * COS(lat * pi()/180) * POWER(SIN(('101.7874058' - lon) * pi()/180 / 2), 2) ))) as distance from posts WHERE posts.subname LIKE '%english%' AND posts.pricing <= '115'
和(posts.pm in ('Sat'( OR posts.evening IN ('Sat'(( GROUP BY posts.UUID having distance <= '40' order by distance
同样的方式,我试图在下午和晚上"星期四"寻找可用的用户。我想它应该返回 user1,因为周四只有用户 1 可用,但它不返回任何结果:
SELECT * , (3956 * 2 * ASIN(SQRT( POWER(SIN(('2.993518' - lat) * pi()/180 / 2), 2) +COS('2.993518' * pi()/180) * COS(lat * pi()/180) * POWER(SIN(('101.7874058' - lon) * pi()/180 / 2), 2) ))) as distance from posts WHERE posts.subname LIKE '%flute%' AND posts.pricing <= '115'
和(帖子.下午在("Thrs"(或帖子.evening IN ("Thrs"(GROUP BY posts.UUID having distance <= '40' order by distance
那么当它返回结果时呢?
当我搜索周六的早晨,下午和晚上。我只是不明白这个查询如何执行搜索。为什么我没有得到预期的结果,我的错误在哪里?如果有人帮助我解决这个问题,那将很有帮助,因为我在这方面浪费了很多天!
上述的完整 PHP 代码:
<?php
JSON formatted data received via ajax
$return = '{"avail":["Wed-2,Thrs-2","Thrs-3"]}';
In the above -1 means for monrning, -2 for afternoon and -3 for evening.I'm categorising them below.'
$avail = $data['avail'];
$days = array();
$cols = array();
$size = sizeof($avail);
if(($avail != "")&&($size > 1)){
$periods = array();
foreach($avail as $v){
list($day, $column) = explode("-", $v); // make sure you validated the data to avoid errors
$periods[$column][] = "'" . mysql_escape_string($day) . "'"; //strtolower// PHP would automatically create an empty array if $periods[$column] was not defined
}
$intToCol = array(1 => "morning", 2 => "afternoon", 3 => "evening");
// $periods should now be identical to ["2" => ["'sun'", "'mon'"], "3" => ["'sun'"]]
$conditions = array();
foreach($periods as $int => $days){
$dayString = implode(",", $days);
$conditions[] = " FIND_IN_SET ($dayString, posts." . $intToCol[$int].")" ;
}
$add_here = "AND (" . implode(" OR ", $conditions) . ")";
}else if(($avail != "")&&($size == 1))
{
foreach($avail as $k=>$v)
{
$v;
$array = explode('-', $v);
$day =$array[0]; // Wed
$column = $array[1]; // 2
if($column == 1)
{
$col = "morning";
}
if($column == 2)
{
$col = "afternoon";
}
if($column == 3)
{
$col = "evening";
}
}
$add_here = " posts.".$col." = '".$day."' ";
$sql = "SELECT * , (3956 * 2 * ASIN(SQRT( POWER(SIN(('$lat' - lat) * pi()/180 / 2), 2) +COS('$lat' * pi()/180) * COS(lat * pi()/180) * POWER(SIN(('$lon' - lon) * pi()/180 / 2), 2) ))) as distance from posts WHERE posts.subname LIKE '%$subject%' AND posts.pricing <= '$rate' ".$add_here."".$IsTutionCentre." GROUP BY posts.UUID having distance <= '$distance' order by distance";
$stmt =connection::$pdo->prepare($sql);
$stmt->execute();
$place=array();
while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
$place[] = $row;
}
$_SESSION['subject'] = $place;
//send back to ajax call made page
echo json_encode($place);
}
?>
afternoon = 'Thrs,Fri,Sat,Sun';
evening = 'Thrs,Fri,Sat,Sun';
当一组值作为 csv 存储在单个行字段中时,使用 'IN'
将不起作用。您必须使用"> FIND_IN_SET
'
更改:
AND (posts.afternoon IN ('Sat') OR posts.evening IN ('Sat'))
至:
AND ( FIND_IN_SET( 'Sat', posts.afternoon )
OR FIND_IN_SET( 'Sat', posts.evening ) )
您可以将相同的应用于其他日期事件。
文档参考:
FIND_IN_SET(str,strlist)
:
- 返回一个介于 1 到 N 范围内的值,如果字符串 str 位于字符串列表 strlist 由 N 个子字符串组成。字符串列表是一个由用","字符分隔的子字符串组成的字符串。如果第一个参数是常量字符串,第二个参数是类型SET,FIND_IN_SET((函数优化使用位算术。如果 str 不在 strlist 中,或者如果 strlist 是空字符串。如果任一参数为 NULL,则返回 NULL。此函数如果第一个参数包含逗号 (","(,则无法正常工作字符。