0

Background

I have a web program which has an administrator panel that allows the current logged in user to add new logins and passwords into the system for other users. While working on some code, it had occurred to me that although I check for the same 'first' and 'last' names, as well as same 'username' in the system, I do not currently check for the user 'Bilbo Baggins' as should 'bilbo baggins' be checked.

In theory, I could do two times the checking of all similarities... whether he be 'bilbo baggins', 'bILBO BaggINS' or 'BILBO BAGGINS'... But I presume there would be a better way to do this rather than rewriting the same code to check over and over the same information.

Using my current implementation, does someone have a similar situation to check 1) the same first name and last name are not reserved in the system, and 2) the same username does not exist?

CODE:

...set all variables used in post...

try {
    //Confirm that there are no other same first and last names with the same registration
    $query = $general->db->prepare("SELECT * FROM users WHERE firstname = :firstname AND lastname = :lastname");
    $names  =  array(
      'firstname' => $firstname,
      'lastname' => $lastname);
  $query->execute($names);
  $result = $query->fetchAll();

      if (sizeof($result) == 0) {
          //Now check that the username isn't currently in use
          $query = $general->db->prepare('SELECT * FROM users WHERE username = :username');
          $username = array('username' => $username);
          $query->execute($username);
          $usernames_exist = $query->fetchAll();

          if (sizeof($usernames_exist) == 0) {
          ##both fullname test as well as username check passed, so allow user to enter the information
            $addedUser = $users->register_user($firstname, $lastname, $username['username'], $password, $cellphone, $seclvl, $email, $direct, $extension);
            $message = "Congratulations, you have successfully added **AN ALREADY ACTIVATED** user with the following information: <br><br>";
Community
  • 1
  • 1
hawkhorrow
  • 475
  • 2
  • 7
  • 18
  • My family friend Mike Smith and his son Mike Smith would be really unhappy with your choice to limit accounts to unique first and last name combos. The Mike Smith (unrelated) that I went to school with would be pretty unhappy too. Yes, I know three people with identical first and last names. – TunaMaxx Aug 19 '14 at 23:16

2 Answers2

0

MySQL WHERE clauses are case insensitive by default. So...

  • Bilbo_Baggins
  • bilbo_baggins
  • bILBO_BaggINS
  • BILBO_BAGGINS

...are all only going to find the one record containing 'bilbo_baggins' in the db. Just make sure that field has a UNIQUE index.

See here for more info.

Community
  • 1
  • 1
TunaMaxx
  • 1,782
  • 12
  • 18
  • 1
    "I love it when a plan comes together"... +1 cookie... or in @TunaMaxx's case +1 piece of celery mixed in – hawkhorrow Aug 19 '14 at 23:54
  • Quick Question though, since I store firstname and lastname in two different columns, will this affect anything? or rather should I set both of those columns to UNIQUE index? – hawkhorrow Aug 19 '14 at 23:56
  • You can create a UNIQUE index over the firstname and lastname columns, so that the combination of the two must be unique. However, that may not be a good idea. You will find that a first name / last name combination is nowhere near as unique as you would think, as in my other comment about the the three Mike Smith's that I know. The only thing the REALLY needs to be unique is the username field. – TunaMaxx Aug 20 '14 at 19:40
0

For your system to function well you need to not check on first / last name. But now to get back on point.

What you can do is have all of the text in lower case form it enters the database. Once you pull it out you can format the text into the correct form.

This allows all of your data to function in the same way so you don't have to have so many checks.

Bioto
  • 1,127
  • 8
  • 21