Hi Jonathan,
Haven't used MySql for ages. More used to Oracle and PostgreSql.
Your table nmc_cd contains foreign keys pointing to nmc_category and nmc_publisher. Create a view containing all the data from those three tables using implicit joins as the data in table nmc_cd should NOT contain any "dangling" foreign keys. (You can always write a test routine to verify the cleaniless of your data).
Thus you have a single query "SELECT * FROM my_nmc_view WHERE ($x IS NOT NULL AND $x = some colum name in the view) AND ($y IS NOT NULL . . . etc.
You can append an ORDER BY clause at the end reflecting the desired display sequence.
If all of your search criteria is null, then the select should return every row in the view.
I don't understand code such as " if($searchCDID=1){ ". Comparing for equality requires "==" or "===". How does this work if the user enters "345" for example?
Apropos other comments, this select statement ought to be in the controller (or action controller) depending on how your application is structured.
Hope this helps.
Cheers,
Rob
