How to make a DateTime field from Date and Time user entry
How to make a DateTime field from Date and Time user entry
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.
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.
Re: How to make a DateTime field from Date and Time user ent
Any help someone might be able to lend on this one will be most appreciated. Thank you.
Re: How to make a DateTime field from Date and Time user ent
Hi Kerem,
Try this:
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)
Try this:
Code: Select all
$data['date_time'] = date("YmdHis", strtotime("{$data['date']} {$data['time']}"));
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.
Re: How to make a DateTime field from Date and Time user ent
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.
Re: How to make a DateTime field from Date and Time user ent
What are the data types of the 3 fields? Could you post a sample of the content of each please?
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.
Re: How to make a DateTime field from Date and Time user ent
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.
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.
Re: How to make a DateTime field from Date and Time user ent
Umm .. that looks all fine. Well, try this code then:
EDIT:
You might as well try the more straightforward:
Code: Select all
$data['date_time'] = date("Y-m-d H:i:s", strtotime("{$data['date']} {$data['time']}"));
You might as well try the more straightforward:
Code: Select all
$data['date_time'] = "{$data['date']} {$data['time']}";
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.
Re: How to make a DateTime field from Date and Time user ent
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!!!
Re: How to make a DateTime field from Date and Time user ent
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:
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
ob_start();
print_r($data);
$out=ob_get_contents();
ob_end_clean();
file_put_contents('./data.log', $out);
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);
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.
Re: How to make a DateTime field from Date and Time user ent
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
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
Re: How to make a DateTime field from Date and Time user ent
No trouble caused .. I'm just puzzled that nothing is working ... Try this code in the after_update:
If that doesn't work still, could you paste the same debugging code into the after_update to see what it's receiving?
Code: Select all
sql("update `performance` set `date_time`='{$data['date']} {$data['time']}' where `performance_id`='{$data['selectedID']}'", $eo);
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.
Re: How to make a DateTime field from Date and Time user ent
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] =>
)
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] =>
)
Re: How to make a DateTime field from Date and Time user ent
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:
and change it to:
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);
Code: Select all
$res = sql("select * from `performance` where `id`='" . makeSafe($data['selectedID']) . "' limit 1", $eo);
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.
Re: How to make a DateTime field from Date and Time user ent
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
Instance 2
Instance 3 - This one is inside function performance_form( .... )
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);
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);
Code: Select all
$res = sql("select * from `performance` where `performance_id`='".makeSafe($selected_id)."'", $eo);
Re: How to make a DateTime field from Date and Time user ent
It's instance 2, the after_update one.
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.
Re: How to make a DateTime field from Date and Time user ent
Hi Ahmad, thanks for persevering with this. Finally we have success!!!
Now I can run
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.
Now I can run
Code: Select all
sql("update `performance` set `date_time`='{$data['date']} {$data['time']}' where `performance_id`='{$data['selectedID']}'", $eo);
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.
Re: How to make a DateTime field from Date and Time user ent
You're always welcome, Kerem
But ... are you sure that exact line is working without errors? Because it has invalid concatenation and an extra double-quote ... it should be:
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.
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);
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.
Re: How to make a DateTime field from Date and Time user ent
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.
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!!!
Code: Select all
sql("update `performance` set `date_time`='{$data['date']} {$data['time']}' where `performance_id`='{$data['selectedID']}'", $eo);