Searching database fields

[codenature]

Hi, Is it possible to have a user put in different hobbies in one text field , and have those words be entered into  the database, into one field in the database as a searchable list? Meaning, can you have a list of words in one database field, and then run a script that compares the hobbies list with other peoples' hobbies list? Thanks in advance for any ideas.

[smoseley]

Not in one field, but you can split the input value into multiple rows in a relational table, e.g:

 

CREATE TABLE users {
    id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(255) NOT NULL
} ENGINE=InnoDB;


CREATE TABLE user_interests {
    id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    user_id INT UNSIGNED NOT NULL REFERENCES users(id),
    interest VARCHAR(255) NOT NULL
} ENGINE=InnoDB;

 

Then in your php:

 

$user_id = 123; // Logged in user
$interests = preg_split("/[^A-Za-z0-9\-]+/" $_POST["interests"]);
foreach ($interests as $interest) {
    if ($interest) {
        $sql = INSERT INTO user_interests (user_id, interest) VALUES ({$user_id}, '{$interest}');
        $result = mysql_query($sql);
    }
}

[codenature]

Wow Smoseley, thanks very much for that code! I really appreciate your time.  Do you know how I would search via a form for 3 matching interests that are in the database? Now that I put the interests in the database. I need to have a form where the user types in the desired matching interests, and results show up in order of how many matched per user in the database. I don't know if that is too complicated.

 

I have an idea of how I would do it if there were fields like "interest 1" "interest 2" in the database, but no idea how I would do it in a relational database. Thanks again for replying, I thought no one would. your reply was awesome so thank you.

 

(I just tried to solve it myself with googling "searching through a relational database" but I can't see any tutorials on it, just articles)

[smoseley]

Using similar code to above:

$interests = preg_split("/[^A-Za-z0-9\-]+/" $_POST["interests"]);
foreach ($interests as &$interest) {
    $interest = "'{$interest}'";
}
$interests = implode(",", $interests);
$sql = "SELECT u.*, GROUP_CONCAT(ui.interest) AS matching_interests 
        FROM users AS u 
        INNER JOIN user_interests AS ui ON ui.user_id = u.id
        WHERE ui.interest IN ({$interests})
        GROUP BY u.id;

[smoseley]

Note: mysql_real_escape_string isn't required here because you're parsing only alpha-numeric characters.

[smoseley]

By the way, you'll want to index your interest column:

 

ALTER TABLE user_interests ADD INDEX (interest);

[codenature]

Thank you!!! Wow. thank you so much for your time again. You really know what you are doing....I am not worthy.

 

There are some things I don't understand in your code. Here they are.

 

 

 

$sql = "SELECT u.*, GROUP_CONCAT(ui.interest) AS matching_interests  // I don't understand u.*, or ui.interest or where "matching_interest" comes from. Like where does the u and ui come from?

        FROM users AS u 
        INNER JOIN user_interests AS ui ON ui.user_id = u.id
        WHERE ui.interest IN ({$interests})
        GROUP BY u.id;
     
ALTER TABLE user_interests ADD INDEX (interest);// I don't know where to add this in the code. do I add this to the first code you replied with?

 

If you can help that would be great again. You are pretty advanced.

[smoseley]

u, ui, and matching_interests are aliases.  you can access matching_interests as if it's a column in your results.  The others are just table aliases for shorthand SQL.

 

The ALTER gets run one time, after the CREATE DDL, as you assumed.

[codenature]

Thank you. Is this correct below in long hand? Please.

 

$sql = "SELECT users.*, GROUP_CONCAT(user_interests.interest) AS matching_interests 
        FROM users AS users 
        INNER JOIN user_interests AS user_interests ON user_interests.user_id = users.id
        WHERE user_interests.interest IN ({$interests})
        GROUP BY users.id;

 

I also still don't know where

  "matching_interest" is created or used 

       

   

ALTER TABLE user_interests ADD INDEX (interest);

 

What is a CREATE DDL? please.

 

sorry for my newbiness. I do understand most of it though. thank you! Also, that concatenation is interesting. I don't think I have ever seen that used in SQL. I could be wrong though.

[codenature]

with the alter table, like this?

 

CREATE TABLE users {
    id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(255) NOT NULL
} ENGINE=InnoDB;


CREATE TABLE user_interests {
    id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    user_id INT UNSIGNED NOT NULL REFERENCES users(id),
    interest VARCHAR(255) NOT NULL



} ENGINE=InnoDB;

ALTER TABLE user_interests ADD INDEX (interest);

[smoseley]

Yeah, looks good... except if you're not aliasing a table, you don't need the "AS".

 

So "FROM users AS users" is the same as "FROM users"

[codenature]

cool thank you! I just have one last question. I don't know how to create the table

 

"matching_interests " in relation to the other tables.

 

It seems like it is by itself and not mentioned in the code which confuses me.

 

that is pretty much all I don't understand now.

 

Thank you so much!

 

 

[smoseley]

It's not a table.  It's an alias for the result of an aggregate function (group_concat).

 

It's not really necessary for you to do anything with that data. 

 

The concept is that you'll retrieve the matching users, and that field will return what interests they matched on, delimited by commas.  Just some ancillary information you might like.

[codenature]

Ok thanks again.  :D