CODEWITHSUNDEEP
phpmysqlsql
Sami Lafrance
Sami Lafrance

Reputation: 43

Create an SQL table with arrays

I have a question! I don't really know how to explain it, so I'll illustrate it!

I know how to make tables on SQL that will have this shape :

var data = {
  "news" :[
    {
      "title" : "I am the title 1",
      "number of views" : 28,
      "text" : "Here is the text",
      "date of publication" : "13 March"
    },
    {
      "title" : "I am the title 2",
      "number of views" : 57,
      "text" : "Here is the text",
      "date of publication" : "24 May"
      ]
    },
    {
      "title" : "I am the title 3",
      "number of views" : 74,
      "text" : "Here is the text",
      "date of publication" : "27 May"
    }
  ]
};

Now I would like to be able to create a table that will have this shape :

var data = {
  "staff member" :[
     {
      "First name" : "John",
      "Last name" : "Doe",
      "schedules" : [
          "Friday : 14h - 22h",
          "Saturday : 10h - 19h",
          "Sunday : 10h - 19h"
      ]
    },
    {
      "First name" : "Amelia",
      "Last name" : "Doe",
      "schedules" : [
          "Friday : 18h - 23h",
          "Saturday : 9h - 17h",
          "Sunday : 8h - 20h"
      ]
    }
  ]
};

See the difference? Sorry if it's a little confusing, but I don't really know what to call it!

I think it can be done by creating another table and finding myself with a common id for the first name, the last name on one side and the schedules on the other. But there must be a way to do it more simply, right?

So my question is simple, is it possible? And if so, how?

I did it in JSON, but now I'd like to switch to MySQL, and I don't know how to do that!

Thank you for your answers.

EDIT :

The issue is about schedules and staff members. So I have to create 2 different tables and link the IDs between them to have a staff members with his schedules?

Upvotes: 1

Views: 49

Answers (1)

daShier
daShier

Reputation: 2116

SQL does not support the concept of "sub tables" the way you are showing in your code. Instead you should create two separate tables similar to:

    CREATE TABLE staff_member (
        staff_member_id int,
        First_name varchar,
        Last_name varchar
    )

    CREATE TABLE schedules (
       staff_member_id int,
       day_of_week varchar,
       start_hour int,
       end_hour int
    )

Then you can select the data using:

   SELECT m.First_name, m.Last_name, s.day_of_week, s.start_hour, s.end_hour
      FROM staff_member m 
      LEFT JOIN schedules s ON m.staff_member_id = s.staff_member_id
      ORDER BY m.Last_name, m.First_name, s.day_of_week, s.start_hour, s.end_hour

Of course, you can add a WHERE clause to select specific information.

Upvotes: 1

Related Questions