Asked  7 Months ago    Answers:  5   Viewed   29 times

I have this mysql Table:

+--------------------+---------+-------+
|      date          | query   | count |
|--------------------+---------+-------|
|2012-11-18 09:52:00 | Michael |   1   |
|2012-11-18 10:47:10 |  Tom    |   2   |
|2012-11-17 15:02:12 |  John   |   1   |
|2012-11-17 22:52:10 |  Erik   |   3   |
|2012-11-16 09:42:01 |  Larry  |   1   |
|2012-11-16 07:41:33 |  Kate   |   1   |
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^

and so on. I can simply take results and order them by date in one row via this code:

$queries = mysql_query("SELECT * FROM my_tables ORDER BY date DESC LIMIT 20"); 
while($row = mysql_fetch_array($queries)){
    echo "Name ".$row['query']."";
}

But how to display elements from table ordered by specific date like this:

In 2012-11-18:
Michael
Tom

In 2012-11-17:
John
Erik

In 2012-11-16:
Larry
Kate

and so on. Thanks!

 Answers

52

Here is teh PHP code:

$query = mysql_query("SELECT date, query FROM table6 ORDER BY date DESC LIMIT 20");
$group_date = null;
while ($row = mysql_fetch_assoc($query)) {
    if ($group_date !== substr($row["date"], 0, 10)) {
        $group_date = substr($row["date"], 0, 10);
        echo "<h1>$group_date</h1>n";
    }
    echo "${row['query']}<br>n";
}

Output:

2012-11-18

Tom

Michael

2012-11-17

Erik

John

2012-11-16

Larry

Kate

Note that while this code "groups" rows by one column, it can easily be extended to group rows by multiple columns. Left as an exercise.

Wednesday, March 31, 2021
 
qitch
answered 7 Months ago
15

Try this:

function count_sort($a, $b) {
  if (count($a) == count($b)) {
    return 0;
  }
  return (count($a) < count($b)) ? -1 : 1;
}

$test_array = array(
  'parent1' => array('child1', 'child2', 'child3', 'child4'),
  'parent2' => array('child1'),
  'parent3' => array('child1', 'child2', 'child3', 'child4', 'child5'),
  'parent4' => array('child1', 'child2', 'child3'),
  'parent5' => array('child1', 'child2', 'child3', 'child4', 'child5', 'child6', 'child7')
);

print_r($test_array);
usort($test_array, "count_sort");
print_r($test_array);
Wednesday, March 31, 2021
 
DilbertDave
answered 7 Months ago
62

you can do it like this

SELECT * FROM Table ORDER BY date(dateColumn) DESC Limit 1
Tuesday, June 22, 2021
 
godot
answered 4 Months ago
96

You need an OUTER JOIN to arrive at every day between a start and an end because if you use an INNER JOIN it will restrict the output to just the dates that are joined (i.e. just those dates in the report table).

In addition, when you use an OUTER JOIN you must take care that conditions in the where clause don't cause an implicit inner join; for example AND domain_id = 1 if use in the where clause would suppress any row that did not have that condition met, but when used as a join condition it only restricts the rows of the report table.

SELECT
      COUNT(r.domain_id)
    , all_dates.Date AS the_date
    , domain_id
FROM (
        SELECT DATE_ADD(curdate(), INTERVAL 2 MONTH) - INTERVAL (a.a + (10 * b.a) ) DAY as Date
        FROM (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as a
        CROSS JOIN (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as b
      ) all_dates
      LEFT OUTER JOIN reports r
                  ON all_dates.Date = r.tracked_on
                        AND domain_id = 1
WHERE all_dates.Date BETWEEN '2014-09-01' AND '2014-09-30'
GROUP BY
      the_date
ORDER BY
      the_date ASC;

I have also changed the all_dates derived table, by using DATE_ADD() to push the starting point into the future, and I have reduced the it's size. Both of these are options and can be tweaked as you see fit.

Demo at SQLfiddle


to arrive at a domain_id for every row (as shown in your question) you would need to use someting like the following; Note you could use IFNULL() which is MySQL specific but I have used COALESCE() which is more generic SQL. However use of an @parameter as shown here is MySQL specific anyway.

SET @domain := 1;

SELECT
      COUNT(r.domain_id)
    , all_dates.Date AS the_date
    , coalesce(domain_id,@domain) AS domain_id
FROM (
        SELECT DATE_ADD(curdate(), INTERVAL 2 month) - INTERVAL (a.a + (10 * b.a) ) DAY as Date
        FROM (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as a
        CROSS JOIN (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as b
      ) all_dates
      LEFT JOIN reports r
                  ON all_dates.Date = r.tracked_on
                        AND domain_id = @domain
WHERE all_dates.Date BETWEEN '2014-09-01' AND '2014-09-30'
GROUP BY
      the_date
ORDER BY
      the_date ASC;

See this at SQLfiddle

Wednesday, August 4, 2021
 
vuliad
answered 3 Months ago
87

Should be able to

GROUP BY date(actionTime)

See this for more info.

Tuesday, October 12, 2021
 
Jasper
answered 6 Days ago
Only authorized users can answer the question. Please sign in first, or register a free account.
Not the answer you're looking for? Browse other questions tagged :