Sign in to follow this  
Followers 0
Craig Hopson

SELECT LIKE search term

5 posts in this topic

hi guys i am having trouble with the syntax of a query here is what i have not shore where to go from here


select * from `jcow_accounts` WHERE 1 AND (username LIKE craig% OR email LIKE craig% OR fullname LIKE craig%) AND birthyear>1953 AND birthyear<1995 AND gender=0 and !hide_me order by lastlogin DESC LIMIT 20
[/CODE]

Thanks

1 person likes this

Share this post


Link to post
Share on other sites

SQL? What's it doing wrong? Are you getting an unexpected result set? An error? It's not returning anything?

Share this post


Link to post
Share on other sites

your like clauses are strings and need encapsulated in single quotes also.... LIKE '%craig' LIKE '%craig%' LIKE 'Craig%' etc

also what SQL variant? MS SQL, Oracle and MySQL all have differences even though they are close to the SQL standard

on SQL Server I'd write that like this

SELECT TOP 20 *

FROM [JCOW_ACCOUNTS]

WHERE ([uSERNAME] LIKE 'craig%' OR LIKE 'craig%' OR [FullName] LIKE 'craig%')

AND [birthyear] between 1953 AND 1995

AND [Gender] = 0

and HIDE_ME <> 1

ORDER BY [LastLogin] DESC

1 person likes this

Share this post


Link to post
Share on other sites

First,


1
[/CODE]

in the WHERE clause is not a propper boolean expression. What did you want to say with it?

[CODE]
1=1
[/CODE]

will give you ever row.

[CODE]
1<>1
[/CODE]

will give you none.

Also, you have to enclose your strings in single quotes:

[CODE]
username LIKE 'craig%' OR email LIKE 'craig%' OR fullname LIKE 'craig%'
[/CODE]

1 person likes this

Share this post


Link to post
Share on other sites

your like clauses are strings and need encapsulated in single quotes also.... LIKE '%craig' LIKE '%craig%' LIKE 'Craig%' etc

Worked like a charm thanks so much, couldn't see for looking lol

Thanks all

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!


Register a new account

Sign in

Already have an account? Sign in here.


Sign In Now
Sign in to follow this  
Followers 0

  • Recently Browsing   0 members

    No registered users viewing this page.