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

Dates not sorting properly...

Thread began 10/12/2014 2:38 pm by Paul | Last modified 4/28/2015 4:45 pm by Ray Borduin | 2797 views | 78 replies |

Paul

Dates not sorting properly...

Jason,

You helped me sort this data before and it was working perfectly on the home page, as well as the events page. Here is the SQL Statement:

SELECT *
FROM events
WHERE DATE_FORMAT(sort_date, '%Y-%m-%d') >= Date_Format(Now(), '%Y-%m-%d') AND authorize = 1

Not sure if it is the data that is not entered properly on these few events on the home page, but it is out of order. On the events page, it is perfect. Not sure why.

See PM

Sign in to reply to this post

Jason ByrnesWebAssist

you need to add an order by clause:

SELECT *
FROM events
WHERE DATE_FORMAT(sort_date, '%Y-%m-%d') >= Date_Format(Now(), '%Y-%m-%d') AND authorize = 1 ORDER BY sort_date ASC

Sign in to reply to this post

Paul

Jason,

Any idea why it works on one of the pages and not the home page?

Thanks!

Sign in to reply to this post

Jason ByrnesWebAssist

because the other page already has the order by clause in place, look at line 61.

Sign in to reply to this post

Paul

That's interesting because when I go to the Server Behaviors panel and double-click on the Recordset (rsEvents), it doesn't show the "ORDER BY sort_date ASC".

Thanks for your help!

Sign in to reply to this post

Paul

Hey Jason,

With the records having a start_date and end_date, if I wanted to stop a record from showing on the website based on the end_date (lots of events are more than 1 day, so we want them to show for the entire period but still have the same sorting), how would I manipulate the query below? I've tried a few things, but can't get past the MySQL test in dreamweaver.

SELECT *
FROM events
WHERE DATE_FORMAT(sort_date, '%Y-%m-%d') >= Date_Format(Now(), '%Y-%m-%d') AND authorize = 1 ORDER BY sort_date ASC

Thanks!

Sign in to reply to this post

Jason ByrnesWebAssist

WHERE DATE_FORMAT(sort_date, '%Y-%m-%d') >= Date_Format(Now(), '%Y-%m-%d') AND DATE_FORMAT(end_date, '%Y-%m-%d') >= Date_Format(Now(), '%Y-%m-%d') AND authorize = 1

Sign in to reply to this post

Paul

Appreciate this Jason, but it isn't showing any results at all. With the original code, I realized today that some events that are in the admin site in 2015 are not showing up on the live site and some are. I verified that the dates are entered properly (comparing the ones that are showing up to the ones that are not) and they are correct.

Any thoughts would be appreciated.

See PM

Sign in to reply to this post

Jason ByrnesWebAssist

Do the ones that are not showing up have an end date value?


if they do not have an endnote set, the query won't return them.

you will need to set an end date.

Sign in to reply to this post

Paul

I admit, Jason, the person who was entering dates was not putting in the "Sort Date" properly. I just finished going through the entire database and fixing the ones that had the Sort Date incorrect and the website is still maxing out at 69 records. Even after they corrected ones showed up on the site, the total record count was not going past 69.

Thoughts?

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