searching mysql

[Anonymous]

Hello guys,

I have a script that searches MYsql for a name in the player field. It looks like this:

$result = mysql_query("SELECT * FROM $dbtable WHERE player LIKE \"%$variable%\" ORDER BY $sortby $desc" ,$db);

Now $variable is a three letter tag that is at the beginning of the name in player field. This part works fine.

I was wonder how I would go about searching for $variable (3 letter tag) at the end of the players name in player field.

thanks

 

[Anonymous]

LIKE \"%$variable%\"

That statement you are using should find it no matter where it is in the name. Using the like with a % before and after the search string should find it at the beginning, ending, and all between. At least it is supposed to :]

try both the above and this to make sure they both return data where it ends with $variable.
LIKE \"%$variable\"

 

[Anonymous]

I created 3 names in the database xxx_test1, test2_xxx, te_xxx_st3 and here is what I get:

with %Variable% I only see xxx_test1
with %Variable I see nothing
with Variable% I see xxx_test1

this i very wierd. what am I missing here

 

[Anonymous]

when you run the script, try printing out what is in $variable to actually see what it is matching on. What is in variable? is it just "xxx"?

I just wondered this incase it is putting in an erronious spaces or anything else like that. If you have MySql Control Center or myphpadmin, try executing the same query within the SQL section of those applications and see what you get back.

I tried the following with MySql:
SELECT *
FROM `user`
WHERE name LIKE "%Doe%";

and it seems to return all rows no matter where they have Doe in their name. I'm not sure why it is not doing the same with yours.

Perhaps try using a single quote to enclose your variable:
$result = mysql_query("SELECT * FROM $dbtable WHERE player LIKE '%$variable%' ORDER BY $sortby $desc" ,$db);

 

[Anonymous]

ah..now we're getting some where... the $Variable is simply some text like "xxx"... I performed the test u suggested and it DOES work as you say.. so, I must be doing something in my script to counter act this...

I will do some digging and if I cant find the problem then I'll be back... you have been a BIG help..thank you so much

 

[Anonymous]

Muhahaha... I had a bracket in the wrong place... LOL.. thanks for the lesson....

 

[Anonymous]

What if i have a data like James C. Connor


and i just type in

James Connor what sql query do i use?
How bout James Conner? what sql query do i use also?



thanks!

 

[Anonymous]

What if i have a data like James C. Connor

and i just type in

James Connor what sql query do i use?
How bout James Conner? what sql query do i use also?

Well, this is getting more complicated. For "James Connor", you could match on "James%Connor", though it would probably be in your best interest to split it up in to first and last name fields. For "Conner" you're getting into spell checking and soundex stuff, which is pretty complicated, definitely not for people new to SQL.