CODEWITHSUNDEEP
phpmysqldatabase
ragebunny
ragebunny

Reputation: 1760

Ordering records that are timestamped

I have a tricky one here.

I have a number of results in a table, this table has id, market, date and points.

I want to get all the records for a given month and then output all the points for each day, in order.

I'm not sure of the best way to do this? Her are my options, well my ideas, I could set a loop that will execute for every day of the given month, this will run a query to find every record for that day and will do this for every day of the month, this will then be easy to order a format the records but i'm thinking its not a good idea to query the database that many times.

The second idea i to query the database for all the records for that date, order them by the date. However i can see come problems here when outputting the information for each day, well it will be a long job then the first i think.

Any ideas? anyone done something like this before and could lend a helping hand? Any advise would be more than welcome!

Thanks.

while($row = mysql_fetch_array($getresults))
{
    $day = FALSE;

    foreach ($row as $row)
    {
        $day_res = date("Y-m-d H:m:s", strtotime($row["date"]));

        if (!$day || $day != $day_res)
        {
            $day = $day_res;
            echo '<h1>'.$day.'</h1>';
        }

        echo $row['date'] . " " . $row["id"] . ": " . $row["market"] . 
                    "/" . $row["points"] . "<br/>";
    }
 }

This is the output i get from it:

1970-01-01 00:01:00

3 3: 3/3
3 3: 3/3
2012-01-13 09:01:06

E E: E/E
E E: E/E
1970-01-01 00:01:00

2 2: 2/2
2 2: 2/2
- -: -/-
- -: -/-
1970-01-01 00:01:00

4 4: 4/4
4 4: 4/4

and that more or less repeats for a while.

Thanks again.

Upvotes: 0

Views: 56

Answers (2)

Abadon
Abadon

Reputation: 287

at first the query to get all for a given month ordered by date:

SELECT * FROM `table` WHERE `date` LIKE '2012-01%' ORDER BY date;

here the php loop to show it:

$day = FALSE;
foreach ($results as $result){
    $day_res = date("d", strtotime($result["date"]));
    if (!$day || $day != $day_res){
        $day = $day_res;
        echo '<h1>'.$day.'</h1>';
    }
    echo $result["id"].": ".$result["market"]."/".$result["points"]."<br/>";
}

Ok here for your structure:

$day = FALSE;
while($row = mysql_fetch_array($getresults))
{
        $day_res = date("Y-m-d H:m:s", strtotime($row["date"]));

        if (!$day || $day != $day_res)
        {
            $day = $day_res;
            echo '<h1>'.$day.'</h1>';
        }

        echo $row['date'] . " " . $row["id"] . ": " . $row["market"] . 
                    "/" . $row["points"] . "<br/>";
 }

Upvotes: 1

stefandoorn
stefandoorn

Reputation: 1032

If that date is only a date, then you could try something like this:

SELECT t.date, SUM(t.points) FROM table t WHERE date BETWEEN [STARTDATE HERE] AND [ENDDATE HERE] GROUP BY t.date

What it does? It selects everything the date & the sum of the points of that day, because we group all the results based on a day. So for every unique date in your table you will get a row with a result. Use the WHERE date statement to define from which month you need the selection.

Addition: the thing shown is only valid if date is in format like: YYYY-MM-DD. In case you have a timestamp (YYYY-MM-DD HH:ii:ss) you could try to change the query to:

SELECT t.date, SUM(t.points) FROM table t WHERE date BETWEEN [STARTDATE HERE] AND [ENDDATE HERE] GROUP BY DAYOFMONTH(t.date)

This ensures that you will group them by the day of the month (1 to 31) based on the dates from your table.

Upvotes: 3

Related Questions