MySQL Forums
Forum List  »  Newbie

Re: How do I create rows from an array to create a view in phpMy Admin - mySql
Posted by: Phillip Ward
Date: December 09, 2021 03:36AM

Database != Spreadsheet

Your data should be stored as in your second example.
(Your database can then create the first with ease!)

As it is, you are breaking fundamental Data Normalisation rules which will, potentially, make your use of this data painfully slow.
If you are ever going to query this data for any one of those dates, you must make this change and do it now! Delay will only make things worse.

If you have been "dumped" with this data structure by someone else, then go and "have words" with them:

    (Plus 1): They have used a consistent Date format.
    (Minus 1): They are storing Dates in a Character field.
    (Minus several million): They are storing multiple values in the same field. No. Just, No.

+----------+----------+----------------------+------------+
| meta_id  | post_id  | Meta_key             | Meta_Value |
| int (PK) | int (PK) | varchar              | date       |
+----------+----------+----------------------+------------+
|        1 |     5510 | tourmaster-tour-date | 2022-07-18 |
|        1 |     5510 | tourmaster-tour-date | 2022-07-25 |
|        2 |     6099 | tourmaster-tour-date | 2021-12-27 |
|        2 |     6099 | tourmaster-tour-date | 2022-01-27 |
|        3 |     6208 | tourmaster-tour-date | 2022-05-09 |
|        3 |     6208 | tourmaster-tour-date | 2022-08-18 |
+----------+----------+----------------------+------------+

Change the table structure to match your second example.
Read up on the group_concat() function to generate the first, if it is required.

Regards, Phill W.

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.