Need Help Date Functions

[GreenSmurf]

SELECT * FROM addressbook, Invoices WHERE Invoices.DateBilled <= (CURDATE() - INTERVAL 30 DAY);

 

I have cut out most of the unnecessary code and I am sure you will notice that addressbook is not used in this query but it is used in the real query. Which is:

SELECT * FROM addressbook, Invoices WHERE (Invoices.DateBilled <= CURDATE() - INTERVAL 30 DAY) AND ((addressbook.lastname LIKE '%%' OR addressbook.firstname LIKE '%%' OR addressbook.lastname2 LIKE '%%' OR addressbook.firstname2 LIKE '%%' OR addressbook.address LIKE '%%' OR addressbook.boss LIKE '%%' OR addressbook.clientID LIKE '%%' OR addressbook.zip LIKE '%%' OR addressbook.zip2 LIKE '%%' OR addressbook.accnt LIKE '%%' OR addressbook.salesrep LIKE '%%' OR addressbook.payrep LIKE '%%' OR addressbook.mobile LIKE '%%' OR addressbook.mobile2 LIKE '%%' OR addressbook.phone2 LIKE '%%' OR addressbook.personal LIKE '%%' OR addressbook.fax LIKE '%%' OR addressbook.home LIKE '%%' OR addressbook.email LIKE '%%' )) ORDER BY addressbook.today, addressbook.lastname, addressbook.firstname, addressbook.boss;

 

The result displayed depending on how I tweak the query either displays nothing or everything. Any help would be appreciated.

 

-B

[Maq]

There is a native MySQL function called, DATE_SUB(), that you should be using.

 

Invoices.DateBilled 
 

Edit: I believe you should also be using NOW() as opposed to CURDATE().

[GreenSmurf]

There is a native MySQL function called, DATE_SUB(), that you should be using.

 

Invoices.DateBilled <= DATE_SUB(NOW(), INTERVAL 30 DAY);

 

Edit: I believe you should also be using NOW() as opposed to CURDATE().

 

Thank you for the suggestion. I had used that before but it still gives me the same results. Any further ideas would be awesome as I am running out of them myself. Thank you.

 

-B

[Maq]

Can you echo out your query string and post the output?

[GreenSmurf]

Can you echo out your query string and post the output?

 

The second code bit listed is the actual output of the query.

SELECT * FROM addressbook, Invoices WHERE (Invoices.DateBilled <= CURDATE() - INTERVAL 30 DAY) AND ((addressbook.lastname LIKE '%%' OR addressbook.firstname LIKE '%%' OR addressbook.lastname2 LIKE '%%' OR addressbook.firstname2 LIKE '%%' OR addressbook.address LIKE '%%' OR addressbook.boss LIKE '%%' OR addressbook.clientID LIKE '%%' OR addressbook.zip LIKE '%%' OR addressbook.zip2 LIKE '%%' OR addressbook.accnt LIKE '%%' OR addressbook.salesrep LIKE '%%' OR addressbook.payrep LIKE '%%' OR addressbook.mobile LIKE '%%' OR addressbook.mobile2 LIKE '%%' OR addressbook.phone2 LIKE '%%' OR addressbook.personal LIKE '%%' OR addressbook.fax LIKE '%%' OR addressbook.home LIKE '%%' OR addressbook.email LIKE '%%' )) ORDER BY addressbook.today, addressbook.lastname, addressbook.firstname, addressbook.boss;

 

Most of the stuff is useless and blank for the time being because this is simply a test run.

 

-B

[Maq]

A couple of things:

 

• LIKE '%%' matches anything and everything.  There's no point to include those clauses.
  
• I meant actually echo the string that's produced implementing the DATE_SUB() function (you should see a date).
  
• What language are you writing this in?
  
• Please post the relevant code.
  
• You would probably want to join these tables.  We would need to know the desired result(s) and the table structures.
  

[artacus]

Biggest problem here is that you are doing a cross join so every row in invoices will be joined to every row in addressbook.

 

Second biggest problem is that your return key seems to be broken and you have a 16 line query on a single line.

[GreenSmurf]

A couple of things:

 

• LIKE '%%' matches anything and everything.  There's no point to include those clauses.
  
• I meant actually echo the string that's produced implementing the DATE_SUB() function (you should see a date).
  
• What language are you writing this in?
  
• Please post the relevant code.
  
• You would probably want to join these tables.  We would need to know the desired result(s) and the table structures.
  

 

1) The Like %% things was removed and will only be used if there is a valid search entered into the search engine. So, we will be ignoring that for now.

2) My script is in PHP but the database is mySQL

3)

SELECT * FROM addressbook, Invoices WHERE Invoices.DateBilled >= DATE_SUB(CURDATE(),INTERVAL 30 DAY) ORDER BY addressbook.today, addressbook.lastname, addressbook.firstname, addressbook.boss;

4) The idea is that it can find any/all past due invoices in the database based on what the user selects. I am pretty sure the error is either in the syntax or my understanding of the query trying to be passed. The number 30 in this particular query is a value selected from a drop down menu- it can be any number the user selects though.

[GreenSmurf]

Biggest problem here is that you are doing a cross join so every row in invoices will be joined to every row in addressbook.

 

Second biggest problem is that your return key seems to be broken and you have a 16 line query on a single line.

 

Is there a way to run the search in Invoices and pull up the data in both addressbook and Invoices at the same time?

[PFMaBiSmAd]

Could you post an example of what is in Invoices.DateBilled?

[artacus]

Is there a way to run the search in Invoices and pull up the data in both addressbook and Invoices at the same time?

 

Sure, but you need to join them. The easiest way is to just add to your where clause.

 

WHERE addressbook.clientID = invoices.clientID

 

The better way is to explicitly join them

 

SELECT *

FROM addressbook ab

JOIN invoices inv ON ab.clientID = inv.clientID

WHERE inv.dateBilled <= CURDATE() - INTERVAL 30 DAY