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
