Jump to content
dil_bert

storing a dataset in a mysql database - 6000 entries

Recommended Posts

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.

 

         
 

Share this post


Link to post
Share on other sites

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) |>----+ 
                                 +-------------------+

Share this post


Link to post
Share on other sites

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 |          +----------+-------------------------------------------------------+
        +--------------+--------+----------+                

Share this post


Link to post
Share on other sites
Posted (edited)

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 by dil_bert

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.