Link to home
Start Free TrialLog in
Avatar of Robert Francis
Robert Francis

asked on

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.
Avatar of Dave Baldwin
Dave Baldwin
Flag of United States of America image

ch_date < IS NULL by itself is an error.  You can not compare (<) NULL data with anything.  You can only determine IS NULL
ASKER CERTIFIED SOLUTION
Avatar of Chris Stanyon
Chris Stanyon
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Robert Francis
Robert Francis

ASKER

Thanks