In the database I put the login and user index UNIQUE, and this problem: if you register a user with identical login: nickname, then nothing is entered in the database. But at the same time, there is a space in id. And if you try to change something one (login, Suppose we leave identical), then such data is already entered in bd.A I need to, so that neither the login, nor the nickname coincide once. Ie, completely individual data. Tell me how to do it, otherwise I’m still not sure)

  • 2
    two separate unique indexes. one for login, second for nickname. Don't pay attention to id, skipping is not scary - Mike
  • 2
    Would kill everyone who does not allow duplicate nicknames. Because of such bastards, you always have to invent something idiotic, instead of the normal Sergey - Sergey

2 answers 2

I didn’t quite understand the question and problems, but I’ll still try to answer: in order for id not to be empty it’s worth doing an auto_increment right away and not touching it, and making login and nickname NOT NULL unique. Below I replaced the login to email.

 CREATE TABLE users ( id int unsigned NOT NULL auto_increment, email varchar(32) NOT NULL UNIQUE, username varchar(32) NOT NULL UNIQUE, PRIMARY KEY (id) );

PHP example below. I suggested that you need to make some form of login in the form: "Enter your login or email."

 <?php $conn = new PDO(DB_DSN, DB_USERNAME, DB_PASSWORD); $sql = "SELECT id,username,email FROM users WHERE username like 'vasya' OR email like 'vasya@example.com' LIMIT 1"; $st = $conn->query($sql); if($row = $st->fetch(PDO::FETCH_ASSOC)) { $id = (int) $row['id']; } $conn = null; ?>

    Check directly via php, or what you use there:

     <?php $db = new mysqli("localhost", "username", "userpwd", "dbname"); $login = "login_test"; $login = $db->real_escape_string($login); // не обязательно. только при $_GET И $_POST $username = "username_test"; $username = $db->real_escape_string($username); // не обязательно. только при $_GET И $_POST $table = "test"; // имя таблицы, в которой нужно проверить то, что ты хочешь if($db->query("SELECT COUNT(*) FROM `".$table."` WHERE `login` = '".$login."' AND `username` = '".$username."'") == 0) { // если нету идентичной записи } else { // если есть } ?>
    • 2
      between $num_rows = $db->query(...) and if($num_rows == 0) {... there is a small amount of time in which, nevertheless, a parallel query can manage to complete user registration with exactly the same login and / or nickname. More harmful advice is hard to imagine. - Sergey