如果用户只填写一个字段，则MySQL查询不起作用

Question

I'm working in CodeIgniter with a search from that will query the database.

The three fields are "Location" (dropdown list), "Date" (date) and "Event Type" (dropdown list).

The code I'm currently using is:

public function search_flyers($location, $date) {
    $date = $this->utils_model->get_sql_date($date);
    $query = $this->db->query("SELECT *, flyers.fid FROM flyers 
                LEFT JOIN event_dates ON flyers.fid = event_dates.fid 
                WHERE ((flyers.interval='weekly' 
                    AND DATEDIFF('" . $date . "',flyers.start_date)%7=0 
                    AND '" . $date . "' <= flyers.end_date)
                  OR (flyers.interval='fornightly' 
                    AND DATEDIFF('" . $date . "',flyers.start_date)%14=0 
                    AND '" . $date . "' <= flyers.end_date)
                  OR (flyers.interval='manual' 
                    AND event_dates.date = '" . $date . "' 
                    OR flyers.start_date = '".$date."' 
                    AND '".$date."' <= flyers.end_date) AND flyers.approved = 1) 
                AND flyers.location = '".$location."' 
                GROUP BY flyers.fid 
                ORDER BY flyers.start_date ASC");

    $data['flyers'] = $query->result();
    $data['rows'] = $query->num_rows();

    return $data;

The Event Date mentioned is being used in conjunction with the "interval" field to work out if an event falls on the date the user has searched for.

The event also has to be approved to be able to show in search results.

The problem is the form does not work if a user only fills in one field.

A previous developer has written this code, and I am charged with the task of making it work.

Normally I would change the AND flyers.location... to OR flyers.location, but that means it would return the results of either the date or the location you specified.

Answer 1

There are several ways to fix this problem, but the quickest is to simply set a default value in the method signature itself.

Instead of

public function search_flyers($location, $date) 

you can actually add default values by setting them like this (just an example)

public function search_flyers($location = 'mylocationstring', $date = strtotime("-1 week"))

If the user doesn't choose a date or a location, $date and $location will default to the values you specified.

Now the cleaner way would be to actually verify that the user has filled out the form correctly in the view before letting them submit it. You can do this with CodeIgniter's form validation http://codeigniter.com/user_guide/libraries/form_validation.html by not letting the user run search_flyers($date,$location) if the form validation run is equal to false

if ($this->form_validation->run() == FALSE) 
{
   //go back to your initial view
}
else
     {
          $data['results'] = search_flyers($location,$date)     
                  $this->load->view('form success',$data);
    }