storing a dataset in a mysql database - 6000 entries

[dil_bert]

good day dear  phpfreaks

 

i have a set of data that i have gatherd from a page,,,,  see below:

 

 

http://europa.eu/youth/volunteering/evs-organisation#open
 

note that are more than 6000 entries .....; 

some of the datasets are more complete than others : in other words, some have more lines of data

 

 

What is aimed: i want to store  the dataset in a mysql database...

 

How to get the first steps to a a viable approach.

  'title' => 'ZWIAZEK MLODZIEZY MNIEJSZOSCI NIEMIECKIEJ',
            'hand' => [
                        'Receiving',
                        'Sending',
                        'Coordinating'
                      ],
            'external_url' => 'http://www.bjdm.eu',
            'topics' => [
                          'Youth (Participation, Youth Work, Youth Policy)',
                          'Creativity and culture',
                          'Romas and/or other minorities'
                        ],
            'pic_number' => '939424146',
            'internal_url' => 'https://europa.eu/youth/volunteering/organisation/939424146_en',
            'location' => 'Opole, Poland'
          },
          {
            'location' => 'Czestochowa, Poland',
            'internal_url' => 'https://europa.eu/youth/volunteering/organisation/947395412_en',
            'hand' => [
                        'Receiving',
                        'Sending',
                        'Coordinating'
                      ],
            'title' => 'Zwiazek Mlodziezy Wiejskiej Czestochowa',
            'topics' => [
                          'Access for disadvantaged',
                          'Creativity and culture',
                          'Early School Leaving / combating failure in education'
                        ],
            'pic_number' => '947395412',
            'external_url' => 'http://www.zmwczestochowa.pl'
          },
          {
            'title' => "ZWIAZEK POLSKICH KAWALER\x{d3}W MALTANSKICH SUWERENNEGO RYCERSKIEGO ZAKONU SZPITALNIK\x{d3}W SWIETEGO JANA JEROZOLIMSKIEGO ZWANEGO RODYJSKIM I MALTANSKIM",
            'hand' => [
                        'Receiving'
                      ],
            'external_url' => 'http://www.centrummaltanskie.eu',
            'topics' => [
                          'Inclusion - equity',
                          'Disabilities - special needs',
                          'Social dialogue'
                        ],
            'pic_number' => '941314385',
            'internal_url' => 'https://europa.eu/youth/volunteering/organisation/941314385_en',
            'location' => "Krak\x{f3}w, Poland"
          },
         

Well - note that are more than 6000 entries .....; 

some of the datasets are more complete than others : in other words, some have more lines of data

 

 

What is aimed: i want to store  the dataset in a mysql database...

 

How to get the first steps to a a viable approach.

 

         
 

[Barand]

From the data shown you will require five db tables. (Inspection of the rest of the data may reveal more)

 

The main tables are Organisation,  hand_type and topic. Because each organisation has multiple occerences of hand and topic, the intermediate tables are required.

+------------------+                                              +------------------+
| organization     |                                              |  hand_type       |
+------------------+                                              +------------------+
| org_id (PK)      |---+-+       +-------------------+     +------| hand_type_id(PK) |
| title            |   | |       |   org_hand        |     |      | description      |
| location         |   | |       +-------------------+     |      +------------------+
| pic_number       |   | |       | org_hand_id (PK)  |     |
| internal_url     |   | +------<| org_id      (FK)  |     |
| external_url     |   |         | hand_type_id(FK)  |>----+
+------------------+   |         +-------------------+
                       |                                          +------------------+
                       |                                          |  topic           |
                       |         +-------------------+            +------------------+
                       |         |  org_topic        |     +------| topic_id (PK)    |
                       |         +-------------------+     |      | description      |
                       |         | org_topic_id (PK) |     |      +------------------+
                       +--------<| org_id       (FK) |     |
                                 | topic_id     (FK) |>----+ 
                                 +-------------------+

[Barand]

The DB data from the above array would look like this (longer fields truncated for brevity)

+--------+---------------------------+---------------------+------------+----------------------+----------------------+
| org_id | title                     | location            | pic_number | int_url              | ext_url              |
+--------+---------------------------+---------------------+------------+----------------------+----------------------+
|      1 | ZWIAZEK MLODZIEZY MNIEJSZ | Opole, Poland       | 939424146  | https://europa.eu/yo | http://www.bjdm.eu   |
|      2 | Zwiazek Mlodziezy Wiejski | Czestochowa, Poland | 947395412  | https://europa.eu/yo | http://www.zmwczesto |
|      3 | ZWIAZEK POLSKICH KAWALER\ | Krak\x{f3}w, Poland | 941314385  | https://europa.eu/yo | http://www.centrumma |
+--------+---------------------------+---------------------+------------+----------------------+----------------------+
   |                                                    
   |                                  
   +---------------------+            +------------------------+       TABLE:
   |                     |            |                        |       hand_type
   |    +-------------+--------+--------------+        +--------------+--------------+
   |    | org_hand_id | org_id | hand_type_id |        | hand_type_id | description  |
   |    +-------------+--------+--------------+        +--------------+--------------+
   |    |           1 |      1 |            1 |        |            1 | Receiving    |
   |    |           2 |      1 |            2 |        |            2 | Sending      |
   |    |           3 |      1 |            3 |        |            3 | Coordinating |
   |    |           4 |      2 |            1 |        +--------------+--------------+
   |    |           5 |      2 |            2 |
   |    |           6 |      2 |            3 |
   |    |           7 |      3 |            1 |
   |    +-------------+--------+--------------+
   |
   +---------------------+           +---------------------+       TABLE:
                         |           |                     |       topic            
        +--------------+--------+----------+          +----------+-------------------------------------------------------+
        | org_topic_id | org_id | topic_id |          | topic_id | description                                           |
        +--------------+--------+----------+          +----------+-------------------------------------------------------+
        |            1 |      1 |        1 |          |        1 | Youth (Participation, Youth Work, Youth Policy)       |
        |            2 |      1 |        2 |          |        2 | Creativity and culture                                |
        |            3 |      1 |        3 |          |        3 | Romas and/or other minorities                         |
        |            4 |      2 |        4 |          |        4 | Access for disadvantaged                              |
        |            5 |      2 |        2 |          |        5 | Early School Leaving / combating failure in education |
        |            6 |      2 |        5 |          |        6 | Inclusion - equity                                    |
        |            7 |      3 |        6 |          |        7 | Disabilities - special needs                          |
        |            8 |      3 |        7 |          |        8 | Social dialogue                                       |
        |            9 |      3 |        8 |          +----------+-------------------------------------------------------+
        +--------------+--------+----------+                

[dil_bert]

dear Barand many

 

 

From the data shown at the page that can be seen here http://europa.eu/you...ganisation#open
we will require some db tables. (Inspection of the rest of the data may reveal more):

the page that i have noted above shows a collection of open source projects in Europe:

we can ... :

- find organisations which can involve volunteers who need some additional support:
- organisations with additional mentoring or other support suitable for volunteers with obstacles, educational difficulties, cultural differences or similar.
- organisations with a physical environment suitable for volunteers with physical, sensory or other disabilities (such as wheelchair access and similar).
- organisations looking for volunteers who face situations that make their participation in the activities more difficult.


back to the database: The main tables are Organisation, hand_type and topic.
Because each organisation has multiple occerences of hand and topic, the intermediate tables are required.

 

 

this was more than expected 

 

 

on a sidenote; the hand type is just a category that shows us the following;:

 

organizations can

 

a. send

b. receive

c cooordinate - the volunteers

 

see for more the above mentioned link http://europa.eu/youth/volunteering/evs-organisation#open

 

dear Barand - now we can go ahead and create a fully fledged way and method to

 

 

a. fetch the data   - eg. with CURL

b. parse it

c. store it into a mysql-db.

 

i am really happy.

 

on a sidenote This is a great approach to learn more about PHP and the techniques.

 

 

many many greetings - have a supergreat day

 

dilbert

Edited May 13, 2018 by dil_bert

[Barand]

A Google search for "Data normalization" should be on your TODO list.