Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Sort by expiry date

Posted on 2008-10-15
11
Medium Priority
?
348 Views
Last Modified: 2013-12-13
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
Comment
Question by:DrZork101
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 4
  • 2
  • +1
11 Comments
 
LVL 17

Expert Comment

by:houssam_ballout
ID: 22718818
you must compare their date with the date of today.

0
 
LVL 6

Expert Comment

by:RemcovC
ID: 22718844
This should do the trick:

$queryTotal = "SELECT COUNT(id) FROM archive_comps WHERE CDate(expirydate)>CURRENT_TIMESTAMP;";
0
 

Author Comment

by:DrZork101
ID: 22718922
when i use that it says:

FUNCTION databaseName1.CDate does not exist

0
Will your db performance match your db growth?

In Percona’s white paper “Performance at Scale: Keeping Your Database on Its Toes,” we take a high-level approach to what you need to think about when planning for database scalability.

 
LVL 6

Assisted Solution

by:RemcovC
RemcovC earned 1000 total points
ID: 22718940
oops sorry, it should be cast(expirydate as datetime)
it's sql not vb ^^
0
 
LVL 6

Expert Comment

by:RemcovC
ID: 22718945
(The cast is not needed if the expiredate is already a datetime field)
0
 

Author Comment

by:DrZork101
ID: 22718980
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
 
LVL 1

Expert Comment

by:gtsupport
ID: 22718979
Maybe this will work

$queryTotal = "SELECT COUNT(id) FROM archive_comps WHERE expirydate> now();";<!-- richText -->
0
 

Author Comment

by:DrZork101
ID: 22719007
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
 
LVL 6

Expert Comment

by:RemcovC
ID: 22719024
yep to datetime format

you can do that by convert or cast
CAST (closigndate as date)
0
 

Accepted Solution

by:
DrZork101 earned 0 total points
ID: 22719190
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
 
LVL 1

Expert Comment

by:gtsupport
ID: 22719364
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

Featured Post

AWS Certified Solutions Architect - Associate

This course has been developed to provide you with the requisite knowledge to not only pass the AWS CSA certification exam but also gain the hands-on experience required to become a qualified AWS Solutions architect working in a real-world environment.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article discusses how to implement server side field validation and display customized error messages to the client.
This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
Learn how to match and substitute tagged data using PHP regular expressions. Demonstrated on Windows 7, but also applies to other operating systems. Demonstrated technique applies to PHP (all versions) and Firefox, but very similar techniques will w…
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …

664 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question