efficient way to match users common data in the database?

Question

An example: The user can enter his hobbies, like 'golf, football, tennis'. I want to list all the users with the same hobbies (or just one of them).

My approach: A field in the database named 'hobbies' and fill it with all hobbies, seperated with commas like above. Then I get this data with php, fill an array and match the users in a loop. But it seems this is very unefficent way.

I thought about to save the hobbies in seperate columns in the database, but then I have to set a max limit and still unsure how to match with 50 different columns.

Can someone give me a little advice to the right direction?

use normaliztion for hobbies otherwise you cannot match them correctly — , Oct 23 '16 at 14:36
Storing multiple values in a comma-delimited field is not the SQL way of representing data. Use table with separate rows for each user and hobby combination. — Gordon Linoff, Oct 23 '16 at 14:37

JazZ · Accepted Answer · 2016-10-23T17:18:29.277

Here is how I would do this :

1) hobbies Table :

+----------+------------+
| id_hobby | hobby_name |
+----------+------------+
|        1 | Football   |
|        2 | Tennis     |
|        3 | Tennis     |
|        4 | T.V.       |
|        5 | Cars       |
|        6 | Swimming   |
|        7 | Coding     |
+----------+------------+

2) users Table :

+---------+-----------+
| id_user | user_name |
+---------+-----------+
|       1 | Bob       |
|       2 | John      |
|       3 | Arnold    |
|       4 | Mary      |
|       5 | Julia     |
|       6 | Amelia    |
+---------+-----------+

3) users_hobbies Table :

+---------+----------+
| id_user | id_hobby |
+---------+----------+
|       1 |        2 |
|       1 |        4 |
|       1 |        6 |
|       2 |        7 |
|       3 |        3 |
|       3 |        4 |
|       3 |        1 |
|       3 |        7 |
|       4 |        5 |
|       5 |        6 |
|       5 |        7 |
|       5 |        2 |
|       6 |        3 |
|       6 |        5 |
|       6 |        7 |
+---------+----------+

And then Go through this with php :

// init DB connection
$db = new PDO('mysql:host=localhost;dbname=testing', $db_user, $db_passwd);

// build data arrays
try {
    foreach ($db->query('
        SELECT h.hobby_name, u.user_name FROM hobbies as h
        LEFT JOIN users as u
            INNER JOIN users_hobbies as uh
            ON u.id_user = uh.id_user
        ON h.id_hobby = uh.id_hobby
    ') as $row) {
        $users_hobbies[] = $row;
        $hobbies[] = $row['hobby_name'];
    }
} catch (PDOExeption $e) {
    print "Error! : " . $e->getMessage() . "<br />";
    die();
}

// drop duplicates from $hobbies array
$hobbies = array_unique($hobbies);

// loop through new hobbies array and get users
foreach ($hobbies as $hobby) {
    echo "<h2>" . $hobby . "</h2>";
    foreach ($users_hobbies as $row) {
        if ($row['hobby_name'] == $hobby) {
            echo "<p>" . $row['user_name'] . "</p>";
        }
    }
}

Output :

Football
Arnold
Tennis
Bob
Julia
Arnold
Amelia
T.V.
Bob
Arnold
Cars
Mary
Amelia
Swimming
Bob
Julia
Coding
John
Arnold
Julia
Amelia

Hope it helps.

Thank you! This gives me the right hint, now I know what to do next. — user3813280, Oct 23 '16 at 17:49
Keep in mind this is a naive implementation of what you need. You'd better read threads that your question is duplicated. — JazZ, Oct 23 '16 at 19:28

efficient way to match users common data in the database?

1 Answers1

Football

Tennis

T.V.

Cars

Swimming

Coding