How to make a DateTime field from Date and Time user entry

If you're a new user of AppGini, feel free to ask general usage questions, or look for answers here.
Post Reply
KSan
AppGini Super Hero
AppGini Super Hero
Posts: 252
Joined: 2013-01-08 20:17

How to make a DateTime field from Date and Time user entry

Post by KSan » 2013-04-20 18:04

Hi, I'm struggling with what must be a very simple task for some. I hope someone here has tackled something similar and can give me some help.

I have a table with separate Date and Time fields to capture when a certain event is scheduled to happen. I like to use separate fields so I can offer the Date Picker as it makes data entry much easier. Problem with separate Date and Time fields is that I can't sort the data based on 2 fields using AppGini default sort method. To help with the sorting issue, I added a hidden DateTime field to the same table.

My idea is to keep the DateTime field updated every time the Date and/or Time fields have been changed. I'm planning to use the following 2 hooks :

tablename_before_insert()
tablename_before_update()

With some code that looks like this :

function tablename_before_insert(&$data, $memberInfo, &$args){

$data['date_time'] = strtotime(date($data['date']) .. " " .. date($data['time']));

return TRUE;
}

So with my php newbie logic what I'm trying to do is...

Use the date() function to get a string out of $data['date'] and $data['time'].
Concat these strings with a " " in between them.
Put the resulting string through strtotime() to convert it back to DateTime format.
Finally set $data['date_time'] to this new value.

I'm failing miserably!!! Anyone know what I'm doing wrong?

Secondly, is my idea to use the following two hooks ok?

tablename_before_insert()
tablename_before_update()

Would you rather use the after_insert and before_update hooks?

Thank you very much for all your help. Most appreciated in advance.

KSan
AppGini Super Hero
AppGini Super Hero
Posts: 252
Joined: 2013-01-08 20:17

Re: How to make a DateTime field from Date and Time user ent

Post by KSan » 2013-04-29 18:44

Any help someone might be able to lend on this one will be most appreciated. Thank you.

User avatar
a.gneady
Site Admin
Posts: 1281
Joined: 2012-09-27 14:46
Contact:

Re: How to make a DateTime field from Date and Time user ent

Post by a.gneady » 2013-04-29 23:09

Hi Kerem,

Try this:

Code: Select all

$data['date_time'] = date("YmdHis", strtotime("{$data['date']} {$data['time']}"));
using the before_insert and before_update hooks is fine ... just make sure that 'date_time' field is not set as read-only in AppGini (setting its value using the above code would overwrite any user-entered value anyway)
:idea: AppGini plugins to add more power to your apps:
  • DataTalk is an innovative AppGini plugin based on ChatGPT that allows you to interact with your AppGini database using natural language questions, without writing any SQL. Check the demo video
  • Mass Update plugin: Update multiple records at once and improve your workflow efficiency.
  • Check our other plugins and get a generous discount of up to 30% when buying 2 or more plugins.

KSan
AppGini Super Hero
AppGini Super Hero
Posts: 252
Joined: 2013-01-08 20:17

Re: How to make a DateTime field from Date and Time user ent

Post by KSan » 2013-04-30 03:47

Hi Ahmad, I'm sorry but this isn't working. Is there anything else I can try? Thank you so very much for your help.

User avatar
a.gneady
Site Admin
Posts: 1281
Joined: 2012-09-27 14:46
Contact:

Re: How to make a DateTime field from Date and Time user ent

Post by a.gneady » 2013-04-30 06:05

What are the data types of the 3 fields? Could you post a sample of the content of each please?
:idea: AppGini plugins to add more power to your apps:
  • DataTalk is an innovative AppGini plugin based on ChatGPT that allows you to interact with your AppGini database using natural language questions, without writing any SQL. Check the demo video
  • Mass Update plugin: Update multiple records at once and improve your workflow efficiency.
  • Check our other plugins and get a generous discount of up to 30% when buying 2 or more plugins.

KSan
AppGini Super Hero
AppGini Super Hero
Posts: 252
Joined: 2013-01-08 20:17

Re: How to make a DateTime field from Date and Time user ent

Post by KSan » 2013-04-30 06:17

Hi Ahmad, please see below. These fields are in the Performance table in the project I sent you the other day to troubleshoot the beta issue. Date field and Time field are visible to the user. Behind the scenes I want to maintain a combined Date_Time field to help with sorting etc.

Field name Type
date date
time time
date_time datetime

Date field contains the following data (using the date picker) ... 2012-06-24

Time field contains the following data ... 11:00:00

Thanks once again for your help. Most appreciated.

User avatar
a.gneady
Site Admin
Posts: 1281
Joined: 2012-09-27 14:46
Contact:

Re: How to make a DateTime field from Date and Time user ent

Post by a.gneady » 2013-04-30 21:24

Umm .. that looks all fine. Well, try this code then:

Code: Select all

$data['date_time'] = date("Y-m-d H:i:s", strtotime("{$data['date']} {$data['time']}"));
EDIT:
You might as well try the more straightforward:

Code: Select all

$data['date_time'] = "{$data['date']} {$data['time']}";
:idea: AppGini plugins to add more power to your apps:
  • DataTalk is an innovative AppGini plugin based on ChatGPT that allows you to interact with your AppGini database using natural language questions, without writing any SQL. Check the demo video
  • Mass Update plugin: Update multiple records at once and improve your workflow efficiency.
  • Check our other plugins and get a generous discount of up to 30% when buying 2 or more plugins.

KSan
AppGini Super Hero
AppGini Super Hero
Posts: 252
Joined: 2013-01-08 20:17

Re: How to make a DateTime field from Date and Time user ent

Post by KSan » 2013-04-30 21:41

Ahmad, thanks so much for your continued effort to help me. This is turning into quite the mystery. I tried both options and neither worked. I'm putting the statements you suggest into before_update and before_insert and nothing happens. I tried even the after_update and after_insert functions and still nothing. I double checked my hook file name (to avoid a d'oh!) and it was correct. I have no idea why this is not working. It is driving me quite mad! Help me keep my sanity please. :) Thank you so much!!!

User avatar
a.gneady
Site Admin
Posts: 1281
Joined: 2012-09-27 14:46
Contact:

Re: How to make a DateTime field from Date and Time user ent

Post by a.gneady » 2013-05-02 16:38

Is any of the fields set as read-only? If so, try setting them all as editable. Let's try some debugging to see what exactly arrives to the hook ... just add this code to the hook function:

Code: Select all

ob_start();
print_r($data);
$out=ob_get_contents();
ob_end_clean();
file_put_contents('./data.log', $out);
Then check the contents of the "data.log" file after making an update/insert to see what gets sent to the hook function.

If you want to use this calculation in the "after" rather than "before" hooks, then you have to run an update query:

Code: Select all

sql("update `tablename` set `date_time`='{$data['date_time']}' where `id`='{$data['selectedID']}'", $eo);
:idea: AppGini plugins to add more power to your apps:
  • DataTalk is an innovative AppGini plugin based on ChatGPT that allows you to interact with your AppGini database using natural language questions, without writing any SQL. Check the demo video
  • Mass Update plugin: Update multiple records at once and improve your workflow efficiency.
  • Check our other plugins and get a generous discount of up to 30% when buying 2 or more plugins.

KSan
AppGini Super Hero
AppGini Super Hero
Posts: 252
Joined: 2013-01-08 20:17

Re: How to make a DateTime field from Date and Time user ent

Post by KSan » 2013-05-03 00:50

Hi Ahmad,

Thanks for all your help. I checked the field in phpMyAdmin and it is not read-only. I manually entered a date & time into it and it looks like this : 2013-05-02 08:17:24.

Thanks for the debug idea. Here's the output from before update hook :
Array
(
[stage] => 1
[date_time] =>
[date] => 2012-6-23
[time] => 11:00:00
[performer] => 515 Players
[details] =>
[phone] =>
[email] =>
[link] =>
[selectedID] => 1
[photo] =>
)

And the following is from before insert hook :
Array
(
[stage] => 1
[date_time] =>
[date] => 2012-6-23
[time] => 11:00:00
[performer] => Test one more time
[details] =>
[phone] =>
[email] =>
[link] =>
[photo] =>
)

I notice that the month is a single digit but at the db level it is recorded as 2 digits. Checked in phpMyAdmin.

I then tried the SQL update option. I don't mind that at all actually... if it worked... The following is how it looked like after changing the table name etc :

sql("update `performance` set `date_time`='{$data['date_time']}' where `performance_id`='{$data['selectedID']}'", $eo);

This did not work. I thought I should try the following :

sql("update `performance` set `date_time`= " .. "{$data['date']}" .. " " .. "{$data['time']}" .. """, $eo);

I think you will get my idea. I messed the SQL formation though I guess as this one locks up the script exection. Oh well...

So here we are. I am sorry for the trouble this is giving you but it is so basic I don't know why I can't get it to work. Thanks for all your help.

Regards,
Kerem

User avatar
a.gneady
Site Admin
Posts: 1281
Joined: 2012-09-27 14:46
Contact:

Re: How to make a DateTime field from Date and Time user ent

Post by a.gneady » 2013-05-03 17:38

No trouble caused .. I'm just puzzled that nothing is working ... Try this code in the after_update:

Code: Select all

sql("update `performance` set `date_time`='{$data['date']} {$data['time']}' where `performance_id`='{$data['selectedID']}'", $eo);
If that doesn't work still, could you paste the same debugging code into the after_update to see what it's receiving?
:idea: AppGini plugins to add more power to your apps:
  • DataTalk is an innovative AppGini plugin based on ChatGPT that allows you to interact with your AppGini database using natural language questions, without writing any SQL. Check the demo video
  • Mass Update plugin: Update multiple records at once and improve your workflow efficiency.
  • Check our other plugins and get a generous discount of up to 30% when buying 2 or more plugins.

KSan
AppGini Super Hero
AppGini Super Hero
Posts: 252
Joined: 2013-01-08 20:17

Re: How to make a DateTime field from Date and Time user ent

Post by KSan » 2013-05-03 18:47

Hi Ahmad, this did not work either I'm afraid. I'm wondering if my use of date and time as field names might be confusing the system. Are these reserved words in MySQL or PHP or AppGini?

Here's the debug data from the update. Thanks much for your effort to get to the bottom of this.

Array
(
[stage] => 1
[date_time] =>
[date] => 2012-6-23
[time] => 11:00:00
[performer] => 515 Players
[details] =>
[phone] =>
[email] =>
[link] =>
[selectedID] =>
[photo] =>
)

User avatar
a.gneady
Site Admin
Posts: 1281
Joined: 2012-09-27 14:46
Contact:

Re: How to make a DateTime field from Date and Time user ent

Post by a.gneady » 2013-05-05 10:30

Using date and time as field names is OK as all field names are automatically quoted in SQL queries .. so date becomes `date` .. etc.

But I see an issue with the debug array ... selectedID should have a value rather than being empty ... This is an issue in AppGini 5.0 that was corrected in the beta. If you aren't using the beta, open the generated perormance_dml.php file and find the line that reads:

Code: Select all

$res = sql("select * from `performance` where `id`='" . makeSafe($data['id']) . "' limit 1", $eo);
and change it to:

Code: Select all

$res = sql("select * from `performance` where `id`='" . makeSafe($data['selectedID']) . "' limit 1", $eo);
:idea: AppGini plugins to add more power to your apps:
  • DataTalk is an innovative AppGini plugin based on ChatGPT that allows you to interact with your AppGini database using natural language questions, without writing any SQL. Check the demo video
  • Mass Update plugin: Update multiple records at once and improve your workflow efficiency.
  • Check our other plugins and get a generous discount of up to 30% when buying 2 or more plugins.

KSan
AppGini Super Hero
AppGini Super Hero
Posts: 252
Joined: 2013-01-08 20:17

Re: How to make a DateTime field from Date and Time user ent

Post by KSan » 2013-05-05 19:16

Hi Ahmad,

Thanks for your help. I think we might be close to nailing this one. Yes, I'm running on 5.0 as I'm close to going live and can't afford to test 5.10 on this system. So I will keep it 5.0 until 5.10 is released and is stable.

I did look for that line in performance_dml.php but could not find an exact match. I have the following 3 instances that are similar though. Kindly let me know which ones (or all) I should change. Thanks much!!!

Instance 1

Code: Select all

// hook: performance_after_insert
	if(function_exists('performance_after_insert')){
		$res = sql("select * from `performance` where `performance_id`='" . makeSafe($recID) . "' limit 1", $eo);
Instance 2

Code: Select all

// hook: performance_after_update
	if(function_exists('performance_after_update')){
		$res = sql("select * from `performance` where `performance_id`='" . makeSafe($data['performance_id']) . "' limit 1", $eo);
Instance 3 - This one is inside function performance_form( .... )

Code: Select all

$res = sql("select * from `performance` where `performance_id`='".makeSafe($selected_id)."'", $eo);

User avatar
a.gneady
Site Admin
Posts: 1281
Joined: 2012-09-27 14:46
Contact:

Re: How to make a DateTime field from Date and Time user ent

Post by a.gneady » 2013-05-06 07:08

It's instance 2, the after_update one.
:idea: AppGini plugins to add more power to your apps:
  • DataTalk is an innovative AppGini plugin based on ChatGPT that allows you to interact with your AppGini database using natural language questions, without writing any SQL. Check the demo video
  • Mass Update plugin: Update multiple records at once and improve your workflow efficiency.
  • Check our other plugins and get a generous discount of up to 30% when buying 2 or more plugins.

KSan
AppGini Super Hero
AppGini Super Hero
Posts: 252
Joined: 2013-01-08 20:17

Re: How to make a DateTime field from Date and Time user ent

Post by KSan » 2013-05-06 18:11

Hi Ahmad, thanks for persevering with this. Finally we have success!!!

Now I can run

Code: Select all

sql("update `performance` set `date_time`='{$data['date']} {$data['time']}' where `performance_id`='{$data['selectedID']}'", $eo);
in after_update and after_insert and they will both get the job done. I am now able to present my table view sorted by the Date_Time (hidden) field.

Thanks much for your help. I am also glad to hear this little bug in 5.0 is getting squished in 5.1. It was a pesky one and had it not been for your troubleshooting help I would have never figured out it was a bug. I kept going round & round in circles trying to fix my 'broken' hook code...

Have a great day!

edit - sql above corrected in order not to mis-lead anyone who might use some of this.

User avatar
a.gneady
Site Admin
Posts: 1281
Joined: 2012-09-27 14:46
Contact:

Re: How to make a DateTime field from Date and Time user ent

Post by a.gneady » 2013-05-11 20:20

You're always welcome, Kerem :D

But ... are you sure that exact line is working without errors? Because it has invalid concatenation and an extra double-quote ... it should be:

Code: Select all

sql("update `performance` set `date_time`= '{$data['date']} {$data['time']}'", $eo);
Besides, you should use a WHERE clause otherwise this statement would change the value of 'date_time' for all records not just the one being updated.
:idea: AppGini plugins to add more power to your apps:
  • DataTalk is an innovative AppGini plugin based on ChatGPT that allows you to interact with your AppGini database using natural language questions, without writing any SQL. Check the demo video
  • Mass Update plugin: Update multiple records at once and improve your workflow efficiency.
  • Check our other plugins and get a generous discount of up to 30% when buying 2 or more plugins.

KSan
AppGini Super Hero
AppGini Super Hero
Posts: 252
Joined: 2013-01-08 20:17

Re: How to make a DateTime field from Date and Time user ent

Post by KSan » 2013-05-11 23:09

You are absolutely right! I don't know where that line came. Must have copy/pasted something from earlier in this thread. The following is the line thats in my hook and is confirmed to be working. It even has a WHERE in it. :D

Code: Select all

sql("update `performance` set `date_time`='{$data['date']} {$data['time']}' where `performance_id`='{$data['selectedID']}'", $eo);
Thanks once again for all your help. Can't wait for 5.10 to be production ready with all the fixes and new features. All the best!!!

Post Reply