Hi.
I'm working on a invoice register that is suppose to control which date periods each customer have received an invoice for and which periods that are "uninvoiced".

I'm simply(?) wondering how to check if the period i try to register as invoiced is allready registred in the database.

Example:
Custom "a" has three records in the "invoiceRegistry" table. Each record is registred with invoiceFrom(DATE), invoicTo(DATE) and customerID(references id in my customer/booking table).

I want to build a php function that checks if the period I try to register(or part of the period) is allready registred as "invoiced" or not.

If customer "a" has registred this period:
2007-01-01(from) - 2007-01-14 (to)
I would like to return an error if I try to register an invoice for the period 2007-01-05(from) - 2007-01-18(to) etc etc.

Help would be much appreciated.

    I would say check if either dates are between an already submitted time period:

    SELECT customerID FROM invoiceRegistry WHERE ('2007-01-01' BETWEEN invoiceFrom AND invoiceTo) OR ('2007-01-18' BETWEEN invoiceFrom AND invoiceTo) LIMIT 1

    You would run that query and check if 0 rows were returned. If so, the date range is OK to submit.

    If 1 row is returned, then the date range submitted overlaps with another (the customerID of the conflicting row is returned; if you don't need this, you could just do a "SELECT 1" or something arbitary yet simple).

      Write a Reply...