Question About Mysql Update Query

[eldan88]

Hey,

 

I have wrote a simple mysql update statment below. However when I enter a duplicate username and password, there obviously isn't any mysql_affected_rows. So how do create another condition that checks to see if there has been a duplicate?

 

Thanks

 

if(empty($errors))
{
$id= $_POST['id']
$username= $_POST['username'];
$password = $_POST['password'];

$query = "UPDATE tablename SET
username = '{$username}',
password = '{$password}'
WHERE id = '{$id}'";


$result_set = mysql_query($query,$connection);
if (mysql_affected_rows() == 1 ) {
// mysql_affected_rows is succesful
}// end of if condition for the mysql_affected rows
else {
echo "Username and Password Creation Failed" . "<br />";
echo mysql_error();
  } // End of else condition if mysql_affected_rows did not succeed
 } // End of if(empty($errors)) {

[MDCode]

if(mysql_num_rows($result_set) != "0") {
echo "Duplicate exists";
}

[Langstra]

$query = "UPDATE tablename t1 SET
t1.username = '{$username}',
t1.password = '{$password}'
WHERE t1.id = '{$id}' AND !EXISTS(
SELECT *
FROM tablename t2
WHERE t2.account = '{$account}' AND t2.id != '{$id}'
)"

 

This checks if there does not exist another row with that username that you want to set. But it does exclude the row you want to update, because that may be the same username.

Edited December 13, 2012 by Langstra

[eldan88]

$query = "UPDATE tablename t1 SET
t1.username = '{$username}',
t1.password = '{$password}'
WHERE t1.id = '{$id}' AND !EXISTS(
SELECT *
FROM tablename t2
WHERE t2.account = '{$account}' AND t2.id != '{$id}'
)"

 

This checks if there does not exist another row with that username that you want to set. But it does exclude the row you want to update, because that may be the same username.

 

Oh gotcha. That makes a whole lot of sense. Thank you!