Page 1 of 1

playing with dates of invoice script

Posted: Thu Oct 21, 2004 12:22 am
by Mr Tech
Hi there,

I'm creating an invoice script where you can record invoices. Here are some of the fields in my mysql database:

inv_day
inv_month
inv_year
inv_paid
inv_due
inv_total

In the inv_day, inv_month and inv_year fields, I have numbers. E.g: 20 10 2004
inv_paid is a number how much has been paid. E.g: 100
inv_due is a number how much is to be paid. E.g: 200
inv_total is the total amount that will end up being paid. E.g: 300

Ok, now what I want to do is display outstanding invoices. Outstanding invoices are invoices where the inv_day, inv_month and inv_year are less or equal to the current day you are viewing the script.

Here is my query:

Code: Select all

select * from &#123;$tbl_name&#125;invoices where inv_month<='$this_month' and inv_year<='$this_year' and inv_paid < inv_total and (inv_due!='0' or inv_due!='0.00' or inv_due!='') order by added desc
The problem with that code is that it doesn't seem to work all the time.

Some fields work and some fields, that should work, don't...

Is there anything wrong with my code or is there anything that will make it work better?

feyd | made topic a little more relevant and helpful.

Posted: Thu Oct 21, 2004 12:30 am
by feyd
is there a reason you aren't using a date/timestamp/int (for unix timestamp) field type instead of your day, month, year concept?

Posted: Thu Oct 21, 2004 12:51 am
by Mr Tech
I wasn't sure if it was going to work... but no you've got me thinking...

I suppose I just need to use the mktime to make the time() code for me (as I use a selection box with numbers to select the date) and then do something like:

Code: Select all

select * from &#123;$tbl_name&#125;invoices where inv_date<='$time' and inv_paid < inv_total and (inv_due!='0' or inv_due!='0.00' or inv_due!='') order by added desc
$time would be time()

Would that work? Is the code inv_paid < inv_total correct as well?

Thanks