[SiyaStoan]

Hi All

 

I'm writing a small car booking app, I wish to execute an SQL query that will return a list of available cars that can be booked, and populate the DropDownList(Cars Id) with only those cars and not the one already booked. My form looks like this:

 

 

 

Thanx

[oVERDRIVE]

What's the problem ?

Do U wanto to have the name of the car, instead id ? (set_relation() )

Can U show me the table model, that U use ?

[SiyaStoan]

What's the problem ?

Do U wanto to have the name of the car, instead id ? (set_relation() )

Can U show me the table model, that U use ?

 

If I set_relation, It returns all the cars in the cars table.

I have a Bookings table and a Cars table. with a one to many relationship. The tables look like this:

 

CREATE TABLE `cars` (
  `id` INT (11) NOT NULL AUTO_INCREMENT,
  `description` VARCHAR (100) NOT NULL,
  `registration` VARCHAR (7) NOT NULL,
  `initial_km` INT (11) DEFAULT '0',
  PRIMARY KEY (`id`),
  UNIQUE KEY `registration_UNIQUE` (`registration`)
) ENGINE = INNODB AUTO_INCREMENT = 6 DEFAULT CHARSET = latin1 $$ 


CREATE TABLE `bookings` (
  `id` INT (11) NOT NULL AUTO_INCREMENT,
  `reason` VARCHAR (100) NOT NULL,
  `from_date` DATE NOT NULL,
  `to_date` DATE NOT NULL,
  `booking_date` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `km` INT (11) DEFAULT NULL,
  `is_cancelled` TINYINT (1) DEFAULT '0',
  `Cars_id` INT (11) NOT NULL,
  `users_id` MEDIUMINT (8) UNSIGNED NOT NULL,
  PRIMARY KEY (`id`),
  KEY `fk_Bookings_Cars` (`Cars_id`),
  KEY `fk_Bookings_users1` (`users_id`),
  CONSTRAINT `fk_Bookings_Cars` FOREIGN KEY (`Cars_id`) REFERENCES `cars` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `fk_Bookings_users1` FOREIGN KEY (`users_id`) REFERENCES `users` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE = INNODB AUTO_INCREMENT = 60 DEFAULT CHARSET = latin1 $$ 

[oVERDRIVE]

If U want to show only the available cars ? not the car in rent ?

[oVERDRIVE]

Create in a table booking, an entry : 'use" TINYINT (1) DEFAULT '0',

In controller set

 

 

$crud->where('use', 0);

 

in this way, you have a list, of only not rent cars ...

 

Of course, after update, U must set a call back function (callback_after_update) to change the 'use' to 0

 

Hoping to understand .... ;)

[SiyaStoan]

Create in a table booking, an entry : 'use" TINYINT (1) DEFAULT '0',

In controller set

 

 

$crud->where('use', 0);

 

in this way, you have a list, of only not rent cars ...

 

Of course, after update, U must set a call back function (callback_after_update) to change the 'use' to 0

 

Hoping to understand .... ;)

 

 

Hey oVERDRIVE, Thanx for the quick response :) . I believe your solution would work if a car wasn't book for a pre-defined period of time.

 

For an example. A use can book a car from 13-02-2013 to 17-02-2013.

 

So what I'm looking to do is after the user enters the from and the to date for the booking. execute a SQL query to return a list of cars not booked between those days and populate cars dropdownlist on the form. I hope you get me.

[goFrendiAsgard]

Hi, doing AJAX seems to be the only available option in this case.
 

$('#field-from_date, #field-to_data').change(function(){
   // send ajax request to server
   // modify the dom of #field-cars_id
});

 

Google chrome developer tools and firebug is your friend to know the DOM id of those fields.
Please let us know if you have problem doing this :)

[SiyaStoan]

Hi, doing AJAX seems to be the only available option in this case.
 

$('#field-from_date, #field-to_data').change(function(){
   // send ajax request to server
   // modify the dom of #field-cars_id
});

 

Google chrome developer tools and firebug is your friend to know the DOM id of those fields.
Please let us know if you have problem doing this :)

 

Hi goFrendiAsgard

 

I have never worked with AJAX, May you please point me to some examples if you can. Thanx

[goFrendiAsgard]

That's gonna take a long time to explain.
Basically AJAX is about fetch data from server and doing some DOM modification.
I'm not sure I can explain it in a post.
This is gonna be a good reference to read http://www.w3schools.com/jquery/jquery_ajax_intro.asp

Basically it is like this:

You have a controller with 2 function. The first function is to show the groceryCRUD, and another function is to show the car which is in the range of date

<?php
class Car_Controller extends CI_Controller{
   public function car(){
      $this->load->library('groceryCRUD');
      $crud = new GroceryCRUD();
      // and any other code
      $output = $crud->render();
      $this->load->view('car_view',$output);
   }

   public function get_allowed_car_id(){
      $this->load->model('car_model');
      $start_date = $this->input->post('start_date');
      $end_date = $this->input->post('end_date');
      $data = $this->car_model->get_car_id_between_date($start_date, $end_date);
      echo json_encode($data);
   }
}
?>

 

Suppose you have a form that post start_date and end_date and point to car_controller/get_allowed_car_id

you'll have something like this as output:

[{id:1},{id:3},{id:5}]

 

Assuming there are car with id 1,3 and 5 available between the date. This kind of format is named JSON (Javascript Object Notation).

 

Now you want to get such an information everytime your visitor change field-from_date or field-to_date input.

Therefore you'll need a javascript (or easier using JQuery framework) in your car_view.php

 

<script type="text/javascript">
$(document).ready(function(){
   // everytime your input changes, send the ajax request
   $('#field-from_date, #field-to_date').change(function(){
       // get the value of field-from_date and field-to_date
       var from_date = $('#field-from_date').val();
       var to_date = $('#field-to_date').val();
       // send ajax request, as someone submit something by using post method to your controller
       $.ajax({
          'url' : 'car_controller/get_allowed_car_id',
          'type': 'POST',
          'dataType': 'json',
          'data': {'from_date':from_date,'to_date':to_date},
          'success': function(response){
             // okay, here you need to write a script to change the DOM, but for now I'll just show it in javascript console
             console.log(response);
          }
       });
   });
});
</script>

 

Sorry, this might not explain too much :P
You still need to learn AJAX by yourself. But I hope I can give you a "big-picture-idea" about what to do.

Tell me again if you have any problem. Good luck :)

 

EDIT:

Actually I use such a method in No-CMS module generator. So If you download No-CMS, install it in your computer, and run module generator, you'll find that everytime you change the value of "selection table", the item of the "selection field" will also be affected. But I've mix it up with other function, so it is not so easy to read. In case of you are interested, you can inspect the source code https://github.com/goFrendiAsgard/No-CMS/blob/master/modules/nordrassil/views/data/nds_column.php

[Julio]

Hi @SiyaStoan,

Did you get to make it work?
Please could you give me more details?

Thanks.

[Julio]

Hi to all,

 

Please could someone help me with this issue?

I need to filter the dropdown list using both start and end dates, but I don't how to get the dates from the form.

 

I've tried the code above, but dates variables are blank.

 

My controller:

$locais = $this->main_model->carregar_locais();
        if (isset($locais)){
            foreach ($locais->result() as $local){
                $array_locais[$local->CODLOC] = $local->DESLOC;
            }            
        }
$crud->field_type('CODLOC','dropdown', $array_locais);

public function carregar_locais(){
        $data_inicial = $this->input->post('DATINI');
        $data_final = $this->input->post('DATFIM');
        $data = $this->main_model->carregar_locais_por_data($data_inicial, $data_final);
        echo json_encode($data);
   }

My model:

    function carregar_locais_por_data($data_inicial =null, $data_final =null){
        if( (isset($data_inicial)) && (isset($data_final)) ){
            return $this->db->query('select CODLOC, DESLOC
                                        from e010loc
                                        where not exists (select 1
                                                    from e050ege
                                                    where e050ege.codloc = e010loc.codloc
                                                    and e050ege.datesc >= '.$data_inicial.'
                                                    and e050ege.datesc <='.$data_final.')');            
        }

My view:

        $(document).ready(function(){
           // everytime your input changes, send the ajax request
           $('#field-DATINI, #field-DATFIM').change(function(){
               var data_inicial = $('#field-DATINI').val();
               var data_final = $('#field-DATFIM').val();
               $.ajax({
                  'url' : 'main/carregar_locais_por_data',
                  'type': 'POST',
                  'dataType': 'json',
                  'data': {'DATINI':data_inicial,'DATFIM':data_final},
                  'success': function(response){
                        
                        //i dont know what i should put here...

                  }
               });
           });
        });
        </script>

Thanks in advance.

[mckaygerhard]

easy to say "oh made it as .." or "ajax its easy, look", but look what? no examples, nocode results, no solution... and of course alwas the complicated stupid ajax.. 

 

never provided a custom easy solution.. those guys think that we are great programmer and we only are a working people triying to made by itselft a solution..

 

after many tries, finnally made by own the edit form and override the add/edit action.. 

 

1) deactivate the edit action

2) made a custom form

3) fill the variables and send to view

4) made a custom method to receive data and insert into db

 

that's seem more "large way" solution but at leas its a solution that a non-programer already know.. 

[mckaygerhard]

i now explain how: no stupid complicated AJAX call's only php and grocerycrud:

 

1) controller made normal, here tables:

CREATE TABLE tablemain ( colkeymain TEXT, collink_noname TEXT, PRIMARY KEY (colkeymain)  );
CREATE TABLE tablerefer ( colkeyrefer TEXT, collink_naming TEXT, PRIMARY KEY (colkeyrefer)  );

2) here controller

$arrayrowsfromtablerefer = $this->modeltablerefer->get_allrows_as_array();
....
// code for show in list action
$crud->callback_column('collink_noname', array($this,'_linkrelation'));
// code for show in add and edit, maybe read
$crud->field_type('collink_noname', 'dropdown',$arrayrowsfromtablerefer);...
...
function '_linkrelation' ( $vale, $rowf ) { return $arrayrowsfromtablerefer[$vale]; }

3) here idea of model logic:

public function get_allrows_as_array()
{
  query = "select * from ..."
...
  foreach( $results as $rows)
     $arrayoflisttolink[$row->collink_naming] = $row->collink_naming . $row->tablerefer;
return $arrayoflisttolink
}

EXPLANATION:

 

theres two situations, when are in a form, and when are in a list.. 

 

for the list, we made a callback, i mean, on every row, we escaped taking the value, and searching inside an array of keycodes comming from the refer table, this due we cannot made a query to db on each row render

 

for the form (add or edit), more simple, only we associate the already array of rows comming from the refer table as a dropdown list and that's all

 

more detail and complete code in the blog of venneux and massenkoh: 

http://qgqlochekone.blogspot.com/2017/04/codeigniter-grocerycrud-asociation-1-1.html