CODEWITHSUNDEEP
phpmysqlajax
user4786688
user4786688

Reputation:

Ajax Search Return Problems

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...

Upvotes: 0

Views: 36

Answers (2)

user5105916
user5105916

Reputation:

Concatenate both the Subject and Number and apply LIKE this way:

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

Upvotes: 1

Nijraj Gelani
Nijraj Gelani

Reputation: 1466

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%');

Upvotes: 0

Related Questions