username comparison

[Anon]

i'm currently studying php, i would like to know how to insert NEW usernames to our database using php w/ html. ive done the script, but the problem is it doesnt detect if a "username already exists" so same usernames can be inserted in the database.
how do i do this? tnx!!!

[[deleted]]

"but the problem is it doesnt detect if a "username already exists" so same usernames can be inserted in the database"

you should not solve taht in PHP, but in your database.

Change the table structure to make the username table 'UNIQUE'.
Then when you try to insert a username that already exists, SQL will give you an error. Then you can check for that error in PHP, and print a message like 'Sorry, this name already exists'.

The other, very BAD way to do it is by first running a query that checks if the username already exists. That method is bad, becuase you have to run two queries. You check if the name exists, and then you insert. If two scripts do that at the same time, they will both see that the username is free, and they will both try to insert. One of them will succeed, the other will fail (because by that time the other query has already inserted the record.) and you just waste two queries.

[Anon]

its already UNIQUE, this is the syntax we used for the table:

CREATE TABLE profiles (
id smallint(4) NOT NULL auto_increment,
username varchar(50) NOT NULL default '',
password varchar(75) NOT NULL default '',
email varchar(75) NOT NULL default '',
homepage varchar(100) NOT NULL default '',
icq varchar(25) NOT NULL default '',
aim varchar(25) NOT NULL default '',
yahoo varchar(25) NOT NULL default '',
location varchar(25) NOT NULL default '',
note mediumtext NOT NULL,
showemail enum('1','0') NOT NULL default '1',
usergroup tinyint(3) NOT NULL default '0',
posts int(7) NOT NULL default '0',
joindate int(10) NOT NULL default '0',
timeoffset int(10) NOT NULL default '0',
timezone tinyint(4) NOT NULL default '0',
avatar varchar(50) NOT NULL default '',
custom varchar(50) NOT NULL default '',
homepagedesc varchar(100) NOT NULL default '',
occupation varchar(50) NOT NULL default '',
msn varchar(75) NOT NULL default '',
templategroup tinyint(4) NOT NULL default '0',
vargroup tinyint(4) NOT NULL default '0',
totalpm int(5) NOT NULL default '0',
lastpm int(10) NOT NULL default '0',
invisible enum('1','0') NOT NULL default '1',
activated enum('1','0') NOT NULL default '1',
banned enum('1','0') NOT NULL default '1',
sig mediumtext NOT NULL,
showavatar enum('1','0') NOT NULL default '1',
showsig enum('1','0') NOT NULL default '1',
showhistory enum('1','0') NOT NULL default '1',
addowntopics enum('1','0') NOT NULL default '1',
autosubscribe enum('1','0') NOT NULL default '1',
PRIMARY KEY (id),
UNIQUE KEY id (id)
) TYPE=MyISAM;

in php how do i print a
message like 'Sorry, this name already exists'." if the username really exists? pls help me, i'm only a beginner, TNXXX!!

[[deleted]]

"its already UNIQUE, this is the syntax we used for the table:"

"UNIQUE KEY id (id)"

nope, it's not unique. the ID field is unique (because it is the primary key)
you'll have to add a new 'UNIQUE KEY' for the username field too.

About printing the message, make a test-table, write a small simple script to insert a username, and do something like this:

if(!$result = mysql_query("INSERT INTO table (username) VALUES ('username')"))
{
echo 'insert failed:<BR>';
echo mysql_error().'<BR>'.mysql_errno().'<BR>';
}
else
{
echo 'user inserted';
};

mysql_errno() will return a code for the 'duplicate entry' error.

Whenever an insert returns that particular error code, it means you tried to insert a duplicate value, and you can use a simple IF to print the message.