Stuck on writing a mySQL query

Forum Moderators: open

Message Too Old, No Replies

Stuck on writing a mySQL query

cookie2

5:07 pm on Jul 15, 2006 (gmt 0)

If I have a table (foo) that has about a dozen fields and I want to select everything, I know I can write the query as "SELECT * FROM `foo`" and I will get all the results. Works fine.

But what do I do if I want to get all the results in all fields but only from ones that contain something in the field named `week`.

I tried several things including "SELECT * FROM foo WHERE field LIKE `week` ORDER BY `week` DESC" but they all don't work right. What am I missing here?

By the way, the something in 'week' will be an array of numbers if that matters.

Thanks.

coopster

5:25 pm on Jul 15, 2006 (gmt 0)

The LIKE predicate allows you to use wildcard characters in the pattern. Without a wildcard character you are going to be looking for exact matches.

% = Matches any number of characters, even zero characters 
_ = Matches exactly one character

Try:

SELECT * FROM foo WHERE field LIKE '%week%' ORDER BY field DESC"

physics

11:19 pm on Jul 15, 2006 (gmt 0)

I'm not sure I understand your question. Do you mean you have a field in the db named week? If so you would do something like:
"SELECT * FROM db WHERE week IS NOT NULL ORDER BY week DESC"

cookie2

7:00 pm on Jul 16, 2006 (gmt 0)

Sorry if I wasn't more clear. Yes, there is a field named week. You understood what I wanted and your code came close to the actual working code that I worked out with help from a friend:

" SELECT * FROM foo WHERE NOT ISNULL(week) AND week<>'' "

He told me that the [ AND week<>'' ] part was basically another way of saying week does not equal empty which I didn't previously know. So this will find any entries in the week field that are not empty. Week is a varchar field with NULL as the default. Whatever the explanation, the statement works. :)

Thank you both for your help.