CODEWITHSUNDEEP
phpmysqlsqlarrays
Keno
Keno

Reputation: 21

How can i run a query on a mysql table using php arrays as values

I have a mysql table that contains records of user's interest. e.g

id  | username |    email     | interest
-------------------------------------------------------------------
2   | lynx     | [email protected] | sports, business, entertainment

With the example above, assuming I'm searching for records of user's with interest in entertainment and the values used to query the database contains array values. eg

array('movies', 'games', 'entertainment');

With that array, i should be able to get lynx records because they are interested in entertainment. I've tried using the IN Operator for MySQL but didn't get any result. I also converted the field to fulltext just to use the LIKE operator but didn't get any results too.

Upvotes: 2

Views: 157

Answers (1)

OMG Ponies
OMG Ponies

Reputation: 332781

Short Term Solution

The interest data is denormalized, so you're looking at using the FIND_IN_SET operator to search it:

WHERE FIND_IN_SET(@interest, t.interest) > 0

@interest represents a single value from the array you posted. IE: "entertainment"

Long Term Solution

The tables need to be restructured to be normalized data - that means moving the interests out of the USERS table using:

USER_INTERESTS

  • user_id (pk, fk to USERS.user_id)
  • interest_id (pk, fk to INTERESTS.interest_id)

INTERESTS

  • interest_id (primary key)
  • description

Here's the query you'd use in that situation:

SELECT u.*
  FROM USERS u
  JOIN USER_INTERESTS ui ON ui.user_id = u.user_id
  JOIN INTERESTS i ON i.interest_id = ui.interest_id
 WHERE i.description = @interest

To search based on multiple interests:

SELECT u.*
  FROM USERS u
  JOIN USER_INTERESTS ui ON ui.user_id = u.user_id
  JOIN INTERESTS i ON i.interest_id = ui.interest_id
 WHERE i.description IN (@interest1, @interest2, @interest3)

Upvotes: 1

Related Questions