Re: How do I create rows from an array to create a view in phpMy Admin - mySql

[Jon Stephens]

You need a normalised version of the table, one that looks more or less like this:

| Post | Key                  | StartDate  | EndDate    |
| ---- | -------------------- | ----------   ---------- |
| 5510 | tourmaster-tour-date | 2022-07-18 | 2022-07-25 |
| 6099 | tourmaster-tour-date | 2022-07-18 | 2022-07-25 |
| 6208 | tourmaster-tour-date | 2022-05-09 | 2022-08-18 |

Then it's dead easy to get totals by date. Here's a CREATE VIEW statement that gets this from your table (use your own table & view names in place of these if/as needed):

mysql> CREATE VIEW myview AS
    ->   SELECT 
    ->     post_id               AS Post,
    ->     Meta_key              AS MKey,
    ->     LEFT(Meta_value, 10)  AS StartDate,
    ->     RIGHT(Meta_value, 10) AS EndDate
    ->   FROM mytable;
Query OK, 0 rows affected (0.02 sec)

mysql> SELECT * FROM myview;
+------+----------------------+------------+------------+
| Post | MKey                 | StartDate  | EndDate    |
+------+----------------------+------------+------------+
| 5510 | tourmaster-tour-date | 2022-07-18 | 2022-07-25 |
| 6099 | tourmaster-tour-date | 2022-07-18 | 2022-07-25 |
| 6028 | tourmaster-tour-date | 2022-05-09 | 2022-08-18 |
+------+----------------------+------------+------------+
3 rows in set (0.00 sec)

The dates are still strings rather than the DATE type, but at least you can now filter by date, e.g.

mysql> SELECT * FROM myview WHERE StartDate = '2022-07-18';
+------+----------------------+------------+------------+
| Post | MKey                 | StartDate  | EndDate    |
+------+----------------------+------------+------------+
| 5510 | tourmaster-tour-date | 2022-07-18 | 2022-07-25 |
| 6099 | tourmaster-tour-date | 2022-07-18 | 2022-07-25 |
+------+----------------------+------------+------------+
2 rows in set (0.00 sec)

HTH.

Jon Stephens
MySQL Documentation Team @ Oracle

MySQL Dev Zone
MySQL Server Documentation
Oracle