Qt prevent to insert duplicate data SQLITE Problem!

Siamak Royal

Hi there . i have a problem in login and register form that i built in qt ( I am beginner ) . so i wrote a code to prevent inserting duplicate data in sqlite and i write this part of code in buttonClicked of registering.

if(data.open()){
        QString username,password,email,phone;
        username = ui->line_Edit_username_Registery->text();
        password = ui->line_Edit_password_Registery->text();
        email = ui->line_edit_email->text();
        phone = ui->line_edit_phone->text();

        //Query Check
        QSqlQuery queryCheck;
        queryCheck.prepare(QString("SELECT * FROM register where username = :username AND        password = :password AND email = :email AND phone = :phone"));
        queryCheck.bindValue(":username", username);
        queryCheck.bindValue(":password", password);
        queryCheck.bindValue(":email",email);
        queryCheck.bindValue(":phone",phone);


        bool check = true;
        if ( queryCheck.exec())
        {

            while (queryCheck.next()) {
                QString usernameDB,passwordDB,emailDB,phoneDB;
                username_database = queryCheck.value(1).toString();
                password_database = queryCheck.value(2).toString();
                email_database = queryCheck.value(3).toString();
                phone_database = queryCheck.value(4).toString();
                if(username == username_database || phone == phone_database || email == email_database){
                    QMessageBox::information(this,"Error!","Duplicated!");
                    check = false;

                }

            }
        }// End Query Check

so as you can see i wrote a condition
if(username == username_database || phone == phone_database || email == email_database).
to prevent duplicate data and when i insert all four data ( username , password , email , phone ) data won't insert to SQLITE (Duplicate error QMessageBox::information(this,"Error!","Duplicated!"); ).
but when i for example changing password and then insert it . data is going to insert in database while there is duplicate user name and phone and password.

how can i solve this problem.

Whole Code of Button_Clicked Register Form.

void mainwindow::on_pushButton_register_clicked()
{

   if(data.open()){
       QString username,password,email,phone;
       username = ui->line_Edit_username_Registery->text();
       password = ui->line_Edit_password_Registery->text();
       email = ui->line_edit_email->text();
       phone = ui->line_edit_phone->text();

       //Query Check
       QSqlQuery queryCheck;
       queryCheck.prepare(QString("SELECT * FROM register where username = :username AND        password = :password AND email = :email AND phone = :phone"));
       queryCheck.bindValue(":username", username);
       queryCheck.bindValue(":password", password);
       queryCheck.bindValue(":email",email);
       queryCheck.bindValue(":phone",phone);


       bool check = true;
       if ( queryCheck.exec())
       {

           while (queryCheck.next()) {
               QString usernameDB,passwordDB,emailDB,phoneDB;
               username_database = queryCheck.value(1).toString();
               password_database = queryCheck.value(2).toString();
               email_database = queryCheck.value(3).toString();
               phone_database = queryCheck.value(4).toString();
               if(username == username_database || phone == phone_database || email == email_database){
                   QMessageBox::information(this,"Error!","Duplicated!");
                   check = false;

               }

           }
       }// End Query Check


       // query insert
       if(check){
           QSqlQuery query;
           query.prepare("INSERT INTO register (username, password , email , phone) "
                         "VALUES (:username, :password, :email , :phone)");
           query.bindValue(":username", username);
           query.bindValue(":password", password);
           query.bindValue(":email", email);
           query.bindValue(":phone", phone);


               bool chkbox = ui->checkBox->isChecked();
               //Checking checkbox condition
               if (chkbox){
                   if(query.exec())
                       QMessageBox::information(this,"this","yes");
                   else
                       QMessageBox::information(this,"this","no");
               }
               else {
                   QMessageBox::information(this,"title","Please accept the rules");
               }//Checking checkbox condition
       }
   }
   else{
       QMessageBox::information(this,"this","database didn't connect");
   }
}

Siamak Royal

i find the solution . the problem was in selecting table from database and i replace AND by OR and then it checks them one by one .

thank you .

Gojir4

@Siamak-Royal said in Qt prevent to insert duplicate data SQLITE Problem!:

queryCheck.prepare(QString("SELECT * FROM register where username = :username AND password = :password AND email = :email AND phone = :phone"));

Hi, I don't know SQL, but here password = :password you are selecting only records with specific password, so I guess you retrieve only records from the database with this specific password and not the others, which have different password but same name, email or phone. That's probably why you don't detect the duplicated data in your loop, because you never iterate on them.

artwaw

Hi,
have you considered marking a column(s) in the database unique? Having that set any insert will fail - you can examine error type afterwards to eventually display notification to the user. Seems like less work?

JonB

@artwaw
I guess OP's username is already primary key(?) One could add email as unique too. I'm thinking that password certainly must not be unique(!), and users may be allowed to share phone numbers....

artwaw

@JonB Hard to say, OP didn't share the table definition. However, sqlite is very fast backend so if something can be done there I prefer to do it there.

Siamak Royal

i find the solution . the problem was in selecting table from database and i replace AND by OR and then it checks them one by one .

thank you .