How to group dates according to the day of the week

Question

How to group dates according to the day of the week

Navigation

#1 by (3 votes)
#2 by (0 votes)

3

Well, I have a table which has a column of id of user 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 is in the row of the dates. For example:

  user_id |  data
  1          07.06.14
  1          07.06.14
  2          06.06.14

Should 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 did not 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>";

}}

php sql

asked by anonymous 07.06.2014 / 20:24

2 answers

Conflict of reference Google.api Object instantiation failed: only the first one is created

score 3 · Answer 1

Always use date fields in your table, not varchar fields with data of type date , may cause future problems. Another factor does not use mysql_* it is deprecated in the new versions of PHP . Even though 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.

score 0 · Answer 2

As already mentioned, use field DATE and not VARCHAR for dates, one option would be to do direct collation 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;

DATE_FORMAT .mysql.com / doc / refman / 5.0 / en / case.html "> CASE WHEN , you compare and return the day of the week in the desired format.

Example: SQLFiddle