0

I have a set of names of schools stored in each row in my schools table. the same table has four different category columns which store the names of the subjects they offer. I want to enable functionality where the user conducts a search on the table to find out all the names of the schools which teach a particular subject (for eg: physics). The name of the particular subject could be stored in any of the four subject columns. Please suggest a mysqli_/mysql_ query to conduct such a search or if you may so please, suggest a PHP script that would achieve the above functionality.

1
  • 3
    Don't splay an array of subjects across columns; create another table. – Rick James Apr 3 '16 at 5:34
1

You could write a simple query using OR in WHERE clause and passing your search string.

SELECT school_name
FROM schools
WHERE Subject1 = 'Physics'
OR Subject2 = 'Physics'
OR Subject3 = 'Physics'
OR Subject4 = 'Physics'

You can parameterize the search string and pass on to this query.

0

You could concatenate the columns and use "like":

SELECT school_name
FROM schools
WHERE Subject1||Subject2||Subject3 like '%Physics%'
-1
SELECT school_name
FROM schools
WHERE 'Physics' IN (Subject1, Subject2, Subject3, Subject4);
1
  • Why the downvotes? It may not be the most efficient approach, but it's valid regardless. Better than concatenation offered by another poster. – mustaccio Feb 7 '17 at 17:59

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.