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

Question

杰森数据: 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))));

但直到现在，我才意识到我做错了，因为我对数据进行分类的方式形成了两个数组。一个用于当天的 session ，另一个是每个 session 的数据集。

$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'))

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

如何更改我的代码，以便查询类似于以下 JSON 数据集？

杰森数据: avail":["Sun-2","Mon-2","Sun-3"]

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

values for morning, afternoon and evening stored as a comma separated values in the database. morning, afternoon and evening are column names in a table named 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) . ")";