Delphi Delphi MYSQl Update information

[mia]

i currently have this query to update some records

Kod: Tümünü seç

connectionmo.userslq.Close;
connectionmo.userslq.SQL.Clear;
connectionmo.userslq.SQL.Add('UPDATE `users` SET `username` = :uname, `password` = :pass WHERE `users`.`id` = :id;');
connectionmo.userslq.ParamByName('id').AsString := trim(idu.Text);
connectionmo.userslq.ParamByName('uname').AsString := trim(txtUsername.Text);
connectionmo.userslq.ParamByName('pass').AsString := trim(txtUserPass.Text);
connectionmo.userslq.ExecSQL;
connectionmo.userslq.Close;
MessageDlg('user has been updated', mtInformation, [mbOK],0);

this is work good and update all data as well , BUT i wanted to check Username field if its duplicated or already exist into other rows in the the same updated row to be sure that cannot use other usernames by changing username Feild

[mrmarman]

Hi,

- First you need the registration process. Even before all. ( connection or update sql ext. )
. - Check if user registered. ( SELECT sql )
. - SignUp as new user ( INSERT INTO sql )

- if you want automatically change UserName with trailing any numbers (1) (2) ext. then make a procedure for this with the squence I wrote above.

. - if user wants update his/her information then execute a UPDATE sql

[mia]

yes i already did that but when i try to updated username i got user already exist i must make security check for that to disallow other users to add other username in use

[mrmarman]

- Let me ask again, which SQL says you that user already exist ? is UPDATE sql ?
- Did they ( "id" and/or "uname" fields ) declare as UNIQUE ?

[mia]

Yes id/uname is unique in mysql structure and here what iam try to do

Kod: Tümünü seç

if txtUsername.Text <> '' then
begin
  connectionmo.userslq.Close;
  connectionmo.userslq.SQL.Clear;
  connectionmo.userslq.SQL.Add('SELECT * FROM `users` WHERE `username` = "'+ trim(txtUsername.Text) +'"');
  connectionmo.userslq.Open;
  if not connectionmo.userslq.IsEmpty then
 begin
 MessageDlg('UserName is already Exist', mtInformation, [mbOK],0);
 exit;
 end else
begin
connectionmo.userslq.Close;
connectionmo.userslq.SQL.Clear;
connectionmo.userslq.SQL.Add('UPDATE `users` SET `username` = :uname, `password` = :pass WHERE `users`.`id` = :id;');
connectionmo.userslq.ParamByName('id').AsString := trim(idu.Text);
connectionmo.userslq.ParamByName('uname').AsString := trim(txtUsername.Text);
connectionmo.userslq.ParamByName('pass').AsString := trim(txtUserPass.Text);
connectionmo.userslq.ExecSQL;
connectionmo.userslq.Close;
MessageDlg('user has been updated', mtInformation, [mbOK],0);
end;
end;

[mrmarman]

Now I see....
- In this case you don't need UPDATE, you need INSERT a new user, I guess.

- How do you know if UID exists which refers username in table ? May be yes may be not...
- UPDATE sql script never works if UID not exists on the table records in this stuation.

- IMHO you need a flow chart what to do when the user do something.

This is wokkflow example

Kod: Tümünü seç

(1) User want to login UserName = 'mia', Pass = 'miapass', UID = '1234'
(2)   Check records for 'mia' exists with own password 'miapass' been signed up. 
(3)      if exists UPDATE record with new UID with new socket handle or your own sessionID ext. ext.
(4)      if not exists INSERT new record name, password, UID ----  CONNECTED ---  END TRANSACTION
(5)   if Password wrong AND same USER then say 'user "mia" exists. Please choose another name' ---- DISCONNECTED --- END TRANSACTION

[mia]

so i possibly can feel safe because unique field will not insert any data as ex username , ok i got it . but can i detect mysql error like if record have error show error message ? i mean if i try to update duplicated name this must be an error wil raised

[mrmarman]

All my respect, you dont need to detect raised error.
- Because you always know there is no way to duplicate record, so you might be checked before like below :

Kod: Tümünü seç

function IsUserExist( strUser: String; SQLConnection:TSQLConnection ): boolean;
begin
  With TSQLQuery.Create(nil) do
  begin
    SQLConnection := SQLConnection;
    SQL.Clear;
    SQL.Add('SELECT Count(*) as UserCount FROM ''users'' WHERE ''username'' = :uname');
    ParamByName('uname').AsString := trim(strUser);
    Active := True;
    Result := FieldByName( 'UserCount' ).AsInteger > 0;
    Active := False;
    Free;
  end;
end;

Usage :

Kod: Tümünü seç

procedure TForm1.BitBtn1Click(Sender: TObject);
begin
  if IsUserExist( trim(txtUsername.Text), connectionmo.userslq.SQLConnection ) then
  begin
    MessageDlg( 'User allready Exists.. Please choose another name...', mtWarning, [mbOk], 0 );
    Exit;
  end;
  // ...
  // ...
  // follows other operations..

- if user exists on table records (above function will return boolean result) then check password to know if the same user want to sign in back. Or new another user wants but not know is there a user name which he/she wants...



EDIT : Result must be greater than 0 for user exists.. I'm sorry to late.

Kod: Tümünü seç

    Result := FieldByName( 'UserCount' ).AsInteger > 0;

[mia]

i really thank you mrmarman for your great help .