Say I have these tables in MySQL
table actors
id int 11 primary key auto_increment
name varchar 30
sex varchar 10
table movies
id int 11 primary key auto_increment
name varchar 30
type varchar 30
table actors_movies
actor_id int 11
movie_id int 11
And assume I have a form in HTML that allows users to search for actors by parameters they define.
For example, a user might search just for actors with the name "John", or they may search for actors who are male and have acted in a "Drama"-type movie, or they may search for actors in the movie with the name "Batman" who are female, or they may search for actors who are in a "Drama" movie AND/OR (they choose which) a "Thriller" movie.
My solution is a very clunky PHP script that builds a prepared statement piece by piece to query the database with this depending on what parameters it is given.
My solution works, but is this the only possible way? I find my solution to be really clunky and as I add more possible parameters it just gets worse.
because I was asked for an example of what I'm doing
here's some pseudocode for what my script basically does
query = "Select * from actors
left join actors_movies on actors.id = actors_movies.actor_id
left join movies on movies.id = actors_movies.movie_id"
Then for each possible parameter it does something like this
if(actors.sex given)
query append("where actors.sex ="
for each sex given
query append sex and either AND/OR
hopefully that makes sense