Codeigniter - Searching for records between two dates 1064 error

Robert Francis
Robert Francis used Ask the Experts™
on
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.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Dave BaldwinFixer of Problems
Most Valuable Expert 2014

Commented:
ch_date < IS NULL by itself is an error.  You can not compare (<) NULL data with anything.  You can only determine IS NULL
Most Valuable Expert 2018
Distinguished Expert 2018
Commented:
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
Robert FrancisDirector of Continuous Improvement

Author

Commented:
Thanks

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial