How to query a table with different values on the same fields?

I’m currently having some trouble with a search query I’m doing in MySQL.

I have a table that stores form data for different fields of a course, with two foreign keys (courseid and fieldid). There is a data field that stores the form data of the field for the course. The table is structured as such:

id | courseid | fieldid | data

I’m doing an advanced search for courses where a user can specify the fields they want to filter. A few of these fields query this one table. Note: I cannot use courseid as I’m joining this table with another table. The other table is the course table itself and I search for the name of the course there.

I have a query like this:

SELECT DISTINCT c.name FROM `course_table` c
INNER JOIN `form_table` f ON c.id = f.courseid
WHERE (c.name LIKE '%hah%')
AND (f.fieldid = 2 AND f.data BETWEEN 1371160800 AND 1529000797) 
AND (f.fieldid = 8 AND f.data LIKE '%foo%') 
AND (f.fieldid = 4 AND f.data LIKE 'bar%') 
AND (f.fieldid = 1 AND f.data LIKE '%gah%')
ORDER BY c.name

The first clause is a date comparison, and the rest are simply string searches.

This will return nothing as there are too many ANDs. I tried with ORs, but I essentially get every course instead of the one I want (the combined data is supposed to be specific to a single course).

I have looked through other solutions, some suggesting to use the IN clause (i.e. fieldid in (2, 8, 4, 1)) combined with GROUP BY (on the courseid) and HAVING, but that also did not help in my case.

It is the common EAV.

I have looked through other solutions, some suggesting to use the IN clause (i.e. fieldid in (2, 8, 4, 1)) combined with GROUP BY (on the courseid) and HAVING, but that also did not help in my case.

It is correct method. But you misused it. You must check not fieldid, but (fieldid, f.data) pair:

SELECT c.name 
FROM `course_table` c
INNER JOIN `form_table` f ON c.id = f.courseid
WHERE (c.name LIKE '%hah%')
AND (    (f.fieldid = 2 AND f.data BETWEEN 1371160800 AND 1529000797) 
      OR (f.fieldid = 8 AND f.data LIKE '%foo%') 
      OR (f.fieldid = 4 AND f.data LIKE 'bar%') 
      OR (f.fieldid = 1 AND f.data LIKE '%gah%')
    )
GROUP BY c.name
HAVING COUNT( /* DISTINCT */ f.fieldid) = 4