In MySQL how can I check through array of sub ids?

Question

I have a table in MySQL called bundles, this table has a column called attractions_array which is a comma separated list of "attraction ids" e.g 41,13,60

attractions is another table in the database that has all the info about each of these attractions.

I have figured out how to get the first id from the array using: substring_index(attractions_array,',',1) but this isn't too helpful.

I somehow need to check through each of these ids from their own attractions table to do things like:

Average & total price for all of the attractions
Make sure the attraction is still available
etc...

How would this be possible in MySQL? I don't even know where to start looking.

If MySQL was PHP I would probably write something like this to get the job done:

foreach ($bundles as $bundle) {

    // init pricing
    $bundle->price_count = 0;
    $bundle->price_total = 0;

    // each of the attractions
    $attractions = explode(",", $bundle->attractions_array);
    foreach ($attractions as $attraction) {

        // no longer available
        if (!$attraction->available) {
            continue 2;
        }

        // count pricing
        $bundle->price_count++;
        $bundle->price_total += $attraction->price;

    }

    // average pricing
    $bundle->price_average = $bundle->price_total / $bundle->price_count;

}

My current statement

SELECT * FROM `bundles` LIMIT 0,10

Is something like this even possible in MySQL?

You shouldn't be storing this as a comma separated list. You should have a linking table with a row for each `(attractionid, bundleid)` — Martin Smith, Nov 19 '17 at 13:04
Does that help me accomplish anything i'm trying to do though? — Mattijs, Nov 19 '17 at 13:08
yes. If you have it structured that way everything you are attempting is straight forward. You just join your attraction_bundles table onto bundles and or attractions and perform any needed filtering and aggregation. — Martin Smith, Nov 19 '17 at 13:09
That's already have an answer, of how you can join your "attractions" https://stackoverflow.com/questions/40578234/how-select-row-in-mysql-by-check-when-column-data-include-delimiter/40578475#40578475 but once again, the complexity of troubles you will have with your coma-separated values will only keep rising — 2oppin, Nov 19 '17 at 13:13

Stefano Maglione · Accepted Answer · 2017-11-19T13:52:55.183

1

Following relational-model approach, you should create a relation between the table 'bundles' and the table 'attractions' that is not based on a list of ids in a field like you did. If the relation between 'bundles' and 'attractions' is many to many, that means bundles can have many attractions and attractions can be related to many bundles, you need to do something like this:

table bundles               pivot table              table attractions

  id   name            id_bundles  id_attract             id  name

where in your pivot table the id_bundles and id_attract have foreign key constraint relative to bundles and attractions.

With a simple join you will be able at this point to retrieve what you need easily.

A MySQL query example to retrieve all the attractions related to one bundle could be:

SELECT * FROM attractions JOIN pivot_table ON (pivot_table.id = attractions.id) where pivot_table.id = customIdbundles

where customIdbundles is the id of the bundle you need info about.

edited Nov 19 '17 at 13:52

answered Nov 19 '17 at 13:21

Stefano Maglione

3,946
11
49
96

This is an easy change and I'm definitely going to implement this "pivot table"! Would you mind giving me an example of how to use the pivot table in the way i'm wanting though? Or maybe a link to a good article explaining it? Thanks so much! – Mattijs Nov 19 '17 at 13:38
@Mattijs if this answer was helpful please confirm it as accepted. – Stefano Maglione Nov 19 '17 at 15:45
I gave you a general idea, we cannot have a programming course here in chat. I encourage you to study sql and you will find your solution – Stefano Maglione Nov 19 '17 at 18:06

In MySQL how can I check through array of sub ids?

1 Answers1