close ad
WARNING: Do Not Install the DREAMWEAVER CC 2017 or 2018 Update »
open ad
View Menu

Technical Support Forums

Free, outstanding support from WebAssist and your colleagues

rating

Filtering a recordset based on 'todays' date - how to compare a start date and end date?

Thread began 5/18/2017 10:45 am by Nathon Jones Web Design | Last modified 6/12/2017 12:59 pm by Ray Borduin | 116 views | 11 replies |

Nathon Jones Web Design

Filtering a recordset based on 'todays' date - how to compare a start date and end date?

What is wrong with this...

<?php
@session_start();
if("" == "") {
$_SESSION["todaySESH"] = " ". date('d M Y h:m:s')." ";
}
?>

<?php
$rsLIVECOMP = new WA_MySQLi_RS("rsLIVECOMP",$csdbmysqli,1);
$rsLIVECOMP->setQuery("SELECT dreamcarCOMPID, dreamcarCOMPDATESTART, dreamcarCOMPDATEND, dreamcarCOMPTITLE, dreamcarCOMPSUMMARY, dreamcarCOMPTEXT, dreamcarCOMPIMG, dreamcarCOMPDELETE FROM dreamcarCOMPS WHERE dreamcarCOMPDATESTART <= ? AND dreamcarCOMPDATEND >= ?");
$rsLIVECOMP->bindParam("t", "".(isset($_SESSION['todaySESH'])?$_SESSION['todaySESH']:"") ."", "-1"); //colname
$rsLIVECOMP->bindParam("t", "".(isset($_SESSION['todaySESH'])?$_SESSION['todaySESH']:"") ."", "-1"); //colname1
$rsLIVECOMP->execute();
?>



I want to only show results where the competition start date is less than, or equal to, today's date/time but ALSO where the end date is greater than, or equal to today's date.

I'm trying to only show competitions that are available during their start and end dates.

At present, using the above, it displays any competition regardless of the dates in the database.

Hope you can help. Thank you.
NJ

Sign in to reply to this post

Ray BorduinWebAssist

You shouldn't need to use parameters or session variables. You can just use:

SELECT dreamcarCOMPID, dreamcarCOMPDATESTART, dreamcarCOMPDATEND, dreamcarCOMPTITLE, dreamcarCOMPSUMMARY, dreamcarCOMPTEXT, dreamcarCOMPIMG, dreamcarCOMPDELETE FROM dreamcarCOMPS WHERE dreamcarCOMPDATESTART <= CURDATE() AND dreamcarCOMPDATEND >= CURDATE()

Sign in to reply to this post

Nathon Jones Web Design

I have the start date set, just now in the database, as "07/06/2017 12:00:00" however it's not picking it up even though that date is less than or equal to CURDATE().

<?php
$rsLIVECOMP = new WA_MySQLi_RS("rsLIVECOMP",$csdbmysqli,1);
$rsLIVECOMP->setQuery("SELECT dreamcarCOMPID, dreamcarCOMPDATESTART, dreamcarCOMPDATEND, dreamcarCOMPTITLE, dreamcarCOMPSUMMARY, dreamcarCOMPTEXT, dreamcarCOMPIMG, dreamcarCOMPDELETE FROM dreamcarCOMPS WHERE dreamcarCOMPDATESTART <= CURDATE() AND dreamcarCOMPDATEND >= CURDATE()");
$rsLIVECOMP->execute();
?>



dreamcarCOMPDATESTART = 07/06/2017 12:00:00
dreamcarCOMPDATEND = 14/06/2017 12:00:00

Hope you can help. Thank you.
NJ

Sign in to reply to this post

Ray BorduinWebAssist

It is probably using US format. 07/06/2017 12:00:00 is next month. Try 05/05/2017 and see if that works. Is that field set as a type Date/Time field?

Sign in to reply to this post

Nathon Jones Web Design

No, because if I change it to 06/06/2017 12:00:00 it picks it up correctly. It's like it's out by one day?

DATETIME field, yes.

Sign in to reply to this post

Ray BorduinWebAssist

06/06/2017 is in the past in US format.

What about when you enter 06/15/2017. I think the database is just using US date format.

Sign in to reply to this post

Nathon Jones Web Design

When I enter 06/15/2017, via ODBC connection in Access, it auto changes to 15/06/2017.
How do I fix this? The client inserts the date via a date picker in an admin section and it's inserting in the correct format, or so I thought.

Thank you.
NJ

Sign in to reply to this post

Ray BorduinWebAssist

It may be that your Access database is set to European date format and the MySQL database is set to US format. What happens when you update from phpMyAdmin directly?

Sign in to reply to this post

Nathon Jones Web Design

In phpMyAdmin:
"Warning #1265 Data truncated for column 'dreamcarCOMPDATESTART' at row 1"

Thing is, the date picker on the website is inserting in UK format and the database accepts it and displays it properly. It's only the SELECT statement that's misinterpreting it.

Urggh, this is messy. What am I doing wrong?
NJ

Sign in to reply to this post

Ray BorduinWebAssist

If you use dashes instead of slashes then the php understands the date format and enters it properly. MySQL only accepts the date format YYYY-MM-DD, so all dates are converted to that before inserting or updating.

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...