Matching Text In A MySQL Database

vizskywalker

Jun 1 2005, 04:29 PM

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:

CODE

create database mousling_users

create 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

CODE

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

Reply

vhortex

Jun 2 2005, 12:36 AM

CODE

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

CODE

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..

CODE

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..

CODE

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

returns all usernames that starts with the word user

CODE

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

returns all usernames that ends with the word user

CODE

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

returns all usernames that haev the word user in the username

Reply

vizskywalker

Jun 2 2005, 02:55 AM

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

Reply

vhortex

Jun 7 2005, 01:42 AM

QUOTE(vizskywalker @ Jun 2 2005, 10:55 AM)

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

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.

Reply

ADD REPLY / Got an Opinion!	a humble request :-)	RAPID SEARCH!	Free Hosting	[X]
Express your Opinions, Thoughts or Contribute your information that might help someone here. Ask your Doubts & Queries to get answers.. "Together, We enlight each other!"	Register FREE for AD-FREE forum, Create your own topics, Ask Questions, track topics, setup subscriptions & notifications and Get a Free Website w/ Email and FTP.		500MB Space No Ads, CPanel, FTP, PHP, MySQL, EMails - 100% FREE