Matching Text In A MySQL Database

[vizskywalker 0]

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

[vhortex 1]

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

[vizskywalker 0]

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

[vhortex 1]

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.