Page 1 of 1

Getting info for the last six months - dates problem

Posted: Mon Feb 06, 2006 7:32 am
by tamiya
I need to know how to design the dates part of my code in order to dynamically get data from my db for the last six months, and then display the info, per month. Remember, this all has to happen dynamically, its for a statistics report...

Any code examples or general tips would help a great deal

Thanks so long

Posted: Mon Feb 06, 2006 10:01 am
by hawleyjr
What does your table look like? Mysql has some great date and time functions that can help you...

http://dev.mysql.com/doc/refman/5.0/en/ ... tions.html

Posted: Tue Feb 07, 2006 12:00 am
by tamiya
this is a replica of what the actual output of data should have had to look like....

Code: Select all

[color=green]<table>
   <tr>
     <td></td><td>Feb</td><td>Jan</td><td>Dec</td><td>Nov</td><td>Oct</td><td>Sep</td>
     <td>Clients</td><td>Feb Info</td><td>Jan Info</td><td>Dec Info</td><td>Nov Info</td><td>Oct Info</td><td>Sep Info</td>
   </tr>
</table>[/color]
Then, eventually, i figured out how to get the information, using variables: eg var1, var2, var3 by running a while loop, to increment the number after 'var' and then display.
But... that made the info display underneath each other:

Code: Select all

[color=green]<tr><td>Feb Info</td></tr>
<tr><td>Jan Info</td></tr>
<tr><td>Dec Info</td></tr>
<tr><td>Nov Info</td></tr>
<tr><td>Oct Info</td></tr>
<tr><td>Sep Info</td></tr>[/color]
...which is not what i wanted.
So, after much pondering and then some, i just realised, shortcuts wont work in this case....

i posted this this morning, but i think it got blocked, cos i didnt put code tags around the code. did that and my problem is sorted out now, thanks, but now i have another problem:

How would i go about getting the top 10 entries for a certain month?
eg. there are a hundred projects in my database (msql) and i would like to see which 10 were viewed the most during the last month...

Posted: Tue Feb 07, 2006 3:07 am
by JayBird
hawleyjr wrote:What does your table look like?
You still haven't answer this question...it is hard to recommend any method without the basic info

Posted: Tue Feb 07, 2006 3:13 am
by dude81
tell us about ur database tables? how do they look like?

Posted: Tue Feb 07, 2006 3:23 am
by tamiya
this problem is solved, thanks for all your help. for each month, to display the data in vertical order, i had to duplicate the code six times. it was long and tedious work, although, if i think about it now, i could have probably done it quicker, using loops. but, main thing is, its working fine.

Posted: Tue Feb 07, 2006 3:25 am
by JayBird
Sounds very inefficient, but if you happy with it :D

Posted: Tue Feb 07, 2006 3:46 am
by tamiya
if i've got time left, i'll go back and rewrite this, but for now, it displays fine, and that is what we need to present to the users in a lil while...

or i'll just leave it for when the next junior comes along to fix, as i had to do in my first week or so