retrieving several values in a field

tigger

hi all
I have a fun question:
If I have a table in a database with a field called monthsCovered, and in each row, there are several values (ex: December, January, Febuary) and I have a field called startmonth (which contains only one month), I would like to perform a search to find the month of December in the startmonth field (that I know how) and in the monthsCovered field. Is this possible?

ex:
fields: startmonth ; monthsCovered
rows: December ; December, January
May ; April, May, June

Thanks
Tigger

Naug

you could try wildcard search :

select bla,bla,bla from database where monthsCovered like "December";

(diff databases have different wildcards? not sure)

Weedpacket

Originally posted by Naug
(diff databases have different wildcards? not sure)

SQL databases use %

Naug

MS Access 2000 uses *, but not %, postgresql vice virsa.

Weedpacket

Originally posted by Naug
MS Access 2000 uses *, but not %, postgresql vice virsa.

Yeah, but I said SQL databases - real database systems that recognise the SQL standard, in other words; not single-user desktop rolodexes that wouldn't last ten minutes if they experienced any real load.

tigger

hi guys, thanks for all ur replies.
I am using SQL by the way.
and I'm still having difficulty:
Here is some of the code:

"SELECT datePosted, jobRef, jobTitleGB
FROM o,m
WHERE o.startMonthGB = m.startMonthGB AND o.startMonthGB = '$when'
OR WHERE o.monthsCovered LIKE '%$when%'
ORDER BY 'datePosted'"

but it comes up with this:
Error performing query - no results found : You have an error in your SQL syntax near 'WHERE o.monthsCovered LIKE 'September' ORDER BY 'd' at line 4

I'm a bit stuck, and really would like it to be an AND instead of the OR, but it doesn't work either!

Help
Tigger

Naug

'WHERE o.monthsCovered LIKE 'September' ORDER BY 'd'

Is not in the code that you posted here, and switch from * to %

tigger

here.

Error performing query - no results found : You have an error in your SQL syntax near 'WHERE o.monthsCovered LIKE '%September%' ORDER BY 'd' at line 4

But the thing is $when contains the value selected from a dropdown menu on the previous page. So here, the person has selected September in the dropdown menu.

Thanks for the help
Tigger

Naug

srop the second where...

tigger

yeah I wound but I need it to list all the things that either start on the month selected (startMonthGB='$when') and all the things that run over that month (monthsCovered, which is basically a list of Months ex😃ecember, January,Febuary,etc.)
So I'm a bit forced to do this!
Tigger

tigger

hey guys
I really need help right now, pleeeaaase!!

I'm really stuck😕
tigger

Weedpacket

So what exactly is your query looking like, now?

Something like:

"SELECT
    datePosted, jobRef, jobTitleGB
FROM
    o,m
WHERE
    o.startMonthGB = m.startMonthGB
    AND (o.startMonthGB = '$when'
        OR o.monthsCovered LIKE '%$when%'
    )
ORDER BY
    datePosted"

But that's assuming that your tables really are called "o" and "m" ... which seem like funny names for tables.

How are "monthsCovered" related to "startMonth"?

tigger

Hi guys
Right further explanations:
The tables are called o and m, it was someone else that called them like that.
Basically startmonth is the list of all the months (jan through to Dec) right, and monthsCovered are the list of months that the job covers (if the job starts in January and goes on for 3 months, then monthsCovered will have "January, Febuary, March" as it's value).
so when someone does a search lets say for January, I want it to display all the jobs that start in january AND all the jobs that have january in the monthsCovered list (cos if the job is still in the database it means the place hasn't been filled, so u can still apply for it).
There u go, I think thats all!
but of course monthsCovered is a long string which contains the month in it, so I almost need to chop the monthsCovered up and retrieve the bit that is equal to the month selected, but I don't know how to do that!!
Thanks for all ur replies guys.
Tigger

tigger

HI
anybody understand what I put? can anyne help me?
Cheers
Tigger

Weedpacket

So what exactly is your query looking like now? Cut-and-paste it here, along with the error message.

tigger

code:

"SELECT datePosted, jobRef, jobTitleGB
FROM o,m
WHERE o.startMonthGB = m.startMonthGB AND o.startMonthGB = '$when'
OR WHERE o.monthsCovered LIKE %$when%
ORDER BY 'datePosted'"

And error message:

Error performing query - no results found : You have an error in your SQL syntax near 'WHERE o.monthsCovered LIKE %October% ORDER BY 'dateP' at line 4

Weedpacket

Well, like Naug said; drop the second WHERE...

Note in the query I wrote I also added a pair of (). This is because "a AND b OR c" is ambiguous - it could mean "(a AND b) OR c" or "a AND (b OR c)", and rather than hope that the DBMS guesses correctly, I prefer to make it explicit. As I showed, you'd want the latter.

tigger

would it be possible to like retrieve the String of monthsCovered, then split it to find the month wanted, and return that row?
If it is possible, any ideas how?
Thanks
Tigger

Weedpacket

Yes, but why bother if you've already got a working SQL query?
Translation: What are your comments on what people have been suggesting?

tigger

thank you for all ur replies, they have been very useful, as it has shown me and explained to me different things. But if I get rid of the WHERE, so I have:

"SELECT datePosted, jobRef, jobTitleGB
                           FROM o,m
                           WHERE o.startMonthGB = m.startMonthGB AND o.startMonthGB = '$when' 
                           OR o.monthsCovered LIKE %$when%
                           ORDER BY 'datePosted'"

it still comes up with the same error. And the Boss wants this to be implemented. Kind of stuck here!!

Thanks again for all ur help guys, it has been great, as I only have been working on php for 3 weeks now!

Tigger