Show all records before current month for this year

[hane]

I have a mysql table ads that has the following structure. The table is for display purposes only 

<table border="1">
<tr>
<th>adid - int(11)</th> 
<th>month - varchar(255)</th> 
<th>size - varchar(255)</th> 
<th>amount - varchar(255)</th> 
<th>companyid - int(11)</th> 
<th>price - varchar(255)</th> 
<th>description - varchar(255)</th> 
<th>year - varchar(225)</th> 
<th>paid - date</th> 
<th>cpaid - date</th> 
<th>done - int(11)</th>
</tr>
<tr>
<td>1</td> 
<td>Jan</td> 
<td>1/2 Page</td> 
<td>1</td> 
<td>1</td> 
<td>3950</td> 
<td>Ad in dropbox</td> 
<td>2012</td> 
<td>2012-03-06</td>
<td>2012-04-30</td> 
<td>1</td>
</tr>
<tr>
<td>2</td> 
<td>Feb</td> 
<td>1/2 Page</td> 
<td>1</td> 
<td>1</td>
<td>3950</td> 
<td>Ad in dropbox</td> 
<td>2012</td> 
<td>0000-00-00</td> 
<td>0000-00-00</td> 
<td>1</td>
</tr>
</table>

 

I have the following query

  $query_ads = "SELECT *  FROM ads INNER JOIN company ON company.companyid=ads.companyid WHERE paid='0000-00-00' AND ads.year = YEAR(CURRENT_DATE) ORDER BY FIELD(month,'Jan','Feb','March','Apr','May','Jun','Jul','Aug','Sept','Oct','Nov','Dec'), name ";

 

I want to limit the the results to all the records form the beginning of the year to the current month. Is it possible with the structure as is or would I have to change month to a date value

 

Any help would be appreciated.

Thanks

Hanè

   

[hane]

I'm wondering if there is away to do it with php date format M. I dont seem to find anything. That's why I'm posting here

[PFMaBiSmAd]

You would need to use the following in your WHERE clause -

 

FIELD(month,'Jan','Feb','March','Apr','May','Jun','Jul','Aug','Sept','Oct','Nov','Dec') <= MONTH(CURRENT_DATE)

 

If you instead store the numerical month value (and convert it to the month abbreviation for display only), your queries will be greatly simplified and will execute faster.

[smoseley]

Always use a DATETIME field to store date and time of a record.  Will save you a lot of headaches like this, and will ensure your data has the granularity you need for virtually any future need.

[hane]

You would need to use the following in your WHERE clause -

 

FIELD(month,'Jan','Feb','March','Apr','May','Jun','Jul','Aug','Sept','Oct','Nov','Dec') <= MONTH(CURRENT_DATE)

 

If you instead store the numerical month value (and convert it to the month abbreviation for display only), your queries will be greatly simplified and will execute faster.

 

Thanks works great. 

 

This is just for my own use but will remember for next time.

 

Always use a DATETIME field to store date and time of a record.  Will save you a lot of headaches like this, and will ensure your data has the granularity you need for virtually any future need.

 

I know, but this wasn't supposed to have been a date field because is just the publication month. But I have learned my lesson and when ever I will have to use a date like field again I will use date