[SOLVED] mySQL DATE manipulation

[GreenSmurf]

I am having trouble with this query. The idea is to take the month and day to get the day of year and compare. The part of the code that give me the fits is the portion after 'WHERE' most likely this portion:

(DAYOFYEAR((YEAR(CURDATE()),celebday.bmonth,celebday.bday)

 

Here is the whole query:

SELECT * FROM celebday LEFT JOIN month_lookup ON celebday.bmonth=month_lookup.bmonth WHERE (DAYOFYEAR((YEAR(CURDATE()),celebday.bmonth,celebday.bday) BETWEEN DAYOFYEAR(CURDATE()) AND DAYOFYEAR(ADDDATE(CURDATE(), INTERVAL 2 DAY))) ORDER BY rand() limit 1;

 

The idea is that I want to have the site display birthdays for that day except on Fridays to show the whole weekend because nobody works on the weekend. Problems arise at the end of the month when simply adding two days in INTERVAL form do not calculate properly because there is no month with more than 31 days and on occasion a month ends on a weekend just like this past October. Any help would be appreciated.

 

-Brandon

[GreenSmurf]

My table structure looks like this:

 

Field          Type        Null

firstname    text          NO

lastname    text        YES

bday          tinyint(2)    NO

byear        varchar(4)  NO

bmonth      varchar(50) NO

 

-Brandon

[GreenSmurf]

SELECT * FROM celebday LEFT JOIN month_lookup ON celebday.bmonth=month_lookup.bmonth WHERE

I feel this portion is relatively straight forward. The bmonth columns are set equal to one another using left join.

(DAYOFYEAR((YEAR(CURDATE()),celebday.bmonth,celebday.bday)

This is the portion I believe to be causing the issue. The idea is to get the DAYOFYEAR from the information within its parentheses. Day of year requires year, day, and month. So, I tried to use YEAR() to extract the the current year from CURDATE() and used the table values celebday.bmonth and celebday.bday for the rest of the info. However, the program hiccups here and I get an error that reads:

SQL Error: 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 'ORDER BY rand() limit 1' at line 1

Speak of the devil here is the portion of code the program is talking about:

BETWEEN DAYOFYEAR(CURDATE()) AND DAYOFYEAR(ADDDATE(CURDATE(), INTERVAL 2 DAY))) ORDER BY rand() limit 1;

Still no idea but I am actively working on it.

 

-GreenSmurf

[Mchl]

There should be just two )) after DAY

[GreenSmurf]

There should be just two )) after DAY

Thank you. I had noticed that. I forgot to post about it. I changed:

(DAYOFYEAR((YEAR(CURDATE()),celebday.bmonth,celebday.bday)

to

(DAYOFYEAR((YEAR(CURDATE()),celebday.bmonth,celebday.bday))

 

Now I get this error:

SQL Error: Operand should contain at leats 1 column(s)

 

-Smurf

[xtopolis]

Problems arise at the end of the month when simply adding two days in INTERVAL form do not calculate properly because there is no month with more than 31 days and on occasion a month ends on a weekend just like this past October.

 

I'm confused:

SELECT "2009-10-31", DATE_ADD("2009-10-31", INTERVAL 2 DAY)

2009-10-31    DATE_ADD("2009-10-31", INTERVAL 2 DAY)

2009-10-31 2009-11-02

That seems to work for me?

 

 

Anyway, to address your query, it should probably look closer to this:

SELECT * FROM celebday c
  LEFT JOIN month_lookup m
    USING(bmonth)
    
  WHERE DAYOFYEAR(CONCAT((YEAR(CURDATE()), '-', c.bmonth, '-', c.bday))
  
  BETWEEN DAYOFYEAR(CURDATE()) 
    AND DAYOFYEAR(DATE_ADD(CURDATE(), INTERVAL 2 DAY)) 
   
   ORDER BY rand() limit 1;

 

You'll notice I fixed your "WHERE DAYOFYEAR" concatenation (you didn't have any!) to be correct.  Realize though that this query makes me cry and it should not be done this way.  If the dates were stored better in the table, you would be able to eliminate a lot of code.  Why do you break up the  Day, Month, Year and use tinyint/varchar when you could use a simple timestamp column in the format YYYY-MM-DD H:i:S (something like that)....  You're creating more work than necessary.

 

I would like to state that while this may get you closer to your desired output, it should really be fixed considerably.

[GreenSmurf]

Thank you xtopolis for your input. I do like the way your query is very clean. I do feel very much like a noob for not concatenating the query.

I'm confused:

SELECT "2009-10-31", DATE_ADD("2009-10-31", INTERVAL 2 DAY)

2009-10-31    DATE_ADD("2009-10-31", INTERVAL 2 DAY)

2009-10-31 2009-11-02

That seems to work for me?

 

 

Anyway, to address your query, it should probably look closer to this:

SELECT * FROM celebday c
  LEFT JOIN month_lookup m
    USING(bmonth)
    
  WHERE DAYOFYEAR(CONCAT((YEAR(CURDATE()), '-', c.bmonth, '-', c.bday))
  
  BETWEEN DAYOFYEAR(CURDATE()) 
    AND DAYOFYEAR(DATE_ADD(CURDATE(), INTERVAL 2 DAY)) 
   
   ORDER BY rand() limit 1;

 

You'll notice I fixed your "WHERE DAYOFYEAR" concatenation (you didn't have any!) to be correct.  Realize though that this query makes me cry and it should not be done this way.  If the dates were stored better in the table, you would be able to eliminate a lot of code.  Why do you break up the  Day, Month, Year and use tinyint/varchar when you could use a simple timestamp column in the format YYYY-MM-DD H:i:S (something like that)....  You're creating more work than necessary.

 

I would like to state that while this may get you closer to your desired output, it should really be fixed considerably.

 

The reason it was stored like that is because I used a script to take celebrity birthdays off of a website and it was the only way I could get it to store.

 

I tried your code and I still get the error:

SQL Error: 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 'ORDER BY rand() limit 1' at line 1

 

I have been trying to figure out why it does not like the query as it is but have not had too much luck. I will be working more on this soon. Probably tomorrow. If anyone else has suggestions I would appreciate it.

 

-GreenSmurf

[GreenSmurf]

I know double posts are annoying but I wanted to attach the tables being used.

 

[attachment deleted by admin]

[xtopolis]

Try this:

SELECT * FROM celebday c
  LEFT JOIN month_lookup m
    USING(bmonth)
    
  WHERE DAYOFYEAR(CONCAT(YEAR(CURDATE()), '-', m.bmonth_num, '-', c.bday))
  
  BETWEEN DAYOFYEAR(CURDATE()) 
    AND DAYOFYEAR(DATE_ADD(CURDATE(), INTERVAL 2 DAY)) 
   
   ORDER BY rand() limit 1 

 

I'm not sure if it does what you want or not, too many rows.

[GreenSmurf]

Try this:

SELECT * FROM celebday c
  LEFT JOIN month_lookup m
    USING(bmonth)
    
  WHERE DAYOFYEAR(CONCAT(YEAR(CURDATE()), '-', m.bmonth_num, '-', c.bday))
  
  BETWEEN DAYOFYEAR(CURDATE()) 
    AND DAYOFYEAR(DATE_ADD(CURDATE(), INTERVAL 2 DAY)) 
   
   ORDER BY rand() limit 1 

 

I'm not sure if it does what you want or not, too many rows.

 

OMG Hax! You got it! Thank you so very much!

 

-Smurf