how to create structure with data repeated in a certain period-CodePudding

I need to return data from two tables this way

Array(
    [0] => Array(
        [Name] => lorem,
        [Qty] => 2),
    [1] => Array(
        [Name] => ipsum,
        [Qty] => 1),
    [2] => Array(
        [Name] => dolor,
        [Qty] => 0)
    )

PHP will only take care of getting the data, there will be no manipulation to organize it. Two tables, one to record a certain event, the date and the id of a table related to the event. And the tables are like this:

tableA
id |   date     | tableB_id
1    2020-10-02   2
1    2020-10-19   2
1    2020-10-21   1
1    2020-11-2    3
1    2020-11-11   1

tableB
id | name     
1    lorem
2    ipsum
3    dolor

And my SQL query

SELECT b.name as Name, a.created_at as created
FROM tableA b 
 INNER JOIN tableA a ON b.tableA_id = a.id WHERE MONTH(b.created_at) = '10' ORDER BY a.id;

which results in

Name | created
ipsum  2020-10-02
ipsum  2020-10-19
lorem  2020-10-21

CodePudding user response：

You can Left Join TableA, but it irst need to have all the wanted rows as a subquery, to get also the 0 count

CREATE TABLE tableA (
  `id` int,
  `date` date,
  `tableB_id` int
);

INSERT INTO tableA
  (`id`, `date`, `tableB_id`)
VALUES
  ('1',    '2020-10-02'  , '2'),
  ('1' ,   '2020-10-19'  , '2'),
  ('1' ,   '2020-10-21'  , '1'),
  ('1' ,   '2020-11-02' ,   '3'),
  ('1' ,   '2020-11-11',   '1');

CREATE TABLE tableB (
  `id` int,
  `name` varchar(19)
);

INSERT INTO tableB
  (`id`, `name`)
VALUES
  ('1',    'lorem'),
  ('2',    'ipsum'),
  ('3',    'dolor');

SELECT b.`name`, Count(`tableB_id`) 
FROM tableB b LEFT JOIN (SELECT * FROM  tableA WHERE MONTH(`date`) = '10') a ON a.tableB_id = b.id
 
GROUP BY b.name
;

name  | Count(`tableB_id`)
:---- | -----------------:
lorem |                  1
ipsum |                  2
dolor |                  0

db<>fiddle here

CodePudding user response：

You can use next php code:

$query = "SELECT 
    name Name, COUNT(tableA.id) Qty
FROM tableB
LEFT JOIN tableA ON tableB.id = tableA.tableB_id AND MONTH(tableA.date) = 10
GROUP BY tableB.name;";

// get DB version using PDO
$stmt = $pdo->prepare($query);
$stmt->execute();
$rows = $stmt->fetchAll(PDO::FETCH_ASSOC);

print_r($rows);

Live PHP fiddle

Result:

Array
(
    [0] => Array
        (
            [Name] => dolor
            [Qty] => 0
        )

    [1] => Array
        (
            [Name] => ipsum
            [Qty] => 2
        )

    [2] => Array
        (
            [Name] => lorem
            [Qty] => 1
        )

)