select * WHERE date = 109649880 (time stamp)

[Paul Ferrie]

I am trying to breakup data returned form a mysql query.
I have a database "worklines"
int ther i have the table "noLines"

ddpID
ddpDate
ddpCount

The date will be set with "date();"
So this will store 109649880 in the database
i will have 100 plus rows in the table
i want to breakdown the data returned in order of months
so month 1 would return the 4 weeks for that month and so on for each month after

I think the query would be something like:

PHP Code:

mysql_query="SELECT * FROM worklines WHERE ddpDate=109649880;
// and so on for each month 


my problem is setting up each time stamp for each month.

I hope you get the picture

[marius]

if i understand corectly you need to get the entries out of the database based on the week.
so then you do ..
select * from worklines where ddpDate>beginoftheweektimestamp and ddpDate<endoftheweektimestamp



is this what you need?

[thumbling]

nonononnono

SELECT * FROM TABLE WHERE date_format("%U", date) = 34;

(but you'd need a mysql datetime format for that, .. if you're able to change into that, please do. use unix_timestamp() to return the date again in epoch seconds)

or else use Marius' way, but rather with date BETWEEN beginoftheweektimestamp and endoftheweektimestamp;

[freddycodes]

Maybe webdreamer would clarify do you want to list one month at a time? Or all data split up my month?

There is nothing wrong with storing as unix time. You can still format dates wit DATE_FORMAT and unix time stamps
http://mysql.he.net/doc/mysql/en/Dat...s.html#IDX1421

SELECT * FROM TABLE WHERE date_format("%U", FROM_UNIXTIME(date)) = 34;

[Paul Ferrie]

What i want to do is display 5 working days of each week of the month.
After spending a view days tinkering about i am still know further forward
Example:
http://www.innovativedesigns.org.uk/test/lines.html
As you will see all data is loaded into the buttons, this is not what i want!

What i want is to load each month into the buttons
So with the data i have ther would be 3 buttons
Augast
September
October

When you click on one of these months a futher row of buttons will appear. These will be the weeks of that month you just clicked.
Now when you click on one of the weeks of that month it will load the 5 days into the graph. all fairly simple except i cant get it to work


Cheers
Paul

[tddlard]

This seems to me like it requires some mysql and php interaction to get the data u need.

	SELECT DISTINCT DATE_FORMAT(ddpDate, %m), DATE_FORMAT(ddpDate, %Y) FROM worklines ORDER BY ddpDate DESC;

Once you have that information passed into php by your mouse event you can get the upper and lower timestamp for that month/year

PHP Code:

<?php
    //assuming variables $month and $year

    $lowerTS = mktime(0, 0, 0, $month, 1, $year);
    $upperTS = mktime(23, 59, 59, $month, date("t", $lowerTS), $year);
    
    //date("t", $lowerTS) gives us the number of days in the month
?>

You would then get that months data

PHP Code:

<?php
    mysql_query("SELECT * FROM worklines WHERE UNIX_TIMESTAMP(ddpDate) BETWEEN " . $lowerTS . " AND " . $upperTS . " ORDER BY ddpDate DESC;");
?>

This should get you where you want to be!