CODEWITHSUNDEEP
phpmysql
112233
112233

Reputation: 2466

How to search through multiple set of dataset for columns and values in mysql query

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

$avail = $data['avail'];

//Below I'm categorizing values by morning, afternoon and evening.

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);
        }

Result for var_dump($cols);

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

Result for var_dump($days);

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

Now I query the table in the database like this: //part of the query

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

BUT only now, I've realized that I'm doing it the wrong way because the way I categorized the data made two arrays. One for session of the day and the other is dataset for each session altogether.

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

SO in the browser:

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

See that its checking all data for $days against each value of $cols. But what I want is, data for morning to check against morning column in the table while afternoon data to check against afternoon column in the table. Same goes for evening.

How do I change my code so that query would be like the following for the JSON dataset?

jason data: 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

Upvotes: 1

Views: 94

Answers (2)

cFreed
cFreed

Reputation: 4484

Let you guided by the result you're wanting: reading the query you need an array of OR'ed day-part conditions, each stating a list of week-days.

So you can work like this (tested, works fine):

$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) . ')';
}

Upvotes: 0

SOFe
SOFe

Reputation: 8214

Group the days by columns in an array first.

/* $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) . ")";

Upvotes: 1

Related Questions