I need MySQL to search a specific column/field...

[ebm242]

How can I get MySQL to search a specific column/field in a a table? I have a small site that sells CDs. on every page is a text field and a drop down menu that allows users to search the site. but i want them to be able to choose a catagory from the drop down menu (which would be individual columns..) such as Artist, Album Title, Genre.. etc.. Here is my table..

CREATE TABLE tblProduct(
prodID int(11) not null auto_increment,
artist varchar(250),
albumTitle(250),
genre(100),
PRIMARY KEY (prodID)
);

What is the proper syntax (if any) in MySQL to do what I'm trying to do?

Thanks in Advance!

 

[r937]

you will want to use your scripting language's if/else capability in order to dynamically modify the sql statement

here it is in coldfusion

[tt]<cfquery name=&quot;productsearch&quot;>
select prodID
, artist
, albumTitle
, genre
from tblProduct
<cfif form.category eq &quot;Artist&quot;>
where artist like '%#form.textfield#%'
<cfelseif form.category eq &quot;Album Title&quot;>
where albumTitle like '%#form.textfield#%'
<cfelse>
where genre like '%#form.textfield#%'
</cfif>
</cfquery>[/tt]

rudy

http://rudy.ca/