Booking system with other types of data formating - d.m.Y instead of Y-m-d

I also truggled with queries with a date field in the where clause & I guess you are missing the single quote.

My date format is by the way dd-mm-YYYY

In a BeforeRecordDelete event I check if the record I want to delete has child records:

// Check if specific record exists

$sql = "select * from Presentie where Datum='".$deleted_values['Datum']."' and LesID=".$deleted_values['LesID'];
$rs = DB::Query($sql);
$data=$rs->fetchAssoc();
if($data)
{
$message="Verwijderen niet toegestaan; verwijder eerst de Presenties.";
return false;
}

// Place event code here.
// Use "Add Action" button to add code snippets.**

return true;

In a BeforeRecordAdd event I had to do it in another way; the Datum field I get from the page is -I guess- still a text field. To use this field in a query I had to convert it to a Date field. Now it works OK:

// Place event code here.
// Use "Add Action" button to add code snippets.

// Check if specific record exists

$datum = substr($values["Datum"],0,10);

$sql = "select * from LesDatum where Datum=STR_TO_DATE('".$datum."','%Y-%m-%d') and LesID=".$values['LesID'];
$rs = DB::Query($sql);
$data=$rs->fetchAssoc();
if($data)
{
$message="Toevoegen niet toegestaan; ".$datum." is al aanwezig!";
return false;
}

return true;

Pay attention to the single quotes. Apparently to query a date or a text field you should use single quotes around the value.

Hi Henny,

Thanks for your support.
I use that code inside the Lookup wizard on the WHERE field of the 'room' input. If I try to use double quote, it won't work at all, only works with single quote.

Btw, this is how I work, and I had no problems yet ('".$values["room_id"]."' OR '".$_SESSION["room_id"]."' and so on).

I just wanted to add that dates in the database are always stored in yyyy-mm-dd format and this is how you need to format it in your SQL queries.

Hi admin,

Thanks for your support.
I've just tried with this query, and it won't work either:
id_room NOT IN (SELECT id_room FROM bookings WHERE (bookings .date_from >= DATE_FORMAT(':check_in','%yyyy-%mm-%dd') OR bookings .date_to >= DATE_FORMAT(':check_in','%yyyy-%mm-%dd')))

I don't want to format the field to display the date in this format yyyy-mm-dd because peoples are not used with that format in my country :(

I guess admin meant that dates are stored as Ymd in the database. This has nothing to do with the format that is displayed on the page. I queried using de Ymd format but dates are displayed as dmY.

Henny,

I think I have not made it clear that I want to do this live, on the add edit page and not after a specific event.
If you look in my first example, you'll see I have formatted the data in the query like this Y-m-d, as MySQL needs.

What I want is to work my query based on a field that is formatted to display data in another way. That is what I haven't been able to do.

So, for a real-time situation, if I use ':example_data_field' in PHP Runner>Lookup Wizard>WHERE, I think it will read and compare data as it is formatted and displayed to the user (d.m.Y instead of Y-m-d). I believe this because when I have formatted it from PHP Runner like this Y-m-d, the query worked, however when I have switched it to this d.m.Y, it stopped working.

To make it a bit more clear: I have 3 fields: rooms, check_in, check_out. Based on what the user fill on check_in and check_out, the app should query a reservation table and see which rooms are available, but in real-time, right on the add/edit page. The thing is that I also want to stick with my country specific locale.

I forgot to mention that this is on an older project that was originally developed in PHP Runner 9.8, and I really want to keep it that way (this is timewise only).

It's easy with something like this (and you can format dates in whatever way you want...) - using custom fields:

$data['booking_date'] - is your date field.

Hi Cristi,
Thanks for your support.

The thing is that I want this to work like a real-time validation.
I use that query through the Lookup wizard and not in an event. But maybe I can declare those data formats in the Before process event, and it will look and work as intended. I haven't tried that yet.