Cross referencing array values stored in MySQL using PHP

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
smokejaguar
Forum Newbie
Posts: 1
Joined: Thu Jan 26, 2012 7:30 pm

Cross referencing array values stored in MySQL using PHP

Post by smokejaguar »

Hey guys,

I have to say, up until this point I've managed to find answers to all my questions concerning PHP online. However, this is really bugging me.

Basically, I have two fields in MySQL: "skills" and "wants", which a user can submit information into using an array (up to ten fields per array).

I want the users to be matched by cross referencing their "skills" and "wants". So, for example, one user's "wants" may be "English, Portuguese, Spanish" and another user's "skills" may be "Japanese, Portuguese, Turkish". I want the second user to be selected in a MySQL query because one of his "skills" matches with the first user's "wants".

If such a MySQL query did exist, It'd be something like this:

$wants = "English Portuguese Spanish"; //an imploded array
SELECT * FROM users WHERE skills CONTAINS (any piece of information from) '$wants'; //"skills" here is also an array

I am relatively new to PHP, but this has seriously just left my mind = blown. :banghead: If anyone could help I'd be extremely appreciative!
User avatar
php3ch0
Forum Contributor
Posts: 212
Joined: Sun Nov 13, 2005 7:35 am
Location: Folkestone, Kent, UK

Re: Cross referencing array values stored in MySQL using PHP

Post by php3ch0 »

I think I understand what you are after??? What about this?


SELECT * FROM users WHERE skills IN ('English', 'Portuguese', 'Spanish') LIMIT 10;
Robertology
Forum Newbie
Posts: 3
Joined: Fri Jan 27, 2012 11:31 am

Re: Cross referencing array values stored in MySQL using PHP

Post by Robertology »

The problem is your wants and skills fields are doing too much. It can work that way but might be better having another table.
user_id, value, type
Where type would be either "want" or "skill".


But the way you have it, you'd have to do something like

Code: Select all

$list = array();
foreach( $wants as $want ) {
  $want = mysql_real_escape_string( $want ); // or whatever you need here
  $list[] = "skills LIKE '%{$want}%'";
}

$where = implode( ' OR ', $list );
It's not perfect. For example, "ball" will match "basketball" and "ballet" but that might help get you started.
Post Reply