check if username existing db

[po3]

Hi, I'm trying to work out a little checking process that looks at the username entered by my user in the "create a user" page and checks it against the DB to be sure the name doesn't already exist.

on the create a user page:

        $fields_with_lengths = array('username' => 30, 'password' => 30, 'client' => 55);
        $errors = array_merge($errors, check_max_field_length($fields_with_lengths, $_POST));

        $username = trim(mysql_prep($_POST['username']));
        $password = trim(mysql_prep($_POST['password']));
        $hashed_password = sha1($password); //requires PHP 4.3 
        // PHP 5.1.2 php.net new hashing functions lookup 'hash'
        $client_name = trim(mysql_prep($_POST['client']));
        
        //check to see if username has been used yet
        $fields_not_to_duplicate = array('username', 'client');
        $errors = array_merge($errors, check_for_duplicates($fields_not_to_duplicate, $username, $_POST)); 

In my form function include file that is included on the create a user page:

    function check_for_duplicates($fields_not_to_duplicate_array, $username) {
        $field_errors = array();        
        foreach ($fields_not_to_duplicate_array as $fieldname) {                
                //check to see if username has been used yet
                $query = "SELECT username ";
                $query .= "FROM users ";
                $query .= "WHERE username = {$username} ";
                $result_set = mysql_query($query, $connection);
                confirm_query($result_set);
                if (mysql_num_rows($result_set) == 1) {
                    //username is already taken
                    $field_errors[] = "This " . ucfirst($fieldname) . " is already taken. Please choose another.";
                }
        }
        return $field_errors;
    } 

I think the problem has something to do with this line: "$query .= "WHERE username = {$username} ";". Am I using the right form of substitution there?

I get the following error when trying to submit the create a user form "Warning: mysql_query(): supplied argument is not a valid MySQL-Link resource in /Users/erik/Sites/mysite/assets/includes/form_functions.php on line 47 Database query failed:"

Any help would be greatly appreciated.

[NogDog]

I usually just do an insert, and check for a Duplicate Key error. That way there's no fear of a race condition between nearly simultaneous requests. (This assumes that the user ID field is either the primary key or has a unique index in the table definition.) If the query fails, check to see if mysql_errno() == 1062, and if so you know it was a duplicate, so output the desired error and have the user try another ID.