check if username is not taken between two tables

Question

Well i have a problem. I have a registration for for legal users and natural users, but i need to write a validation in each php file for username checking, i have no idea how to combine two table checking.

one table is called users_legal, 2nd one is users_natural. In both forms name for input field is "username".

So far i have a code that checks passwords :

if ($password == $password_re)
{
    // insert into table
    $sql = $db->prepare("INSERT INTO users_legal(name, reg_number, address, phone, email, username, password) VALUES(?, ?, ?, ?, ?, ?, ?);");
    $sql->bind_param("sssssss", $name, $reg_number, $address, $phone, $email, $username, $password);        
    $sql->execute();
    $sql->close();
}

After makeing a validation in register forms, i also need it in login page. I figured out how to check if there is only and only one user with that username and that password, but i have no idea how to search them between tables.

login.php code :

if($_SERVER["REQUEST_METHOD"] == "POST") {
  // username and password sent from form 

  $myusername = mysqli_real_escape_string($db,$_POST['username']);
  $mypassword = mysqli_real_escape_string($db,$_POST['password']); 
  $sql = "SELECT id FROM login WHERE username = '$myusername' and password = '$mypassword'";
  $result = mysqli_query($db,$sql);
  $row = mysqli_fetch_array($result,MYSQLI_ASSOC);
  $active = $row['active'];
  $_SESSION['username'] = $myusername;
  $count = mysqli_num_rows($result);


  if($count == 1) {
     session_register("myusername");
     $_SESSION['username'] = $myusername;
     $_SESSION['loggedin'] = true;
     header("location: www.goole.lv");
  }else {
     $error = "Your Login Name or Password is invalid";
  }
}

One more thing : i set my mysql to utf format, var_dump says that input is allright, but in mysql it saves in unbelievuble forms, like Ä¶egums or SkrÄ«velis.

Thnx for any examples, tips or whateva u got.

Your code is open to SQL injections and it seems that the passwords of your users are stored in clear in your database. Please have a look to https://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php, and [`password_hash()`](https://php.net/password_hash) and [`password_verify()`](https://php.net/password_verify). — Syscall, Mar 15 '18 at 20:37

score 0 · Accepted Answer · answered Mar 15 '18 at 20:49

0

When I got you right, you have two tables with users. To validate if an user has logged in successfully you look up their login credentials in the related database table.

You are asking for 'combining' these two tables. But I don't think that that's what you want. You have two separate user tables. They do not belong to each other. If you join those tables, you might have dulicate unique user ids when combining these tables.

What you could do instead is check both tables separately, first for users_legal and second for users_natural.

You should also think about using password hashes instead of plain passwords in your db. And use pdo ;)

Good luck

answered Mar 15 '18 at 20:49

Leo

1,508
13
27

well yeah, i have two seperate tables with users, each table ahs different fields, but some of them are same in both, for example, username, password, name and address. botht ables have these fields. In login page i need to check if user exists on first or 2nd table, if it exists in one of them, then login, else throw error. Same in registration - if username in any table is taken, throw error message. – Maris S. Mar 15 '18 at 22:04
What's wrong with handling both tables separately? For example, when validating an username you can search the first table for that name. If it is taken, throw an error if not continue searching the second table for that username. If its taken in this table, throw (the same) exception. If its free the user can take that name since it's not used in one of your account tables. – Leo Apr 08 '18 at 09:43

score -1 · Answer 2 · answered Mar 15 '18 at 21:02

To solve the problem of having two different types of users I would just put them in the same table and add a value that represents the user type for example 0 = legal and 1 = natural. This will also automatically prevent two users from sharing the same username. (This will only work for sure if the database is still empty, if not you might end up with two users with the same name). For the character encoding try setting mysql to utf-8 if you haven’t done it already (instead of just utf). Also you should never save passwords in plaintext. Use the sha1 function in php to convert them to their hash value before storing them. That way even if someone gets unauthorized access to the database they still won’t know the passwords. You should also append some user specific information to the password before hashing so that even if two users share the same password their hash values will be different. To verify if it’s correct you just apply the same procedure to the input before comparing it with the hash value you have stored.

The problem is that i have different fields for both types, like legal persons has reg_number and name of company while natural person needs just name and surename instead of that — Maris S., Mar 15 '18 at 22:01
and about utf8 - i checked out every possible option of formats, none of them worked, like utf8_bin or utf8_latvian (smth like that) didnt save field data in coding i wanted. About types - its recommended to save as text, for example phone number or registration number, because i havent got any plans in future to do calculations with them, thats why i leave them as a text. But there is no need to convert them before storing, because var_dump says that they are allright so problem is in mysql table. — Maris S., Mar 15 '18 at 22:08

check if username is not taken between two tables

2 Answers2