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

show by date

Thread began 8/07/2014 8:48 pm by Paul | Last modified 8/11/2014 6:18 pm by Jason Byrnes | 1472 views | 17 replies |

Paul

show by date

I have a MySQL database that is a list of events happening in the area. On the full list of events page, everything comes up fine.

What I am trying to do is show a few events on another page that start with todays date and go forward.

I have the mySQL "sort_date" field set as date. I found an old post on here that I edited the table fields:
SELECT * FROM events WHERE sort_date => NOW()

Seems like: NOW() is an old method.

Appreciate any assistance.

Here is the link to the page: http://www.conejo365.com/default2.php

Sign in to reply to this post

Jason ByrnesWebAssist

1) For the comparison, => is not valid, it should be >= (Greater Than or Equal To, not Equal To or Greater Than)

2) you need to use Date_Format to format the date in the database and NOW() so they are in the same format:

WHERE DATE_FORMAT(sort_date, "%Y-%m-%d") >= Date_Format(Now(), "%Y-%m-%d")

Sign in to reply to this post

Paul

Jason,

When I format the sort_date field as "date", the content in the fields come up as: 0000-00-00. I had to wipe the table and reimport the csv file.

This is what I put in the SQL section of the recordset:
SELECT * FROM events WHERE sort_date >= NOW()
WHERE DATE_FORMAT(sort_date, "%Y-%m-%d") >= Date_Format(Now(), "%Y-%m-%d")

I also don't understand what you meant with the NOW(). Where do I format that?

When I put this code in the SQL section, I get the error in the attachment. Here is line 1:
<?php require_once('Connections/connConejo.php'); ?>

Thanks
Paul

Sign in to reply to this post

Jason ByrnesWebAssist

your query now has 2 WHERE statements:

WHERE sort_date >= NOW()
WHERE DATE_FORMAT(sort_date, "%Y-%m-%d") >= Date_Format(Now(), "%Y-%m-%d")


only use the one where statement:

SELECT * FROM events
WHERE DATE_FORMAT(sort_date, "%Y-%m-%d") >= Date_Format(Now(), "%Y-%m-%d")




  I also don't understand what you meant with the NOW(). Where do I format that?  




that is what this part of the WHERE statement is doing:
Date_Format(Now(), "%Y-%m-%d")

Sign in to reply to this post

Paul

Jason,

I went into the recordset rsEvents and pasted in what you wrote:
SELECT * FROM events
WHERE DATE_FORMAT(sort_date, "%Y-%m-%d") >= Date_Format(Now(), "%Y-%m-%d")

It tested with no records showing and when I uploaded the page, it was entirely blank. Nothing on the page at all.

Was I supposed to also format the sort_date field in phpmyadmin as date? But when I do that, it turns all the dates to zeros.

Thanks

Sign in to reply to this post

Jason ByrnesWebAssist

I'll need to troubleshoot directly, see the private message section.

Sign in to reply to this post

Paul

See PM

Sign in to reply to this post

Jason ByrnesWebAssist

your sort date is in the wrong format in the Database. the datatype needs to be set to date, and the format should be:

yyyy-mm-dd


you are storing the dates in the wrong format:
7/30/2014


this means the column must a text column like varchar, MySQL can't do math operations like greater than or less than on text.

Sign in to reply to this post

Paul

Jason,

I fixed the sort_date field to be yyyy-mm-dd and re-uploaded and replaced the entire database.

I then put in your code to the SQL statement of the recordset rsEvents:
SELECT * FROM events
WHERE DATE_FORMAT(sort_date, "%Y-%m-%d") >= Date_Format(Now(), "%Y-%m-%d")

When I click test, it shows it correctly. When I upload the page, it is completely blank.

Thoughts?

Sign in to reply to this post

Jason ByrnesWebAssist

a blank page means that a php error is occurring but error reporting is turned off.

to turn error reporting on, add the following code at line 1:

<?php
error_reporting(E_ALL);
ini_set('display_errors','on');
?>



once we know what the error is, we will be able to troubleshoot the cause.

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