mysql query to match multiple words and display result using php

Question

I am trying to pull data using an mysql query that requires that I look for keywords within a field in the database.

A user in my database may have the following in their 'subjects' field:

'math | science | reading | writing'

This means that user accepts all of those subjects. This table is called 'user_teacher'

I have another table for students (called 'student_requests') that consists of requests from students for say, science, or math, or maybe even two subjects (so the entry in this case would look like 'science | math'). This table stores those values also in a field called 'subjects'.

My question is, how would I form a query where i am looking up entries in 'student_requests' where the subject matches any of the listed subjects within a particular user's profile from the table 'user_teacher'? Let's assume that i originally look up the teacher's subject field using something like this:

$query = "SELECT * FROM user_teacher WHERE username = '$username' LIMIT 1 ";
$result = mysql_query($query);
while ($row = mysql_fetch_object($result)) {
    $teacher_subjects=$row->subject;
}

In other words, if i am a teacher who offers reading and math, i want to see all entries in the 'student_requests' table where the subject field has 'math' or 'reading' in it.

Any assistance on this would be greatly appreciated. Also, if you think I am approaching this problem incorrectly based on how i have set up the tables, please let me know. My goal here is to have the queries run as quickly as possible.

Thanks!

khael · Accepted Answer · 2012-01-20 18:01:42Z

1

You could try not using that kind of construction for you tables, instead make another table subjects (id, name) and other two tables students_requests (id, id_student, id_subject) [here you can record multiple requests for one student], and teach_subjects (id, id_teach, suject_id) [the same, multiple subject for a teacher] and yhen you can make a join, or even a plain php check between tables, based on what you received.

EDIT 1:

SELECT * FROM `students` WHERE `id` IN (SELECT `subject_id` FROM `students_request` INTERSECT SELECT `subject_id` FROM `teach_subject`);

And please change id_subject from students_requests in subject_id.

edited Jan 20, 2012 at 18:01

answered Jan 20, 2012 at 16:34

khael

2,6101 gold badge18 silver badges36 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

rob melino Over a year ago

will this method be faster performance wise than the solution proposed below by Dor Shemer?

khael Over a year ago

@robmelino Yes, my version is the normalization of your system. check wikipedia for 3 normal form.

rob melino Over a year ago

assuming i had created the tables as you describe, could you include in your answer an example of what the join mysql query would look like? Or alternatively the php check you suggested? thanks so much for your help!

dorsh · Accepted Answer · 2012-01-20 16:35:35Z

1

Instead of defining subjects as a text field, try using set. Your definition should be along the lines of:

subjects SET('math', 'science', 'reading', 'writing')

Then you can search those fields in a more convenient way, for example:

SELECT * FROM student_requests s join user_teacher t ON (FIND_IN_SET(s.subject, t.subjects)>0) WHERE t.username = '$username';

That should give you all the students that requested subjects this teacher accepts.

Read more about sets here: http://dev.mysql.com/doc/refman/5.0/en/set.html

answered Jan 20, 2012 at 16:35

dorsh

24.9k2 gold badges29 silver badges30 bronze badges

Collectives™ on Stack Overflow

mysql query to match multiple words and display result using php

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related