0

I have a function i have done that gets the invoice_due_date, it could be in either format: 12/06/2015 or could be 06/12/2015 however i am very new to this and a little lost on how i can do what i want to do.

I need to get date (either format) DD/MM/YYYY or MM/DD/YYYY and compare the date to todays date and if its late to show how many invoices are late.

I guess i need to get the date, check date format and then see if its later than todays today and output the number of how many invoices are late.

PHP so far:

function countLateInvoices() {
    // Connect to the database
    $mysqli = new mysqli(DATABASE_HOST, DATABASE_USER, DATABASE_PASS, DATABASE_NAME);

    // output any connection error
    if ($mysqli->connect_error) {
        die('Error : ('.$mysqli->connect_errno .') '. $mysqli->connect_error);
    }

    $query = "SELECT invoice_due_date 
                FROM `invoices`";

    // mysqli select query
    $results = $mysqli->query($query);

    /* determine number of rows result set */
    $row_cnt = $results->num_rows;

    echo $row_cnt;

    // Frees the memory associated with a result
    $results->free();

    // close connection 
    $mysqli->close();
}
James
  • 1,668
  • 19
  • 50
  • possible duplicate of [How to compare two dates in php](http://stackoverflow.com/questions/8722806/how-to-compare-two-dates-in-php) – Narendrasingh Sisodia Jun 04 '15 at 10:31
  • I dont see it being a duplicate as its my own function and has to get from database, check formats and echo amount of invoices that are late if past todays date. That post is just comparing 2 dates, as i said im new to PHP so hoping someone could help me a little better. – James Jun 04 '15 at 10:34
  • 1
    You shouldn't be saving the dates in the database in your own format - you should use MySQL's own `date` format. That would solve your problem. – Geoff Atkins Jun 04 '15 at 10:35
  • How would that solve my problem? i still need to compare dates and check if its later than todays and output the number of how many are late? – James Jun 04 '15 at 10:35
  • 1
    Show your sql for creating the table – joel goldstick Jun 04 '15 at 10:37
  • they are just stored as varchar(255) which is how i wanted it. – James Jun 04 '15 at 10:40
  • I guess need to loop through results? checking date and comparing and outputting how many are late? – James Jun 04 '15 at 10:43
  • The reason @GeoffAtkins says that using DATE instead of VARCHAR would solve your problem is that you can then use a query along the lines of `SELECT * FROM invoices WHERE invoice_due_date < NOW()` and get all the records that are overdue - MySQL does the date calculation for you if you use the DATE type. – Simon Jun 04 '15 at 13:57
  • As @Simon says, using the correct date format in the database itself would give you access to the MySQL date comparison functions, making your code much more streamlined. It also would remove the need for determining the date format (which is not always possible - 01/02/2015 is valid in both of the date formats you mention, but you'd never be able to determine if it's 2nd January or 1st February. – Geoff Atkins Jun 04 '15 at 14:09

0 Answers0