how to minimize my query?

i want to minimize my query

 "SELECT * FROM tb_videos  GROUP BY DATE(added) ORDER BY DATE(added) desc"

after get result

foreach($result as $rst)
{    
  $dt=date('Y-m-d',strtotime($rst->added));
  SELECT * FROM tb_videos WHERE  DATE(added)='$dt' ORDER BY added desc
}

Can i do with single q开发者_JAVA百科uery?

Your queries make no sense. First you are selecting the DISTINCT dates (ignore time) from the data. Then for each date, you.. select all data for that date?

Why don't you just fire one query

SELECT *, DATE(added) as DateNoTime
FROM tb_videos
ORDER BY added desc

If you only want 5 dates, and the table is large, there are two possibilities.

1, There are never gaps in dates, you can use

SELECT *, DATE(added) as DateNoTime
FROM tb_videos
WHERE added >= ADDDATE(CURDATE(), interval -4 days)
ORDER BY added desc

2, If there may be gaps, e.g. nothing for yesterday so it has to show last 5 days that have records

SELECT *, DATE(added) as DateNoTime
FROM (
    select min(DateNoTime) as MinDate
    from
    (
        select DATE(added) as DateNoTime
        FROM tb_videos
        order by DateNoTime desc
        limit 5
    ) x) y, tb_videos
WHERE added >= y.MinDate
ORDER BY added desc

This gives you all the data. In PHP, keep track of DateNoTime. Whenever that changes, you are in a different date, which would have previously caused you to fire another query. The code should otherwise only change minimally.

Unchecked PHP code

$result = mysql_query('
    SELECT *, DATE(added) as DateNoTime
    FROM (
        select min(DateNoTime) as MinDate
        from
        (
            select DATE(added) as DateNoTime
            FROM tb_videos
            order by DateNoTime desc
            limit 5
        ) x) y, tb_videos
    WHERE added >= y.MinDate
    ORDER BY added desc
');
$prevdate = NULL;
foreach($result as $rst)
{    
    if($prevdate!=$rst=>DateNoTime) {
        // do something, like printing out a header for each new date
        $prevdate=$rst=>DateNoTime;
    }

    // do something with the record
}

You want to minimise the records found? use a where clause to choose your specific records

"SELECT * FROM tb_videos WHERE videogroup = action GROUP BY DATE(added) ORDER BY DATE(added) desc"

For example

SELECT * FROM tb_videos WHERE DATE(added) = curdate() ORDER BY added desc