Ajax搜索返回问题

Question

I'm trying to make AJAX search calls as the user types a course name...
So far, I have the following problem:

My database design is as follows:

TABLE COURSE (
  Subject VARCHAR,
  Number CHAR(6)
)

Courses such as MATH 101 and BIOL 301B are stored as:
Subject: MATH, Number: 101
Subject: BIOL, Number: 301B

Now the problem is in PHP, I'm querying the db as follows:
SELECT * FROM COURSE WHERE Subject LIKE :subject AND Number LIKE :number

If I use AND in the SELECT statement, then if the user hasn't yet typed the full course title then he would not get any result...
Example Input: math
In that case, I want all the MATH courses to show but since I'm using AND they won't...

Now if I use OR it's a different problem; Example Input: math 200
This will return any courses with Number 200 which is not right!

How do you suggest I resolve this issue?

One solution that I have in mind is to have different functions that handle the searches differently and then combine the results...

Answer 1

You can try concatenating the values of columns Subject and Number in the query and then apply LIKE clause on that concatenated string.

Like this :

SELECT * FROM `course` WHERE CONCAT(Subject, ' ', Number) LIKE ('%math%');