msql - unique fields

Ye' old general discussion board. Basically, for everything that isn't covered elsewhere. Come here to shoot the breeze, shoot your mouth off, or whatever suits your fancy.
This forum is not for asking programming related questions.

Moderator: General Moderators

Post Reply
eliddell
Forum Newbie
Posts: 8
Joined: Thu Apr 22, 2010 12:31 pm

msql - unique fields

Post by eliddell »

hi all,
I will be honest i don't do much php so i am a bit rusty.. I am setting up a basic users database with mysyql.. there is a table called Users in the DB and three of the fields are unique: iD, userName, and email. setting these up in mySQL to be unique prevents duplicate users with the same username and/or email adress.. great.. and when i test my php, it doesn't write to the DB if one of these is not unique.. OK? AWESOME! but its also not throwing an error.. I'd like it to spit back which required field was not unique, so i can prompt the user to use a different email address or username..

Code: Select all

// add user to database
mysql_connect($DBhost,$DBuser,$DBpass) or die("Unable to connect to database");

@mysql_select_db("$DBName") or die("Unable to select
database $DBName");

$sqlquery = "INSERT INTO $table(userName,password,email)
VALUES('$userName', '$password', '$email')";

mysql_query($sqlquery);

mysql_close();
User avatar
Jonah Bron
DevNet Master
Posts: 2764
Joined: Thu Mar 15, 2007 6:28 pm
Location: Redding, California

Re: msql - unique fields

Post by Jonah Bron »

What does mysql_error() give you? To figure out exactly which field it is, you may have to query first.

Code: Select all

$result = mysql_query("SELECT userName, email FROM $table WHERE userName = '$userName' OR email = '$email'", $connection);
if ($row = mysql_fetch_assoc($result)) {
    if ($row['userName'] == $userName) {
        // username already taken
    } elseif ($row['email'] == $email) {
        // email already taken
    }
}
User avatar
Bill H
DevNet Resident
Posts: 1136
Joined: Sat Jun 01, 2002 10:16 am
Location: San Diego CA
Contact:

Re: msql - unique fields

Post by Bill H »

You are using the mysql_query($sqlquery); function without testing the value returned. You should use an if..else.. structure, because the function will return TRUE if it executes successfully, and FALSE if it does not. In the else portion you can present a message saying that the email or username are already in use.
eliddell
Forum Newbie
Posts: 8
Joined: Thu Apr 22, 2010 12:31 pm

Re: msql - unique fields

Post by eliddell »

you both just confused me.. can you show me what my entire code should look like?

i rewrote it to say:

Code: Select all

// add user to database
mysql_connect($DBhost,$DBuser,$DBpass) or die("Unable to connect to database");

@mysql_select_db("$DBName") or die("Unable to select
database $DBName");
$result = mysql_query("SELECT userName, email FROM $table WHERE userName = '$userName' OR email = '$email'", $connection);
if ($row = mysql_fetch_assoc($result)) {
    if ($row['userName'] == $userName) {
        echo " username already taken";
    } elseif ($row['email'] == $email) {
        echo " email already taken";
    }
}else{
	echo "user name and email are available";
	$sqlquery = "INSERT INTO $table(userName,password,email)
	VALUES('$userName', '$password', '$email')";

	mysql_query($sqlquery);
}
mysql_close();
but then it returned the errors:
Warning: mysql_query(): supplied argument is not a valid MySQL-Link resource in /home/eliddell/kingnozzle.com/regUser.php on line 25

Warning: mysql_fetch_assoc(): supplied argument is not a valid MySQL result resource in /home/eliddell/kingnozzle.com/regUser.php on line 26
user name and email are available
Last edited by eliddell on Fri Jul 15, 2011 11:54 am, edited 1 time in total.
User avatar
Bill H
DevNet Resident
Posts: 1136
Joined: Sat Jun 01, 2002 10:16 am
Location: San Diego CA
Contact:

Re: msql - unique fields

Post by Bill H »

We're not in the business of writing code for you. Jonah gave you an excellent sample of testing for prior use.

Code: Select all

if (mysql_query($sqlquery))
{     // indicate success
}
else
{     // indicate user name or email already taken
}
eliddell
Forum Newbie
Posts: 8
Joined: Thu Apr 22, 2010 12:31 pm

Re: msql - unique fields

Post by eliddell »

right but as stated above.. that returned errors
User avatar
Bill H
DevNet Resident
Posts: 1136
Joined: Sat Jun 01, 2002 10:16 am
Location: San Diego CA
Contact:

Re: msql - unique fields

Post by Bill H »

You only show twenty lines of code and it's returning an error on line 25, so it's pretty hard to tell you what the problem is when a) you aren't showing all the code which b) makes it impossible for us to know which line of code is throwing the error.
eliddell
Forum Newbie
Posts: 8
Joined: Thu Apr 22, 2010 12:31 pm

Re: msql - unique fields

Post by eliddell »

sorry line 25 and 26 are as follows:

$result = mysql_query("SELECT userName, email FROM $table WHERE userName = '$userName' OR email = '$email'", $connection);
if ($row = mysql_fetch_assoc($result)) {
User avatar
Jonah Bron
DevNet Master
Posts: 2764
Joined: Thu Mar 15, 2007 6:28 pm
Location: Redding, California

Re: msql - unique fields

Post by Jonah Bron »

You need to assign the value of mysql_connect() on line 21 to $connection.

Code: Select all

$connection = mysql_connect($DBhost,$DBuser,$DBpass) or die("Unable to connect to database");
eliddell
Forum Newbie
Posts: 8
Joined: Thu Apr 22, 2010 12:31 pm

Re: msql - unique fields

Post by eliddell »

thanks.. that was it!
Post Reply