• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 360
  • Last Modified:

Sort by expiry date

I have a table of different competitions. Each as an expiry date in the form:

DD/MM/YYYY

at the moment I am counting the total number of entries using this query:

      $queryTotal = "SELECT COUNT(id) FROM archive_comps;";
      
      $totalResult = mysql_query($queryTotal);
      confirm_query($totalResult);
            
      $total = mysql_fetch_array($totalResult);

However i want to be able to count the entries which are still active ie. have not expired.

How do I do this?

Thanks
0
DrZork101
Asked:
DrZork101
  • 4
  • 4
  • 2
  • +1
2 Solutions
 
houssam_balloutCommented:
you must compare their date with the date of today.

0
 
RemcovCCommented:
This should do the trick:

$queryTotal = "SELECT COUNT(id) FROM archive_comps WHERE CDate(expirydate)>CURRENT_TIMESTAMP;";
0
 
DrZork101Author Commented:
when i use that it says:

FUNCTION databaseName1.CDate does not exist

0
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
RemcovCCommented:
oops sorry, it should be cast(expirydate as datetime)
it's sql not vb ^^
0
 
RemcovCCommented:
(The cast is not needed if the expiredate is already a datetime field)
0
 
DrZork101Author Commented:
the expiry date is stored in the database as DD/MM/YYYY

now its says:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ') > CURRENT_TIMESTAMP' at line 1

0
 
gtsupportCommented:
Maybe this will work

$queryTotal = "SELECT COUNT(id) FROM archive_comps WHERE expirydate> now();";<!-- richText -->
0
 
DrZork101Author Commented:
the query from gtsupport does run, however does no come up with the right results.

does closingDate have to be converted to a specific formatting?
0
 
RemcovCCommented:
yep to datetime format

you can do that by convert or cast
CAST (closigndate as date)
0
 
DrZork101Author Commented:
Thanks remcovC but that did not work I have solved the problem using:

$queryTotal = "SELECT COUNT(id) FROM archive_comps WHERE STR_TO_DATE(closingDate,'%d/%m/%Y') > CURDATE()";
0
 
gtsupportCommented:
Great that it answered your problem. I did not think that you would save the expired date field as a string. You might want to change it to a timestamp format for better performance. That way you will not need to call the STR_TO_DATE function.  It will also allow you to  index your  column  to make it faster to retrieve data.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 4
  • 4
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now