-1

I have a content type called 'Event' in which I have used one date time field for storing the event starting and end time. Now I should have a field validation for a that date time field. For example, For one event, admin has choosed '27/02/14 08:00' as a 'starting time' and '27-02-14 09:00' as a 'end time' respectively. Then if admin try to create a new event with time between previously entered event time, like '27-02-14 08:30' and '27-02-14 09:40', then we need to show a error that ' particular time period is already choosed'. For this feature, I have create a custom validation using my custom module with the following code

function my_module_node_validate($node, $form) {
  if ($node->type == 'event') {
        //starting time
        $time1=$node->field_event_date['und'][0]['value'];
        //End time
    $time2=$node->field_event_date['und'][0]['value2'];
     $sql_query = db_select('field_data_field_event_date','ed');
    $sql_query->fields('ed', array('entity_id'));
        //Condition to check whether start time is greater than or equal to existing database starting time value
        $sql_query->condition('ed.field_event_date_value',$time1,'>=');
         //Condition to check whether start time is less than or equal to existing database end time value
    $sql_query->condition('ed.field_event_date_value2',$time1,'<=');
        $count = $sql_query->countQuery()->execute()->fetchField();
        if($count){
           form_set_error('field_event_date', 'Time is already booked.');
    }
  }
}

The problem is, condition are not working with database values. It seems that event date field is storing its value in mysql in datetime format but the above query is not doing proper validation. Please tell me, is there any problem with above query or do we have any other methods to do this validation.Thanks in advance.

0

When comparing datetimes with each other I always find it easier to convert to unix timestamps first. Example would be:

function my_module_node_validate($node, $form) {
    if ($node->type == 'event') {
        //starting timestamp
        $time1 = strtotime($node->field_event_date['und'][0]['value']);
        //End timestamp
        $time2 = strtotime($node->field_event_date['und'][0]['value2']);
        $sql_query = db_select('field_data_field_event_date','ed');
        $sql_query->fields('ed', array('entity_id'));
        //Condition to check whether start time is greater than or equal to existing database starting time value
        $sql_query->where('UNIX_TIMESTAMP(ed.field_event_date_value) >= :time', array(':time' => $time1));
        //Condition to check whether start time is less than or equal to existing database end time value
        $sql_query->where('UNIX_TIMESTAMP(ed.field_event_date_value2) <= :time', array(':time' => $time1));
        $count = $sql_query->countQuery()->execute()->fetchField();
        if($count){
            form_set_error('field_event_date', 'Time is already booked.');
        }
    }
}
  • Thanks for your answer, I have followed your code but no success. Still it is allowing me to save events between existing event time interval – Moorthy Feb 24 '14 at 12:37
  • You will need to debug exactly what is not working. Install devel and use the dmp function to inspect the $count variable, and maybe run the SQL directly against the db to see what is being returned and why it is not working. – Felix Eve Feb 25 '14 at 9:03
0

Drupal store datetime in UNIX_TIMESTAMP on database. So before using it on your own code convert datetime value in your site or user timezone's timestamp.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.