Update UNIQUE Users

Hi guys. I am working on an update function which edits users of the admin area of my website.

The functions works well to certain extend. I need to make sure that when a user is updated that a repeat username is not used, and display a message that a particular user already exist.

I have that functionality in my function, however, this part of the code is not executed and I keep getting the error message: Duplicate entry 'b' for key 'username'. I also need to keep intact any part of the content that's not updated, i.e., password not changed, etc.

Here's the code:

function editUser($userUsername, $userPassword, $userEmail, $userRealname, $id){
//select users to see if they exist 

$query = mysql_query("SELECT * FROM authorized_users WHERE username='$userUsername' ") or die(mysql_error()) ;
if (mysql_num_rows($query) >= 1 )

{
/* Username already exists */
echo "<p><p><span class='current_admin'>Username already exists</span>";

echo '<p>Please <a href="javascript:history.go(-1)">Go Back</a> and complete the form<br>';
echo '<META HTTP-EQUIV="Refresh" CONTENT="8; URL=users_authorized.php">';
}
else 
{
/* Username doesn't exist */     
$query = mysql_query("UPDATE authorized_users SET username ='$userUsername', password = '$userPassword', email='$userEmail', realname='$userRealname' ") or die (mysql_error());
echo '<p><p><b> 1 User Added</b>';
echo '<META HTTP-EQUIV="Refresh" CONTENT="4; URL=users_authorized.php">';
}
}

Thanks in advance for your help.

Can somebody help me please?

Thanks!

There are two problems with your code:

1) If the user is updating other info, but not their username, the code you have will always fail because there will already be a user with the username passed to the function (the one editing their info). You need to see if there are existing users with the same user name EXCLUDING the current user.

2) Your update query doesn't have a WHERE clause - it is trying to update ALL the records in the table. Update queries should almost always have a WHERE clause. If it doesn't have one you need to be very sure before running it, lest you overwrite all the records in the table.

Also, it is good practice to create your queries as variables, then run those variables as the query parameter. That way, if there is a problem, you can echo, log etc. the query to the page for analysis.

function editUser($userUsername, $userPassword, $userEmail, $userRealname, $id)
{
    //See if there is ANOTHER user with same username
    $query = "SELECT *
              FROM authorized_users
              WHERE username = '$userUsername'
                AND id <> $id";
    $query = mysql_query($query) or die(mysql_error()) ;
    if (mysql_num_rows($query) !== 0)
    {
        /* Username already exists */
        echo "<p><p><span class='current_admin'>Username already exists</span>";
        echo '<p>Please <a href="javascript:history.go(-1)">Go Back</a> and complete the form<br>';
        echo '<META HTTP-EQUIV="Refresh" CONTENT="8; URL=users_authorized.php">';
    }
    else 
    {
        /* Username doesn't exist */
        $query = "UPDATE authorized_users
                  SET username ='$userUsername',
                      password = '$userPassword',
                      email = '$userEmail',
                      realname = '$userRealname'
                  WHERE id = $id";
        $query = mysql_query($query) or die (mysql_error());
        echo '<p><p><b> 1 User Added</b>';
        echo '<META HTTP-EQUIV="Refresh" CONTENT="4; URL=users_authorized.php">';
    }
}

Thanks for your response mjdamato. I will take a look and keep you posted. Thanks a lot!

I tested the code with your changes, but now I get the following error:

Unknown column 'testuser' in 'where clause'

Also, now the code will not update anything, even when a new user is used.

Can you take a scond look?

.... Thanks!

There's nothing in the code I provided that would produce that error. Echo the query to the page so you can find out what is wrong with it.

Actually, single quotes were missing in both WHERE clauses, in the '$id' variable.

I fixed that, and I am not getting that error anymore. However, the bahavior of the script has changed as follow:

1) Again, the unknow column error is gone.

2) If I try to edit anything other than the username, it will display the "Username already exists" text, and no update will take place in the database.

3) If I edit the username (along with any other items), it will display the text User Updated, but the actual update will not take place.

One last thing, I noticed you used  "not equal to:" !== as opposed to !=. I did test it with a sigle iqual sign, but results did not vary. Here's the updated function:

function editUser($userUsername, $userPassword, $userEmail, $userRealname, $id)
{
    //See if there is ANOTHER user with same username
    $query = "SELECT *
              FROM authorized_users
              WHERE username = '$userUsername'
                AND id <> '$id'";
    $query = mysql_query($query) or die(mysql_error()) ;
    
if (mysql_num_rows($query) !== 0)
    {
        /* Username already exists */
        echo "<p><p><span class='current_admin'>Username already exists</span>";		
        echo '<p>Please <a href="javascript:history.go(-1)">Go Back</a> and complete the form<br>';        
	echo '<META HTTP-EQUIV="Refresh" CONTENT="8; URL=users_authorized.php">';
    }
    else 
    {
        /* Username doesn't exist */
        $query = "UPDATE authorized_users
                  SET username ='$userUsername',
                      password = '$userPassword',
                      email = '$userEmail',
                      realname = '$userRealname'
                  WHERE id = '$id'";
        
	$query = mysql_query($query) or die (mysql_error());
        
	echo '<p><p><b> 1 User Updated</b>';
        echo '<META HTTP-EQUIV="Refresh" CONTENT="4; URL=users_authorized.php">';
    }
}

You can view it in action in this test site: http://javierbooks.comuf.com/administrar/

Just cick on Manage Users and use test for both username and password.

Thank you so much for your help!

I tried the page you linked to and everythign seemed to work fine for me no matter what I tried to edit.

Actually, single quotes were missing in both WHERE clauses, in the '$id' variable.

I intentionally left off quote marks from the ID value because and ID should be a number and wouldn't require quotes.

Really.... maybe I didn't explain myself well enough.  Remember that you are testing the Manage Users, not Manage Posts. OK, test the following and you will see:

1) Click on Manage Users, then click Edit on the first user (a). The update form will load. Just for testing purposes click SAVE without changing anything. One of Two things should happen in the database:

    a) DB should be updated, but since you haven't changed anything, all data will stay the same. (Currently this is no happening. Since the current user is the one trying to  update the database, it should no give you and User Exist error)

    b) Or nothing should happen since there was no change.

2) Click on Manage Users, then click Edit on the first user (a).  Change anything, excluding the username, and the following should happen:

    a) The database should be updated, and since the username wasn't changed, this will stay the same. (For this test, you are also getting the Username Exist Error text, and this should no happen).

3) Finally,  Click on Manage Users, then click Edit on the first user (a). Change anything, including the username, and the following should happen:

  a) All information should be updated accordingly, and 4 seconds after the User Updated message is displayed, you should see the changes in the Mange Users page. (Again this is not happening. Even when the User Updated message is displayed, you will see that no actual changes occurred in the database when you check the Manage Users page).

So there's something in the structure of the code that is wrong because are getting any database errors.

I hope this is a bit more clear. Thank you so much for help. It's very appreciated. Here's the link again,

http://javierbooks.comuf.com/administrar

Hmm...

I've reviewed the query a few times now and don't see any reason why that error would be triggered assuming the correct data is getting passed to the function. I have a suspicion that the ID being passed to the function is not correct.

Try adding the following code for debugging purposes and see what you get.

    if (mysql_num_rows($query) !== 0)
    {
        /* Username already exists */
        echo "<p><p><span class='current_admin'>Username already exists</span>";
        echo '<p>Please <a href="javascript:history.go(-1)">Go Back</a> and complete the form<br>';
        echo '<META HTTP-EQUIV="Refresh" CONTENT="8; URL=users_authorized.php">';
        ## START DEBUGGING CODE
        echo "Query: $query<br>\n";
        echo "Results: <pre>";
        while($row = mysql_fetch_assoc($query))
        {
            print_r($row);
        }
        echo "<pre>\n";
        ## END DEBUGGING CODE
    }

Hi mjdamato. I am sorry for the delay in my reply. I have dealing with a lot of pain in my hands lately, due to a severe case of carpal tunnel syndrom.

I used the cod eas you suggested :

function editUser($userUsername, $userPassword, $userEmail, $userRealname, $id)
{
    //See if there is ANOTHER user with same username
    $query = "SELECT *
              FROM authorized_users
              WHERE username = '$userUsername'
                AND id <> '$id'";
    $query = mysql_query($query) or die(mysql_error()) ;
    
if (mysql_num_rows($query) !== 0)
    {
        /* Username already exists */
        echo "<p><p><span class='current_admin'>Username already exists</span>";
        echo '<p>Please <a href="javascript:history.go(-1)">Go Back</a> and complete the form<br>';
        echo '<META HTTP-EQUIV="Refresh" CONTENT="8; URL=users_authorized.php">';
        ## START DEBUGGING CODE
        echo "Query: $query<br>\n";
        echo "Results: <pre>";
        while($row = mysql_fetch_assoc($query))
        {
            print_r($row);
        }
        echo "<pre>\n";
        ## END DEBUGGING CODE
    }
}


?>

Two things I noticed:

1) If I edit the username, it seems to execute, but takes me to a blank page.

2) If edit anything other than the username, I get the following:

Username already exists

Please Go Back and complete the form

Query: Resource id #6

Results:

Array

(

    [id] => 428

    [username] => ko

    [password] => ed73f6b46391b95e1d03c6818a73b8b9

    => ko

    [realname] => ko

)

Did I place your new code in the right place? Thanks for your help.

1) If I edit the username, it seems to execute, but takes me to a blank page.

The code above is just a function to update the record. You need to look at the code that calls the function and what it does afterward

2) If edit anything other than the username, I get the following:

 

 

 

 

Username already exists

 

Please Go Back and complete the form

 

Query: Resource id #6

Results:

Array

(

    [id] => 428

    [username] => ko

    [password] => ed73f6b46391b95e1d03c6818a73b8b9

    => ko

    [realname] => ko

)

There was a typo in the code I provided, but you also left out the code that would actually update the record. Try this

<?php

function editUser($userUsername, $userPassword, $userEmail, $userRealname, $id)
{
    //Escape input for DB queries
    $userUsername = mysql_real_escpae_string(trim($userUsername));
    $userPassword = mysql_real_escpae_string(trim($userPassword));
    $userEmail    = mysql_real_escpae_string(trim($userEmail));
    $userRealname = mysql_real_escpae_string(trim($userRealname));
    $id           = (int) $id;

    //See if there is ANOTHER user with same username
    $query = "SELECT *
              FROM authorized_users
              WHERE username = '$userUsername'
                AND id <> '$id'";
    $result = mysql_query($query) or die(mysql_error()) ;
    
if (mysql_num_rows($result) > 0)
    {
        /* Username already exists */
        echo "<p><p><span class='current_admin'>Username already exists</span>";
        echo '<p>Please <a href="javascript:history.go(-1)">Go Back</a> and complete the form<br>';
        echo '<META HTTP-EQUIV="Refresh" CONTENT="8; URL=users_authorized.php">';
        ## START DEBUGGING CODE
        echo "Query: $query<br>\n";
        echo "Results: <pre>";
        while($row = mysql_fetch_assoc($query))
        {
            print_r($row);
        }
        echo "<pre>\n";
        ## END DEBUGGING CODE
    }

    //There is not another user with same username (or user didn't select to change username)
    $query = "UPDATE authorized_users
              SET username = '$userUsername'
                  password = '$userPassword'
                  email    = '$userEmail'
                  realname = '$userRealname'
              WHERE id = $id";
    $result = mysql_query($query) or die(mysql_error());
    if(mysql_affected_rows()==0)
    {
        //No matching record found
        echo "The user does not exist";
    }
    else
    {
        echo "The record was successfully updated";
    }
}	

?>

Ok, here's the update error message I am getting now.

Username already exists

Please Go Back and complete the form

Query: SELECT * FROM authorized_users WHERE username = 'ko' AND id <> '0'

Results:

Warning:  mysql_fetch_assoc() expects parameter 1 to be resource, string given in F:\wamp\www\JavierBooks\website - Back-Up\administrar\includes\functions.php on line 354

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'password = 'ed73f6b46391b95e1d03c6818a73b8b9'

                  email    = 'ko'' at line 3

I fixed several typos I noticed when escaping the form inputs - mysql_real_escape_string();

Thanks.

Ok, here's the update error message I am getting now.

 

Username already exists

 

Please Go Back and complete the form

Query: SELECT * FROM authorized_users WHERE username = 'ko' AND id <> '0'

Results:

 

Warning:  mysql_fetch_assoc() expects parameter 1 to be resource...

Is the error regarding "User name already exists" correct? I would think so. Based on the query you were trying to modify the record with an ID of 0 and either the name of that record was already 'ko' or you were changing it to 'ko'. In either case there appear to be other records in the database with that username.

The mysql_fetch_assoc() error was a typo in the debugging code and has nothing to do with the logic of the code. To, fix that error use this

while($row = mysql_fetch_assoc($result))

OK, I updated as recommended. I did, however discovered that we were no putting the commas in the UPDATE query sefter SETting the values.

Here's the updated function:

function editUser($userUsername, $userPassword, $userEmail, $userRealname, $id)
{
    //Escape input for DB queries
    $userUsername = mysql_real_escape_string(trim($userUsername));
    $userPassword = mysql_real_escape_string(trim($userPassword));
    $userEmail    = mysql_real_escape_string(trim($userEmail));
    $userRealname = mysql_real_escape_string(trim($userRealname));
    $id           = (int) $id;

    //See if there is ANOTHER user with same username
    $query = "SELECT *
              FROM authorized_users
              WHERE username = '$userUsername'
                AND id <> '$id'";
    $result = mysql_query($query) or die(mysql_error()) ;
    
if (mysql_num_rows($result) > 0)
    {
        /* Username already exists */
        echo "<p><p><span class='current_admin'>Username already exists</span>";
        echo '<p>Please <a href="javascript:history.go(-1)">Go Back</a> and complete the form<br>';
        echo '<META HTTP-EQUIV="Refresh" CONTENT="800; URL=users_authorized.php">';
        ## START DEBUGGING CODE
        echo "Query: $query<br>\n";
        echo "Results: <pre>";
        while($row = mysql_fetch_assoc($result))
        {
            print_r($row);
        }
        echo "<pre>\n";
        ## END DEBUGGING CODE
    }

    //There is not another user with same username (or user didn't select to change username)
    $query = "UPDATE authorized_users
              SET username = '$userUsername',
                  password = '$userPassword',
                  email    = '$userEmail',
                  realname = '$userRealname'
              WHERE id = $id";
    $result = mysql_query($query) or die(mysql_error());
    if(mysql_affected_rows()==0)
    {
        //No matching record found
        echo "The user does not exist";
    }
    else
    {
        echo "The record was successfully updated";
    }
}	

and here's what I am getting now when:

a) the user name is NOT edited

Username already exists

Please Go Back and complete the form

Query: SELECT * FROM authorized_users WHERE username = 'ko' AND id <> '0'

Results:

Array

(

    [id] => 428

    [username] => ko

    [password] => ed73f6b46391b95e1d03c6818a73b8b9

    => ko

    [realname] => ko

)

and b) when the username is edited:

The user does not exist

I definitively think we are getting closer. However, no record is being updated at all in the database.

Well, look at the results for scenario A. Your query is looking for users with username 'ko' and an ID that is NOT 0. And, it found one (ID = 428). Since you were not editing the username that tells me that the ID of the user you were trying to modify is actually the one with ID 428 - not 0. That also explains the error you received in scenario B.

You are not passing the correct ID to the function. Look at where you are calling the function and validate the ID.

Thank you so much for your help.  At this point I am taking a break as I am having  lot of pain due to my carpal tunnel syndrome.

I will get back to this at some point. Again, your support has been much appreciated.