using SUBSTING() in WHERE CLAUSE

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
kendall
Forum Regular
Posts: 852
Joined: Tue Jul 30, 2002 10:21 am
Location: Trinidad, West Indies
Contact:

using SUBSTING() in WHERE CLAUSE

Post by kendall »

Hello,

I have a table

create table individuals values (id, fname, mname, lname, born, died)

i have a form that has input fields name, year, month , day with a boolean AND/ OR option.

the theory...to be able to query the data by fname or mname or lname or year or month or day

now for the nam part i have opted to use a FULLTEXT Index search

but can i do a SUBSTRING(Date,exp ) in a WHERE CLAUSE?

what other otpions i have?

Kendall
User avatar
Crashin
Forum Contributor
Posts: 223
Joined: Mon May 06, 2002 3:42 pm
Location: Colorado

Post by Crashin »

Try using this instead:

Code: Select all

EXTRACT(interval FROM datetime)
Your intervals can be any of those listed on this page relative to the DATE_ADD function:

http://www.mysql.com/doc/en/Date_and_ti ... tions.html

For example:

Code: Select all

... WHERE EXTRACT(MONTH FROM 'born') = $bornMonth
8)
User avatar
kendall
Forum Regular
Posts: 852
Joined: Tue Jul 30, 2002 10:21 am
Location: Trinidad, West Indies
Contact:

using SUBSTING() in WHERE CLAUSE

Post by kendall »

Crashin,

Yo man...I can't get it to work...are you sure this can be used there?...I'm scouring the mysql documentation but i the def. are really vague(i find there search method really unreliable)

Kendall
User avatar
kendall
Forum Regular
Posts: 852
Joined: Tue Jul 30, 2002 10:21 am
Location: Trinidad, West Indies
Contact:

Post by kendall »

ok,

i see my mistake

I quoted the column

thus it should be WHERE EXTRACT(YEAR FROM Born) = $year

thanks
User avatar
Crashin
Forum Contributor
Posts: 223
Joined: Mon May 06, 2002 3:42 pm
Location: Colorado

Post by Crashin »

Cool! I'm glad it worked for you! 8)
Post Reply