How to use PHP’s ” .date(‘Y-m-d’). ” in a MySQL query in Where clause?

I have cloned a movie theatre project where the movie_carousel.php uses a query to fetch movies from the MySQL database.

However it doesn’t fetch any data when I run it, only when I remove the entire Where clause from the query – then it fetches the movies.

I believe it has something to do with the .date('Y-m-d'). in the script.

What things I could try to fix it?

Here’s the code of movie_carousel.php:

<?php 
  include 'admin/db_connect.php';
  $movies = $conn->query("SELECT * FROM movies where '".date('Y-m-d')."' BETWEEN date(date_showing) and date(end_date)" );
?>

Answer

It is better to use SQL rather than PHP when possible.

So you can replace :

$movies = $conn->query("SELECT * FROM movies where '".date('Y-m-d')."' BETWEEN date(date_showing) and date(end_date)" );

By:

$movies = $conn->query("SELECT * FROM movies WHERE DATE(NOW()) BETWEEN DATE(date_showing) and DATE(end_date)" );

Also check if you really have datas in your table for now.

Fiddle: https://www.db-fiddle.com/f/heYtYGoChmz28XeT7mzGsp/0#&togetherjs=bguyuSjnva