How to group dates according to the day of the week

Question

How to group dates according to the day of the week

Asked 10 years, 3 months ago

Viewed 835 times

3

Well, I have a table which has a column of id user’s user_id and one with dates. I would like to know how to group and count the dates that are in the formed. Example: 07.06.14 on the respective days of the week and according to the user user_id that’s on the date line. For example:

  user_id |  data
  1          07.06.14
  1          07.06.14
  2          06.06.14

Must return:

   O usuario 1 possui 2 datas no Sabado.
   O usuario 2 possui 1 data na Sexta.

I tried to create 2 functions for this, but it didn’t work:

function Get_weekday($d,$m,$y){
//Pega o dia da semana em ingles
$date = date("l", mktime(0, 0, 0, $m  , $d, $y));
//Traduz
if($date == "Monday"){return 4;}
if($date == "Tuesday"){return 3;}
if($date == "Wednesday"){return 2;}
if($date == "Thursday"){return 1;}
if($date == "Friday"){return 0;}
if($date == "Saturday"){return 0;}
if($date == "Sunday"){return 0;}
}
function count_entregas_per_date($func_id,$day){
$result = mysql_query("SELECT COUNT(date) as data,date FROM entregas WHERE func_id =    '$func_id' GROUP BY date");
while($row = mysql_fetch_assoc($result)) // Armazena os funcionarios em um array
{
$dat = explode(".",$row['date']);
echo "Na data ". $row['date'] . " houveram " . $row['data']. " pedidos realizados por      "              . username_from_user_id($func_id). "
   .  E foi no dia de codigo " . 
Get_weekday($dat[0],$dat[1],$dat[2]). " </br>";

}}

1

Each BD has its functions to manipulate dates, look for the BD documentation in question for details. But in any case try ANSI EXTRACT http://users.atw.hu/sqlnut/sqlnut2-chp-4-sect-4.html

– Motta

2014/06/07 at 20:04
What exit do you get like this?

– Jorge B.

2014/07/08 at 08:10
Is the date in the database the one mentioned above?? DD/MM/YYYY?? or ta registered with the YYYY/MM/DD database standard, because if you have it in the default format, I send you a function that it already returns everything straight from the bank

– Douglas Bernardino

2014/07/08 at 14:16

2 answers

Browser other questions tagged php sql

You are not signed in. Login or sign up in order to post.

by user6026 · Answer 1 · 2014-06-08T01:28:32+00:00

Always use fields date on your table, not fields varchar with data of the type date, can bring future problems. Another factor does not use mysql_* he is depreciated in the new versions of PHP. Even so I built 3 examples: mysql, mysqli and pdo see below:

Example:

1 ) Mysql (not recommended)

<?php
    function GetSemana($date){
        list($d,$m,$y) = explode('.', $date);
        switch (strtolower(date('l', mktime(0, 0, 0, $m ,$d,$y))))
        {           
            case 'sunday': {
                return ' no domingo';
                break;
            }
            case 'monday': {
                return ' na segunda-feira';
                break;
            }
            case 'tuesday': {
                return ' na ter&ccedil;a-feira';
                break;
            }           
            case 'wednesday': {
                return ' na quarta-feira';
                break;
            }
            case 'thursday': {
                return ' na quinta-feira';
                break;
            }
            case 'friday': {
                return ' na sexta-feira';
                break;
            }
            case 'saturday': {
                return ' no s&aacute;bado';
                break;
            }
        }
    }

    $conn  = mysql_connect("localhost", "root", "senha");
             mysql_select_db("generics", $conn);
    $query = mysql_query("SELECT user_id, data, count(user_id) as datacount FROM entregas GROUP BY user_id, data", $conn);
    while($item = mysql_fetch_array($query))
    {

        echo '['.$item['data'].'] ';
        echo ' O usuario '.$item['user_id'];
        echo ' possui '.$item['datacount'];
        echo ' data'.((int)$item['datacount']>1?'s':'');
        echo GetSemana($item['databr']);
        echo '<br>';
    }

2 ) Mysqli

<?php
    function GetSemana($date){
        list($d,$m,$y) = explode('.', $date);
        switch (strtolower(date('l', mktime(0, 0, 0, $m ,$d,$y))))
        {           
            case 'sunday': {
                return ' no domingo';
                break;
            }
            case 'monday': {
                return ' na segunda-feira';
                break;
            }
            case 'tuesday': {
                return ' na ter&ccedil;a-feira';
                break;
            }           
            case 'wednesday': {
                return ' na quarta-feira';
                break;
            }
            case 'thursday': {
                return ' na quinta-feira';
                break;
            }
            case 'friday': {
                return ' na sexta-feira';
                break;
            }
            case 'saturday': {
                return ' no s&aacute;bado';
                break;
            }
        }
    }

    $conn  = mysqli_connect("localhost", "root", "senha", "generics");
    $query = mysqli_query($conn, "SELECT user_id, data, count(user_id) as datacount FROM entregas GROUP BY user_id, data");
    while($item = mysqli_fetch_array($query))
    {

        echo '['.$item['data'].'] ';
        echo ' O usuario '.$item['user_id'];
        echo ' possui '.$item['datacount'];
        echo ' data'.((int)$item['datacount']>1?'s':'');
        echo GetSemana($item['databr']);
        echo '<br>';
    }

3 ) PDO

<?php
    function GetSemana($date){
        list($d,$m,$y) = explode('.', $date);
        switch (strtolower(date('l', mktime(0, 0, 0, $m ,$d,$y))))
        {           
            case 'sunday': {
                return ' no domingo';
                break;
            }
            case 'monday': {
                return ' na segunda-feira';
                break;
            }
            case 'tuesday': {
                return ' na ter&ccedil;a-feira';
                break;
            }           
            case 'wednesday': {
                return ' na quarta-feira';
                break;
            }
            case 'thursday': {
                return ' na quinta-feira';
                break;
            }
            case 'friday': {
                return ' na sexta-feira';
                break;
            }
            case 'saturday': {
                return ' no s&aacute;bado';
                break;
            }
        }
    }

    $pdo = new PDO("mysql:host=localhost;dbname=generics", "root", "senha");
    $res = $pdo->query("SELECT user_id, data, count(user_id) as datacount FROM entregas GROUP BY user_id, data");
    foreach($res as $item){
        echo '['.$item['data'].'] ';
        echo ' O usuario '.$item['user_id'];
        echo ' possui '.$item['datacount'];
        echo ' data'.((int)$item['datacount']>1?'s':'');
        echo GetSemana($item['databr']);
        echo '<br>';
    }

Obs: can adapt to your script.

by abfurlan • **10,570** points · Answer 2 · 2014-07-08T15:28:50+00:00

As already mentioned, use field DATE and not VARCHAR for dates, an option would be to group directly in the SQL query

SELECT
t.user_id,
COUNT(t.user_id) as qt_dias,

CASE WHEN DATE_FORMAT(t.data,'%w') = 0 THEN 'Domingo' 
     WHEN DATE_FORMAT(t.data,'%w') = 1 THEN 'Segunda-feira'
     WHEN DATE_FORMAT(t.data,'%w') = 2 THEN 'Terça-feira'
     WHEN DATE_FORMAT(t.data,'%w') = 3 THEN 'Quarta-feira'
     WHEN DATE_FORMAT(t.data,'%w') = 4 THEN 'Quinta-feira'
     WHEN DATE_FORMAT(t.data,'%w') = 5 THEN 'Sexta-feira'
     WHEN DATE_FORMAT(t.data,'%w') = 6 THEN 'Sábado'
END as dia_semana

FROM tab t
GROUP BY dia_semana
ORDER BY t.user_id;

With DATE_FORMAT you extract the day of the week, where (0 = Sunday, 1 = Monday, etc...), and using CASE WHEN, you compare and return the day of the week in the desired format.

Example: Sqlfiddle