CODEWITHSUNDEEP
phpmysqlsqldatabaseselect
user1282947
user1282947

Reputation: 83

Find match in database with php and mysql

So this is the problem for which I need solving: Users in my database are following a certain amount of courses. These courses are all numbered, so the value of the row courses could be 12, 34, 56 or 12 or '', etc. Now I'm doing a search query to find users that are following a certain course. I was thinking about using LIKE, but it seems like an impractical solution, because then I have to match it as follows:

courses LIKE '$course,%' OR courses LIKE '%,$course,%' OR courses LIKE '%,$course' OR courses LIKE '$course'

Since there are four different ways to match, I was hoping for a better solution, but haven't thought of anything yet myself. Anybody with a creative solution? Thanks a mil.

Upvotes: 0

Views: 2162

Answers (3)

Marcassin
Marcassin

Reputation: 1405

You can add another table course in your DB and one more to make the link between courses and users :

Users (id_user, name,  ...)
Courses (id_course, num_course, ...)

userHasCourse (id_user, id_course)

userHasCourse.id_user and userHasCourse.id_course are foreign key of id of Users and Courses table.

You new able to get back all courses info for each user

SELECT Users.name, Courses.num_course FROM Courses JOIN userHasCourse USING id_course JOIN Users USING id_user ORDER BY id_user

You will get array with all users and associated num_courses. This solution can be usefull if you want to add more information about courses (more than only one number)

Upvotes: 0

John Woo
John Woo

Reputation: 263933

MySQL has implemented a function called FIND_IN_SET() that searches a value from a comma separated value, eg

SELECT *
FROM   TableName
WHERE  FIND_IN_SET('12', 'comma separated value') > 0

Consider normalizing your tables. Storing comman separated value in a single column is such a bad idea.

Upvotes: 3

Cups
Cups

Reputation: 6896

try using

WHERE courses IN (12,34,56);

Upvotes: -1

Related Questions