'How to make search base on pickup and return date?

I get stuck for several weeks, the problem is I want to make it easy for users to search the car name that is available on pickup and return dates. This query is not correct and I have no idea to correct it.

I have two tables:

Cars table:
carId (prim-key)
carType,
carName
carPrice

rent table:
rentId (prim-key)
rentStart date:
rentEnd date:
rentStatus,
rentCarId (foreign key) to carId in cars table.

So far I have been able to achieve search the car name.

here is my code

//model query builder
function search($car = null , $start, $end) {
      $start= $this->db->escape($start);
      $end= $this->db->escape($end);
      $this->db->select('  carId as id,
                           rentStatus as status,
                           rentStart as start,
                           rentEnd as end,
                           carName as name,
                           carType as type,
                           carPrice as price,
                           carImage as image
                        ');

      $this->db->from('cars');
      $this->db->group_by('carName');
      $this->db->join('rent', 'carId = rentCarId', 'left');
      $this->db->like('carName', $car);
      $this->db->where('rentEnd' > $start AND 'rentStart' < $end);
      $this->db->where('rentCarId', NULL);
      
      return $this->db->get()->result_array();
   }

//controller
public function search() {
   $start= $this->input->post('start');
   $end= $this->input->post('end');
   $car= $this->input->post('car');
   $id = $this->input->post('car-id');

   $data['cars'] = $this->M_cari->search($car, $start, $end);
    
   $this->template->title('Home');
   $this->template->build($this->module.'/v_search_index', $data);
}
phpmysqlcodeigniter


Solution 1:[1]

You use the GROUP operator and for it you need to use aggregate functions for the remaining fields in the select selection. Therefore, remove this line and why do you select only those fields for which WHERE rentCarId = NULL. If by this condition you mean that your machine is not busy, then I advise you to reconsider the logic of your tables

Your query

    SELECT carId as id,
        rentStatus as status,
        rentStart as start,
        rentEnd as end,
        carName as name,
        carType as type,
        carPrice as price,
        carImage as image FROM cars
**GROUP BY carName**
JOIN rent ON carId = rentCarId
like carName $car
WHERE rentEnd > $start AND rentStart < $end
WHERE rentCarId = NULL

Your request is wrong. you first join the two tables on the keys carId = rentCarId and then ask for the key rentCarId to be null .

The best solution would be to write a subquery to search for rented cars and remove them from the result of all cars

SELECT carId as id,
        rentStatus as status,
        rentStart as start,
        rentEnd as end,
        carName as name,
        carType as type,
        carPrice as price,
        carImage as image FROM cars
LIKE carName $car
WHERE carId NOT IN (SELECT rentCarId FROM rent)

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 boris4682

Related Questions