[Resolved] Select data from field where field is unknown

dougmcc1

How do I select a piece of data from my database if I don't know what field it's in? I know it's in 1 of 5 fields and I know exactly what piece of information I'm looking for, but I don't know which of the 5 fields it's located in.

Here is my scenario:

$contents="data";
mysql_query("SELECT (the correct field) FROM [table] WHERE (the correct field)='$contents'");

Thanks.

LordShryku

Ok, this should never happen.

Never.

But....

I would use mysql_list_fields, and loop through them to query against the database.

dougmcc1

So what am I doing wrong that is putting me in this situation that I shouldnt be in? What's a work around? I think I have a legitimate situation here but maybe I don't, in which case I'd be grateful for more information.

Thanks again LordShryku.

LordShryku

Well....why wouldn't you know what field this would be in?

If this is something that's happening, I just don't understand why. Maybe it's my incompetance. In which case, I think I would do like I said before, and loop through the fields to see what outputs.

dougmcc1

I don't know, you're probably right. I probably have redundant data in my database. Let me give you some more background info so you can see what's going on.

I'm working on a directory which of course has categories and subcategories. These are stored in a table not called category_list (ok it's called category_list)...
It contains these fields:
catID section category subcat1 subcat2... subcat5 page_name

There are 3 sections and each section has different categories and each category has different subcategories. So when I delete a subcategory (along with the subdirectory folder for that subcategory) I have to first know what subcategory I'm deleting. So I put it into a variable:

$desiredsubcat=(name of subcat to delete);
$selectsubcattodelete=mysql_query("SELECT (which subcat?) FROM category_list WHERE (which subcat?)='$desiredsubcat'");

$desiredsubcat could be subcat1, subcat2, subcat3... so hence my problem.

LordShryku

Ok, I understand. I probably would have set it up so it only had one subcatagory field, and inserted 5 seperate records, maybe add a rank field or something, so you can specify which subcategory it was. Anyway, try something like this

SELECT *
FROM   category_list 
WHERE  (subcat1 = 'value'
        OR subcat2 = 'value'
        OR subcat3 = 'value'
        OR subcat4 = 'value'
        OR subcat5 = 'value')

Then with your resultset, you can figure out which subcategory it was in....

dougmcc1

A rank field. Why didn't I think of that?? (let's keep that question rhetorical 🙂)

LordShryku

I won't say anything 😃

dougmcc1

hehe