using a asterisk in a mysql query

[davidz]

I have a search page that queries a database and my users are used to using "*" (asterisks) for wildcard searching.  Mysql on the other hand uses "%" percent signs.

 

Is there a simpler way besides looking through their search item and replacing any * with %?  Or is this the recommended method?

 

 

Thanks!

[MadTechie]

You lost me..  ???

[effigy]

Would FULLTEXT work, or do you require wildcards?

[davidz]

Sorry if I was too vague.

 

For example, you type bob* into a search box expecting to find results like bob, bobby, bobbie, bobble, etc.

 

But mysql does not use *'s they use %'s so you query should look like

 

SELECT * FROM WordList WHERE username like 'bob%';

 

So I am wondering if there is a simple way to do this.  Or if doing a str_replace is the best method.

 

 

Thanks!

[MadTechie]

is most cases i filter out the * and when using like i use the following:~

 

"SELECT * FROM WordList WHERE username like '%".$search."%';"

 

 

[davidz]

 

Would FULLTEXT work, or do you require wildcards?

 

I need wildcards. 

 

 

is most cases i filter out the * and when using like i use the following:~

 

"SELECT * FROM WordList WHERE username like '%".$search."%';"

 

 

This is how I used to have it, but the queries were too slow with the leading %.  The table has over almost half a million records, so I don't want any wildcards unless the user puts them in explicitly (this way they expect it to be slower).  But if I know what I'm looking for and type it in exactly it queries instantly without the wildcards. 

 

Thanks for the all the input!

 

[Azu]

If I were you I'd either use str_replace to just escape the %s and then replace * with %

 

OR have it search for an exact match, and then search for it with wildcards only if it doesn't find it.