Home>

For the title, $date contains 0-8 dates (example: 2018-08-29). I want to iterate over the number of stored data to add to the WHERE OR condition of the MySQL SELECT statement (FOR loop?). Is this way possible with SQL? You can run the query for the number of data items, but it feels like it will take some time to process, and I felt it was not very beautiful.

function search_from_date ($dbh, $date) {
  if (! empty ($date)) {
    $cnt1 = count ($date);
  }
  $active_days_str = get_active_days_str ();
  $cnt2 = count ($active_days_str);
  if (empty ($cnt1) || $cnt1 == $cnt2) {
    $sql =<<<SQL
    SELECT `id`
    FROM `schedule`
SQL;
    $stmt = $dbh->prepare ($sql);
    if ($stmt->execute ()) {
      $teacher_id = $stmt->fetchAll ();
      return $teacher_id;
    } else {
      return FALSE;
    }
  } else {
// I want to increase the OR condition of the SQL WHERE statement $cnt1 times when the date is stored in $date ...!
  }
}


I want to perform an OR search on ids with 1 referring to multiple column names from the virtual array $date.

CREATE TABLE `schedule` (` id` varchar (20) NOT NULL, `2018-09-23` int (1) DEFAULT NULL,` 2018-09-22` int (1) DEFAULT NULL, `2018-09-16` int (1) DEFAULT NULL,` 2018-09-15` int (1) DEFAULT NULL, `2018-09-09` int (1) DEFAULT NULL,` 2018-09-08` int (1) DEFAULT NULL, `2018-09-02` int (1) DEFAULT NULL,` 2018-09-01` int (1) DEFAULT NULL)
INSERT INTO `schedule` (` id`, `2018-09-23`,` 2018-09-22`, `2018-09-16`,` 2018-09-15`, `2018-09-09`, `2018-09-08`,` 2018-09-02`, `2018-09-01`) VALUES ('honda', NULL, 1, 1, 1, 1, 1, 1, 0)
  • Answer # 1

    Is MySPL typo?

    SELECT .... WHERE COL1 in (a, b, c, ...)

    Is it better to write

    ?

  • Answer # 2

    It's common to assign several data candidates to a specific column

    $d = ["2018-08-29", "2018-12-31", "2019-01-01"];
    $data = [];
    $sql = "SELECT` id` FROM `schedule` WHERE 1";
    if (count ($d)>0) {
      $sql. = "AND` d` IN (".implode (", ", array_fill (0, count ($d),"? ")).") ";
      $data = array_merge ($data, $d);
    }
    print $sql;
    print_R ($data);
    / *
    $pdo = new PDO ($dsn, $user, $password);
    $stmt = $pdo->prepare ($sql);
    $stmt->execute ($data);
    $rows = $stmt->fetchAll (PDO :: FETCH_ASSOC);
    */
    sample

    For the time being, using the presented table, it looks like this

    $date = ["2018-08-29", "2018-09-02", "2018-09-08"];
    $sql = "SELECT` id` FROM `schedule` WHERE 1";
    $cols = ["2018-09-23", "2018-09-22", "2018-09-16", "2018-09-15", "2018-09-09", "2018-09-08 "," 2018-09-02 "," 2018-09-01 "];
    $sql. = "AND (0";
    foreach ($date as $d) {
      if (in_array ($d, $cols)! == false) {
        $sql. = "OR` {$d} `= 1";
      }
    }
    $sql. = ")";
    print $sql;

    A list of column names is reserved in $cols as a white list

    If the given $date element ($d) exactly matches $cols, use it as the column name
    Check if it matches 1

    How to connect (0 OR condition 1 OR condition 2 ...) for AND processing because it is an OR search