user3813280 user3813280 - 1 month ago 13
SQL Question

efficient way to match users common data in the database?

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?

Answer

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.