Jump to content
xisto Community
vizskywalker

Matching Text In A MySQL Database

Recommended Posts

I have a database that I am using to keep track of the members to my website. THe statements I used to create the database are as follows:

create database mousling_userscreate table members(memberID int default \'0\' not null auto_increment, username char(20), password char(20), firstname char(20), lastname char(30), email char(50), privelege char(30), banned bool, primary key (memberID), unique id (memberID))alter table members add fulltext (username)
The reason there are no prompt symbols or anything like that is I used a php page and mysql_query() to send these queries.

The problem comes in when trying to match records from the database with a string such as to check if a username already exists. Currently I have one record with a username of Vizsky, but when I send the query
select * from members where match (username) against ('Vizsky')
I get no records returned.

What am I doing wrong and how can I use queries to check when a username or password already exists?

~Viz

Share this post


Link to post
Share on other sites
select * from members where match (username) against ('Vizsky')

Hi vizskywalker,

your problem have so many work arounds.. dont be tie up with one single solution that is a big nono for programmers like us..

solution number 1 goes like this

select * from members where `username` = 'Vizsky'

what this piece of code do is to search the database for every records that have a username of Vizsky and return all column data that goes with it.

you already know the username so you can be sure that you can feed the query with the right username value..

now assuming that you only have a partial string for the username or you want all data that match the given username to be check..

example...

you have usernames of

user1
user2
user2
kim
foo
kee_user

and you want to find an excat match for user kee_user..

select * from members where `username` like  "kee_user"

this will return an exact match but we can extend that if you only have a portion of the username..

select * from members where `username` like  "user%"

returns all usernames that starts with the word user

select * from members where `username` like  "%user"

returns all usernames that ends with the word user

select * from members where `username` like  "%user%"

returns all usernames that haev the word user in the username

Share this post


Link to post
Share on other sites

Thank you, that worked. I am new to MySQL, and I was perusing the reference manual and the match thing appeared to be the solution. Just out of curiosity, however, do you have any idea why that didn't work? Also, if I use the `username` = 'Vizsky' thing, do I need to have username be a fulltext index?~Viz

Share this post


Link to post
Share on other sites

Thank you, that worked.  I am new to MySQL, and I was perusing the reference manual and the match thing appeared to be the solution.  Just out of curiosity, however, do you have any idea why that didn't work?  Also, if I use the `username` = 'Vizsky' thing, do I need to have username be a fulltext index?

 

~Viz

<{POST_SNAPBACK}>


no idea why it did not work..i dont use that anyway. well on my version of mySQL, the field do not need to be a full text.

 

in fact, i dont have any full text in my database. by the way my mySQL is version 3.x up.. never used a version lower than that.

 

current i am running 3.x, 4.x and 5 beta.

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

×
×
  • Create New...

Important Information

Terms of Use | Privacy Policy | Guidelines | We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.