close ad
Databridge V2 with MySQLi support IS Now Available!
open ad
View Menu

Technical Support Forums

Free, outstanding support from WebAssist and your colleagues

rating

Populate Recordset only if current DATE and TIME matches

Thread began 10/30/2009 10:01 am by info305726 | Last modified 11/02/2009 7:32 pm by Jason Byrnes | 2335 views | 17 replies |

info305726

Populate Recordset only if current DATE and TIME matches

I would like to create a task application using WA DataAssist and JQuery to produce the page overlay (lightbox style) dialogue
box which will blank out the rest of the page and focus the user to the dialogue box.


I'm going to use a fixed position div along the bottom of the web site on every page (like facebook). I will then use Ajax which will reload
the content of the div along the bottom of the web site every 10 seconds which is pulling its content from a seperate page
which contains a recordset similar to the following:

(The jQuery code to generate the popup lightbox style dialogue box will only execute if the recordSet is NOT empty)

SELECT * FROM tasksTable
WHERE tasksTable.userId = sessionUserId
AND tasksTable.TaskDateTime = {current date and time} <<Not sure what I would put here to get it to filter by date and time.
AND tasksTable.taskStatus <> "completed"


Is their anyway for the recordset to only display data from the above query if the current dateTime is equal to the current
server date and time?

So if a user sets a task to remind them at say 10am on Monday 1st September 2010 the recordset will only show the contents
of that recordset (ie the recordset will not be empty) at 10am on Monday 1st September 2010?

I know you can do this with dates but I would like to be specific to the time as well.

The web application will only sit on a local machine anyway so the server time will be identical to the users computers time.

Any advice would be appreciated.

I'm currently using WA Data Assist, Security Assist etc and I can subscribe to your monthly toolkit if I need to to achieve this.

Sign in to reply to this post

Jason ByrnesWebAssist

In MySQL, NOW() will return the date in yyyy-mm-dd h:m:s format:
2009-10-30 10:25:24


you can remove the seconds using the DATE_FORMAT() function:
DATE_FORMAT(NOW(), '%Y-%m-%d %h:%m')



So in your SQL you can use:
tasksTable.TaskDateTime = DATE_FORMAT(NOW(), '%Y-%m-%d %h:%m');

Sign in to reply to this post

info305726

Thanks Jason - I will give this a try.

If I wanted it to format the date in UK (DD MM YY) how would I do this as I think I would just need to switch it on the PHP side rather than the SQL side as I believe it is stored in the database in US Format?

Sign in to reply to this post

Jason ByrnesWebAssist

Do you want to switch it in the SQL or when displayed on the page?

Sign in to reply to this post

anonymous

Hey guys,

Here's a couple of functions I wrote that I use in my core functions include file at the top of my pages... these are not exact (as they are for US date formatting) but could easily be modified for use in any format.

The first one is what I call goodDate - it currently takes database formatted date and returns the US date formatting of "M/D/YY".

php:
// goodDATE FUNCTION

function goodDate($datetochange) {
    $date_var = strtotime($datetochange);
    echo date('n/j/y', $date_var);
 
}



If you need to modify another variable with this, just chance the echo command to "return".

The second function was written so when a user is updating dates in a form, their submission will be changed from "M/D/YY" to the database format. By enabling a function to change the date, you don't have to rely on client or JS validation to actually handle the reformat allowing for easier validation of the input.

php:
// DATABASE DATE CONVERSION (ASSUMES DATE FORMAT IS MM/DD/YY)

function dataDate($datetoconvert) {
    $dateDataDate = explode('/', $datetoconvert);
    if ($dateDataDate[2] >= 40 && $dateDataDate[2] <= 99) {
        $dateDataYear = '19'.$dateDataDate[2]; 
    } else {
        $dateDataYear = '20'.$dateDataDate[2];
    }
    if (strlen($dateDataDate[0]) == 1) {
        $dateDataMonth = '0'.$dateDataDate[0];
    } else {
        $dateDataMonth = $dateDataDate[0];
    }
    if (strlen($dateDataDate[1]) == 1) {
        $dateDataDay = '0'.$dateDataDate[1];
    } else {
        $dateDataDay = $dateDataDate[1];
    }
    $dataDateFinal = $dateDataYear.'-'.$dateDataMonth.'-'.$dateDataDay;
    return $dataDateFinal;
}



To use this... just take your post variable as it gets entered into the database and wrap it with dataDate(variable).

Again, this was set up to handle US dates but as you can see, could be easily modified for any format.

Cheers,

Brian

Sign in to reply to this post

info305726

Thanks Jason and Brian for your input.

I think I would be best formatting the date to UK format within the recordset?

I have this currently:


SELECT * FROM tasks
WHERE session(userId) = tasks.assignedUserId
AND task.alertShown <> 1
AND tasks.TaskDateTime = DATE_FORMAT(NOW(), '%Y-%m-%d %h:%m');


Because I have a div tag on every page that calls the recordset every 10 seconds I need
to somehow set a value of "1" in the tasks table column "alertShown" so the user doesnt
see 10x popup messages with the same task listed within the minute.

The recordset will only contain data if the alertShown is not equal to the value "1"
and hence no popup message will be duplicated.

How would this be done?

Thanks very much for your help so far.

Sign in to reply to this post

anonymous

You would need to add the message within a conditional region, then.

You could have your recordset at the top - just as you describe - then warp your message with conditional PHP code like this:

php:
<?php

if ($row_tasks['alertshow'] < 1) { ?>

Then insert whatever you want to happen here when that condition is met.

<?php ?>



Regards,

Brian

Sign in to reply to this post

info305726

Hi Brian,

So could I put an record update within the conditional PHP code as you suggested to insert a value of "1" into the database column alertShown (which will be null the first time it is queried)?

<?php

if ($row_tasks['alertshow'] < 1) { ?>

code to UPDATE column "tasks.alertShown" to "1" for every record retrieved by the recordset

<?php } ?>

Do I need to use Data Assist to achive this? or is their some custom code that needs to be written to do this? and if so could you point me in the right direction?

Thanks again for all your help.

Lee

Sign in to reply to this post

info305726

Hi Both, quick update to my previous post.

I have this as a recordset query:

SELECT *
FROM tasks
WHERE dueDateTime <= DATE_FORMAT(NOW(), '%Y-%m-%d %h:%m')
AND alertShown <> 1

For some reason, it does not work.

This is the table structure for tasks within MySQL:

`taskId` int(10) unsigned NOT NULL AUTO_INCREMENT,
`title` text,
`text` text,
`currentStatus` text,
`taskType` text,
`createdUsername` text,
`assignedUsername` text,
`dueDateTime` datetime DEFAULT NULL,
`createdDateTime` text,
`priority` text,
`alertShown` int(11) NOT NULL,
PRIMARY KEY (`taskId`)


I'm not sure why I dont get any records if I insert a new record with the dueDateTime as 2009-11-01 18:40:00 and set the alertShown value to 0 and wait for the computers clock to reach 18:40 or even go to 18:41 etc (remember that I have a jQuery div on the page that reloads the content of the page with the recordset on it every 10 seconds, the user does not have to manualy refresh the page, even if they do no record is displayed).

Sign in to reply to this post

Jason ByrnesWebAssist

to trouble shoot what is wrong, the first thing we will need to know id the actual SQL that is being run by the script.

Just after the body tag of you page, add the following:
<?php echo($query_recordsetName); ?>

where recordsetName is the name of your recordset.

once you know the SQL being run by the script, try taking that and running it against the database directly. I use the MySQL GUI Tools Query Browser, you may have something else.


If you having problems, post the SQL here so I can see it.

Sign in to reply to this post
loading

Build websites with a little help from your friends

Your friends over here at WebAssist! These Dreamweaver extensions will assist you in building unlimited, custom websites.

Build websites from already-built web applications

These out-of-the-box solutions provide you proven, tested applications that can be up and running now.  Build a store, a gallery, or a web-based email solution.

Want your website pre-built and hosted?

Close Windowclose

Rate your experience or provide feedback on this page

Account or customer service questions?
Please user our contact form.

Need technical support?
Please visit support to ask a question

Content

rating

Layout

rating

Ease of use

rating

security code refresh image

We do not respond to comments submitted from this page directly, but we do read and analyze any feedback and will use it to help make your experience better in the future.

Close Windowclose

We were unable to retrieve the attached file

Close Windowclose

Attach and remove files

add attachmentAdd attachment
Close Windowclose

Enter the URL you would like to link to in your post

Close Windowclose

This is how you use right click RTF editing

Enable right click RTF editing option allows you to add html markup into your tutorial such as images, bulleted lists, files and more...

-- click to close --

Uploading file...