Codeigniter - Searching for records between two dates 1064 error

I keep getting this error every time I go to the chargeticket_list page.

Controller

public function chargeticket_list() {
$data['charge_tickets'] = $this->Chargeticket_model->list_charges($this->input->post('date1'), $this->input->post('date2'));
$this->load->view( 'inc/header' );
$this->load->view( 'chargeticket/chargeticket_list', $data );
$this->load->view( 'inc/footer' );
}

Open in new window

Model

public function list_charges($date1, $date2) //This function returns an array
    {   $this->db->join( 'customers', 'charges.idcustomers = customers.idcustomers' );
        $this->db->where('ch_date >=', $date1);
        $this->db->where('ch_date <=', $date2);
        $query = $this->db->get('charges'); //create query
        return $query->result_array(); //creates array from query
    }

Open in new window

View

<script type="text/javascript">

$(document).ready(function (){
    $('.date1').datepicker({
dateFormat: 'yy-mm-dd'});
});

$(document).ready(function (){
    $('.date2').datepicker({
dateFormat: 'yy-mm-dd'});
});
</script>

<div class="container">
<?php 
    $attributes = array('id'=>'chargeticket_list', 'class'=> 'form-horizontal');
    echo form_open('chargeticket/chargeticket_list', $attributes);
?>

<div class="form-group">
    <?php $ldata = array('class' => 'control-label col-sm-4');
    echo form_label('From','date1', $ldata ); 
    $data = array('class' => 'form-control date1','name' => 'date1');?>
    <div class="col-sm-4"><?php echo form_input($data);?></div>
</div>

<div class="form-group text-center">
    <?php $ldata = array('class' => 'control-label col-sm-4');
    echo form_label('To','date2', $ldata ); 
    $data = array('class' => 'form-control date2','name' => 'date2');?>
    <div class="col-sm-4"><?php echo form_input($data);?></div>
</div>

<div class="form-group">
    <div class="text-center">
    <?php $data = array('class' => 'btn btn-primary','name' => 'submit','value' => 'submit');
    echo form_submit($data); ?>
    </div>
</div>
<?php echo form_close();?>  
<br>

<h2 align="center"> Charge Tickets </h2>
<br>


<?php if(isset($_POST['submit'])):?>
    <table class="table table-hover table-bordered table-condensed">
    <tr class="table-header">
          <td><b>Date Created</b></td>
          <td><b>Posted</b></td>
          <td><b>First Name</b></td>
          <td><b>Last Name</b></td>
          <td><b>Charge Ticket #</b></td>
          <td><b>Grand Total</b></td>
          <td><b>View</b></td>
          <td><b>Delete</b></td>
    </tr>
    <?php foreach ($charge_tickets as $object){ ?>
    <tr>
        <td><?php echo $object['ch_date'];?></td>
        <td><?php echo $object['posted'];?></td>
        <td><?php echo ucwords(strtolower($object['fname']));?></td>
        <td><?php echo ucwords(strtolower($object['lname']));?></td>
        <td><?php echo ucwords(strtolower($object['ch_ticket_id']));?></td>
        <td></td>
        <td></td>
        <td></td>
     </tr>      
    <?php } ?>  
  </table>
<?php endif;?>
</div>

Open in new window

The error I continue to get is: Error Number: 1064

You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'NULL AND ch_date < IS NULL' at line 4

SELECT * FROM charges JOIN customers ON charges.idcustomers = customers.idcustomers WHERE ch_date > IS NULL AND ch_date < IS NULL

Filename: models/Chargeticket_model.php

Line Number: 76

I guess the question I have is why is it trying to access the database before I use the date text fields in the form.

I have another page that I am copying from that does work. I can't see how what I am doing is any different from this:

Controller

public function customer_search() {
    $data[ 'title' ] = 'Customer Search';
    $data[ 'results' ] = $this->Customers_model->search_customers($this->input->post('search')); //excecutes get_customers function
    $this->load->view('inc/header');
    $this->load->view('customers/customer_search', $data);
    $this->load->view('inc/footer');
}

Open in new window

Model

public function search_customers($search) //This function returns an array
{
    $this->db->select('idcustomers, add_name, fname, lname, city, company, marina_reg');
    $this->db->like('fname', $search);
    $this->db->or_like('lname', $search);
    $this->db->or_like('company', $search);
    $this->db->or_like('add_name', $search);
    $query = $this->db->get('customers'); //create query
    return $query->result_array(); //creates array from query
}

Open in new window

View

<div class="container"><h2 class="text-center"><?php echo $title;?></h2>
<br>

<?php 
    $attributes = array('id'=>'search_form', 'class'=> 'form-horizontal');
    echo validation_errors("<p class='bg-danger'>");
    echo form_open('customers/customer_search', $attributes);
?>
<div class="text-center"><p>Search using part of first name, last name or company</p></div>
<div class="form-group">
    <?php $data = array('class' => 'form-control','name' => 'search');?>
    <div class="col-sm-4"></div>
    <div class="col-sm-4"><?php echo form_input($data);?></div>
</div>

<div class="form-group">
    <div class="text-center">
    <?php $data = array('class' => 'btn btn-primary','name' => 'submit','value' => 'Search Customer');
    echo form_submit($data); ?>
    </div>
</div>

<?php echo form_close();?>

<?php if ($this->input->post('search')):?>
    <table class="table table-hover table-bordered table-condensed">
    <tr class="table-header">
          <td><b>ID</b></td>
          <td><b>Regular</b></td>
          <td><b>Last Name</b></td>
          <td><b>First Name</b></td>
          <td><b>Additional Contact</b></td>
          <td><b>Company</b></td>
          <td><b>City</b></td>
          <td><b>Details</b></td>
    </tr>
    <?php foreach ($results as $object){ ?>
    <tr>
      <td><?php echo $object['idcustomers'];?></td>
      <td>
      <?php 
      if ($object['marina_reg'] == 'Yes'):echo $object['marina_reg'];endif;?>
      </td>
        <td><?php echo ucwords(strtolower($object['lname']));?></td>
        <td><?php echo ucwords(strtolower($object['fname']));?></td>
        <td><?php echo ucwords(strtolower($object['add_name']));?></td>
        <td><?php echo ucwords(strtolower($object['company']));?></td>
        <td><?php echo ucwords(strtolower($object['city']));?></td>
        <td><a href="<?php echo base_url();?>customers/customer_edit/<?php echo $object['idcustomers'];?>">Details</a></td>
     </tr>      
    <?php } ?>  
  </table>
<?php endif;?>
</div>

Open in new window

Thanks in advance for your help.
Robert FrancisDirector of Continuous ImprovementAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Dave BaldwinFixer of ProblemsCommented:
ch_date < IS NULL by itself is an error.  You can not compare (<) NULL data with anything.  You can only determine IS NULL
Chris StanyonWebDevCommented:
In your controller function, you're searching the database when the page is loaded:

public function chargeticket_list() {
    $data['charge_tickets'] = $this->Chargeticket_model->list_charges($this->input->post('date1'), $this->input->post('date2'));
    $this->load->view( 'inc/header' );
    $this->load->view( 'chargeticket/chargeticket_list', $data );
    $this->load->view( 'inc/footer' );
}

Open in new window

That firstline will fire, regardless of whether you've submitted the form or not. Obviously, if you haven't submitted the form the date1 and date2 will be NULL which is why you're getting the error. You'll need to apply some logic to avoid that. Something like this (unntested):

public function chargeticket_list() {
    if ( $this->input->post('date1') && $this->input->post('date2') ) {
        $data['charge_tickets'] = $this->Chargeticket_model->list_charges($this->input->post('date1'), $this->input->post('date2'));
    } else {
        $data['charge_tickets'] = null;
    }

    $this->load->view( 'inc/header' );
    $this->load->view( 'chargeticket/chargeticket_list', $data );
    $this->load->view( 'inc/footer' );
}

Open in new window

Now your controller will only do the search if date1 and date2 have been POSTed.

FYI - The reason your other controller works is because, even though it will still do the search without a POST, it will be searching a String for NULL, which is valid:

SELECT * FROM customers WHERE fName LIKE NULL

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Robert FrancisDirector of Continuous ImprovementAuthor Commented:
Thanks
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
PHP

From novice to tech pro — start learning today.