Jump to content

storing a dataset in a mysql database - 6000 entries


dil_bert

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.

 

         
 

Link to comment
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) |>----+ 
                                 +-------------------+
Link to comment
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 |          +----------+-------------------------------------------------------+
        +--------------+--------+----------+                
Link to comment
Share on other sites

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

Link to comment
Share on other sites

Archived

This topic is now archived and is closed to further replies.

×
×
  • Create New...

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.