如何在多组数据集中搜索mysql查询中的列和值

Question

jason data: avail":["Sun-2","Mon-2","Sun-3"]

$avail = $data['avail'];

//下面我按上午、下午和晚上对价值进行分类。

if($avail != ""){
        foreach($avail as $k=>$v)
        {
             $v;
           //dividing value before and after the symbol '-'
            $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";
            }
            array_push($cols,$col);
            array_push($days,$day);
        }

Var_dump($cols)结果；

array (size=3)
  0 => string 'afternoon' (length=9)
  1 => string 'afternoon' (length=9)
  2 => string 'evening' (length=7)

Var_dump($days)结果；

array (size=3)
  0 => string 'Sun' (length=3)
  1 => string 'Mon' (length=3)
  2 => string 'Sun' (length=3)

现在，我查询数据库中的表，如://查询的一部分

$add_here .= sprintf(" AND %s",implode(' OR ', array_map(function($colx) use ($days) {return sprintf("posts.%s IN ('%s')", $colx, implode("','",$days));}, is_array($cols) ? $cols : array($cols))));

但直到现在，我才意识到我这样做是错误的，因为我对数据进行分类的方式形成了两个数组。一个是一天中的会话，另一个是每个会话的数据集。

$days = ['sun','mon','tue']; <- data for morning,afternoon and evening mixed together
$col = ['afternoon','morning','evening'];

所以在浏览器中：

AND (posts.afternoon IN ('sun','mon','tue') OR posts.morning IN ('sun','mon','tue') OR posts.night IN ('sun','mon','tue'))

确保它根据$days的每个值检查$cols的所有数据。但是我想要的是，早上的数据和表中的上午列核对，下午的数据则对照表格中的下午列进行检查。晚上也一样。

如何更改代码，使JSON数据集的查询如下所示？

jason data: avail":["Sun-2","Mon-2","Sun-3"]

AND (posts.afternoon IN ('sun','mon') OR posts.night IN ('sun'))

作为逗号存储在数据库中的上午、下午和晚上的值。上午、下午和晚上是一个名为posts的表格中的列名。

Answer 1

先按数组中的列分组天数。

/* $periods[$column] = [$day1, ...] */
$periods = [];
foreach($avail as $v){
    list($day, $column) = explode("-", $v); // make sure you validated the data to avoid errors
    $periods[$column][] = "'" . mysqli_escape_string(strtolower($day)) . "'"; // PHP would automatically create an empty array if $periods[$column] was not defined
}
$intToCol = [1 => "morning", 2 => "afternoon", 3 => "night"];

// $periods should now be identical to ["2" => ["'sun'", "'mon'"], "3" => ["'sun'"]]

$conditions = [];
foreach($periods as $int => $days){
    $dayString = implode(",", $days);
    $conditions[] = "posts." . $intToCol[$int] . " IN ($dayString)";
}
return "AND (" . implode(" OR ", $conditions) . ")";

Answer 2

让您以您想要的结果为指导:读取您需要一个OR‘’ed日部分条件数组的查询，每个条件都声明一个周-天的列表。

这样你就可以像这样工作(测试，工作得很好)：

$avail = ["Sun-2","Mon-2","Sun-3"];

$day_parts = [
  'morning',
  'afternoon',
  'evening',
];
$decoded = [];
if ($avail) {
  foreach ($avail as $v) {
    //dividing value before and after the symbol '-'
    $array = explode('-', $v);
    // register week_day as a key rather than a value, avoiding duplicates:
    $decoded[$day_parts[$array[1] - 1]]['"' . $array[0] . '"'] = TRUE;
  }
}

$ors = [];
foreach ($decoded as $day_part => $week_days) {
  if ($week_days) {
    $ors[] = 'posts.' . $day_part . ' IN (' . implode(',', array_keys($week_days)) . ')';
  }
}
if ($ors) {
  $where_part = ' AND (' . implode(' OR ', $ors) . ')';
}