Product search By Title and By Manufacture is different from Product search by Categories; because I assume that you would have a list of categories which will be populated from the database (of course not to mention that each category would have an ID associated with it) and Title and Manufacturer can be either the same as follows or be a free text field (like text boxes) to search.
Assuming categories is a list, Title and Manufacturer are free text field, you can do the following
Front End:
Select a category from the list
Type title
Type Manufacturer
Back End
The query would be something like this;
select *
from dbo.Products
where categoryID = coalesce((ID from the front end), categoryID)
and title like '%' + @title + '%'
and manufacturer like '%' + @manufac + '%'
coalesce keyword is used if the category id is not supplied, it still returns all relevant results.
Hope this helps
Happy Coding :)