MySQL Forums
Forum List  »  Newbie

Re: How do I create rows from an array to create a view in phpMy Admin - mySql
Posted by: Jon Stephens
Date: December 09, 2021 07:46AM

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
Orlando, Florida, USA

MySQL Dev Zone
MySQL Server Documentation
Oracle

Options: ReplyQuote




Sorry, only registered users may post in this forum.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.