Page 1 of 1
msql - unique fields
Posted: Thu Jul 14, 2011 3:01 pm
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();
Re: msql - unique fields
Posted: Thu Jul 14, 2011 7:17 pm
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
}
}
Re: msql - unique fields
Posted: Fri Jul 15, 2011 10:19 am
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.
Re: msql - unique fields
Posted: Fri Jul 15, 2011 11:43 am
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
Re: msql - unique fields
Posted: Fri Jul 15, 2011 11:53 am
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
}
Re: msql - unique fields
Posted: Fri Jul 15, 2011 11:55 am
by eliddell
right but as stated above.. that returned errors
Re: msql - unique fields
Posted: Fri Jul 15, 2011 12:38 pm
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.
Re: msql - unique fields
Posted: Fri Jul 15, 2011 12:44 pm
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)) {
Re: msql - unique fields
Posted: Fri Jul 15, 2011 1:03 pm
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");
Re: msql - unique fields
Posted: Fri Jul 15, 2011 1:42 pm
by eliddell
thanks.. that was it!