How to set this full text index
I would like some guidance in how to set the full text index in phpMyAdmin like following:
Tables
universities:
id
name
programs:
id
name
university_id
courses:
id
name
program_id
students:
id
name
course_id
Search function User needs only to serach for students but I would like to create a full text of the students.name and the courses.name that match the students.course_id and so on so that the students name, course name (student participate in), program name (course is given in) and university name (program belongs to) is all searchable.
Query If I construct it as above will the full text be created so that it is searchable like standard, e.g. with PDO:
$STH = $DBH->prepare('SELECT * FROM students WHERE MATCH(fulltext) AGAINST开发者_Python百科(:query IN BOOLEAN MODE));
$STH->bindParam(':query', $query);
or how would the pdo query in look in php?
Also will this be a heavy search function?
Approx numbers: 5 universities, 100 programs 1 000 courses and 20 000 students.
That's not how FULLTEXT
indexes work. A FULLTEXT
index must be on a single textual (CHAR
/ VARCHAR
/ TEXT
) column in a MyISAM table, and allow searches to match words within the contents of that column. For instance, you might use a FULLTEXT index in a course catalog to allow users to search for text within the course descriptions.
In your case, what you probably want to do is to create one "normal" (non-fulltext) index on each of students.name
, courses.name
, programs.name
, and universities.name
, then perform one query for each of those tables and merge the results yourself. (You will probably have to display results for a matched student differently than a matched university, for instance.) It may help somewhat to break the existing student.name
field up into a separate first-name and last-name field, as that'll allow you to search those two separately.
In PHP
$query= "SELECT * FROM students,courses,programes,universities WHERE MATCH(students.name,courses.name,programs.name,universities.name) AGAINST('$keyword' IN BOOLEAN MODE)"
$result = mysql_query($query);
$keyword
is the word you want to search for.
EDIT
Based on the comment, Think this is what you need
SELECT name from student where name like %key% OR
course_id IN (SELECT id FROM course WHERE name like %key%) OR
course_id IN (SELECT id FROM course WHERE program_id IN (
SELECT id FROM program WHERE name Like %key%)) OR
course_id IN (SELECT id FROM course WHERE program_id IN (
SELECT id FROM program WHERE university_id in (
SELECT id from university where name like %$key%)))
Where key
is the word you are searching for.
精彩评论