How to correctly set an SQL query to the database to get it in a given interval?

Question

PHP

Good day!

The situation is this. There is a site with courses. Courses are recorded in the database. They have fields start_event (start of the course) and end_event (end of the course).

I need to send a query to a db where I have two known variable variables data_start = start and data_end = end.
The task is to obtain from the database all the courses, the duration of which falls within this interval, having these variables.
That is, if a person is on 11/15/2020 and 11/20/2020, he should be given all the courses that take place in this interval.

Thank you!

Answer 1

1. The query to the database depends on the format in which you store dates in the database (DATETIME, TIMESTAMP/int() or God forbid varchar)
2. The data that the user sent will most likely need to be converted ( DateTime , strtotime() ), before submitting a query to the DB.
For DATETIME or TIMESTAMP the query would be:

SELECT * FROM courses 
WHERE 
    ( start_event BETWEEN $data_start AND  $data_end ) AND
    ( end_event BETWEEN $data_start AND  $data_end )

Or the same without BETWEEN

SELECT * FROM courses 
WHERE 
    ( start_event >= $data_start AND start_event <= $data_end ) AND
    ( end_event >= $data_start AND end_event <= $data_end )

Answer 2

I think, it is necessary to do sampling from a DB. Something like "SELECT * FROM someTable WHERE
date = 10/10/2020 AND date = 10/11/2020" (Greater than or less than signs instead of equals, don't remember). Pre-shamanize something with the dates in order to send them correctly to the database. In PHP it will look something like this:

$dsn = 'mysql:host=my_host;dbname=my_dbname';
$user = 'user_name';
$pass = 'password';
$opt = [
    PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION,
    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC, //FETCH_NAMED; FETCH_NUM;
    PDO::ATTR_EMULATE_PREPARES   => false,
];

$pdo = new PDO($dsn, $user, $pass, $opt);

global $data;

$param1 = $_POST['date1'];
$param2 = $_POST['date2'];
 $stmt = $pdo->prepare("SELECT * FROM catalog WHERE param1 = ? AND param2 = ?");
if ($stmt->execute([$param1, $param2])) {
   while ($row = $stmt->fetch()) {
   $data[$row['id']] = $row;
 }
}

See what happened:

function debug($arr){
    echo '<pre>' . print_r($arr, 1) . '</pre>';
};
debug($data);