从数据库中获取某个时间段内可用的每个项目

Question

I want to echo each car ID which is available in a period between the start_date and the end_date. So far i got this with some help here on SO. This gives me a page not responding, if I delete the second part (commented in the script down here) it is eching all the car id's but not only the ones which are available ofcourse. What is wrong with the script? Because i cant figure out what i did wrong here.

<?php
$start_date = '2017-06-12';
$end_date = '2017-06-14';
$items = array();

$result = mysqli_query($con, "SELECT * FROM invoice_line ");
while ($car = mysqli_fetch_array($result)) {
  echo $car['car_car_id'];
  //second part
  $car_available = mysqli_query($con, "SELECT * 
                             FROM invoice_line 
                             WHERE car_car_id = $car['car_car_id'] 
                              and start_date>='$start_date' 
                              and end_date<='$end_date'");
  echo $car_available['car_car_id'];
} // end second part

?>

Answer 1

The process you are doing is: 1. fetch all lines from invoice_line 2. for each line, fetch all lines between two dates.

You do it wrong, because you do not have to use two queries. You can use one:

<?php
$start_date = '2017-06-12';
$end_date = '2017-06-14';

$result = mysqli_query($con, "SELECT car_car_id FROM invoice_line where start_date>='$start_date' AND end_date<='$end_date'");
while ($car = mysqli_fetch_array($result)) {
  echo $car['car_car_id'];
} // end second part

?>

I would add distinct for car_car_id, in order to get only one line per car id. if you want other data frome this line, you have to add it in the "SELECT" query