How to use ACCOUNT_TBL or ACCOUNT_TBL_DETAIL to create a email confirmation?

[ronscript]

I don't want to create another table because of redundancy. The ACCOUNT_TBL_DETAIL already has a email field so I have an idea to use it to have an email confirmation. Can anyone help me what field to use? THanks

 

[Lodelight]

This is already done in the sapphire site, but is handled by WEBSITE_DBF rather then ACCOUNT_DBF.

 

[NickHough]

You can handle it in ACCOUNT_TBL, you've got to add the following columns to your table.

Activated -> Bit/Int
ActivationKey -> varchar(32) or varchar(128) -------- Depending on Hash used to generate Key

Then on your website, send an email with the activation key to the email address they registered with, just do a SHA256 or MD5 hash on the email and account. Make sure to default Activated to 0 to prevent multiple clicks of the activation key.

Then on the website again you need a page that checks the entire account table for the ActivationKey and check if Activated = 0, if it is 0 and the ActivationKey matches, just changed Activated to 1

Then in LOGIN_STR (Procedure) change the following line

IF EXISTS(SELECT a.account FROM ACCOUNT_TBL a,ACCOUNT_TBL_DETAIL b 
                  WHERE a.account = b.account AND a.account = @iaccount )

to

IF EXISTS(SELECT a.account FROM ACCOUNT_TBL a,ACCOUNT_TBL_DETAIL b 
                  WHERE a.account = b.account AND a.account = @iaccount AND a.Activated = 1 )

This will basically prevent logging in if Activated is not equal to 1


P.S Please note that you will need to use php's Mail or a equivalent to sent the email. The link below is to the phpmailer script i use.
https://www.sitepoint.com/sending-emails-php-phpmailer/

 

[ronscript]

That's what I need thanks for the best feedback. Right now I created a separated website table, but when I found out your soulution I will use again the account_tbl instead thanks! Because my only concern is there are redundaant datas

You can handle it in ACCOUNT_TBL, you've got to add the following columns to your table.

Activated -> Bit/Int
ActivationKey -> varchar(32) or varchar(128) -------- Depending on Hash used to generate Key

Then on your website, send an email with the activation key to the email address they registered with, just do a SHA256 or MD5 hash on the email and account. Make sure to default Activated to 0 to prevent multiple clicks of the activation key.

Then on the website again you need a page that checks the entire account table for the ActivationKey and check if Activated = 0, if it is 0 and the ActivationKey matches, just changed Activated to 1

Then in LOGIN_STR (Procedure) change the following line

IF EXISTS(SELECT a.account FROM ACCOUNT_TBL a,ACCOUNT_TBL_DETAIL b 
                  WHERE a.account = b.account AND a.account = @iaccount )

to

IF EXISTS(SELECT a.account FROM ACCOUNT_TBL a,ACCOUNT_TBL_DETAIL b 
                  WHERE a.account = b.account AND a.account = @iaccount AND a.Activated = 1 )

This will basically prevent logging in if Activated is not equal to 1


P.S Please note that you will need to use php's Mail or a equivalent to sent the email. The link below is to the phpmailer script i use.
https://www.sitepoint.com/sending-emails-php-phpmailer/

 

[NickHough]

That's what I need thanks for the best feedback. Right now I created a separated website table, but when I found out your soulution I will use again the account_tbl instead thanks! Because my only concern is there are redundaant datas

If you wish too, you can have a php script run every day to remove accounts that haven't click the link for say 30 days.

But you would have to add another column called DateSent or something. That way if Activated = 0 and DateSent > 30 days it will delete all account data

 

[ronscript]

I guess date registered will be fine for that? Hey can I add you as friend?

If you wish too, you can have a php script run every day to remove accounts that haven't click the link for say 30 days.

But you would have to add another column called DateSent or something. That way if Activated = 0 and DateSent > 30 days it will delete all account data

I also wanted to know what is the usage and definition of gamecode, m_chLoginAuthority, secession on actual game.

 

[NickHough]

Never really used Gamecode, m_chLoginAuthority is entire game authority across all characters (GM status), Session is probably session ID for website

 

[ronscript]

I wonder why the structure of the database is so bad, I wish I could analyze the whole sourcee so I could re design the whole structure.

Never really used Gamecode, m_chLoginAuthority is entire game authority across all characters (GM status), Session is probably session ID for website

 

[NickHough]

Ow re-designing the structure would be a waste of time, it wouldn't boost performance at all.

I mean i've got MSSQL enterprise 2012 and it works absolutely fine without many delays. Nothing else could realistically be done, except removing all logging as it takes up a ton of space

 

[Cuvvvie]

Another thing you could do is not do any database insertions until they click the email confirmation link, besides making sure that the account name is available. If you have a separate database or table for the website then you can store the temporary/non-confirmed login details there, then the email confirmation link would execute the actual account creation script (or embed the account name/password in the confirmation link itself securely to keep no data server side).

This way theres no account created for non-confirmed emails at all, so you don't need to prune non-confirmed accounts after X days.

 

[NickHough]

Another thing you could do is not do any database insertions until they click the email confirmation link, besides making sure that the account name is available. If you have a separate database or table for the website then you can store the temporary/non-confirmed login details there, then the email confirmation link would execute the actual account creation script (or embed the account name/password in the confirmation link itself securely to keep no data server side).

This way theres no account created for non-confirmed emails at all, so you don't need to prune non-confirmed accounts after X days.

Although true, the OP said he wanted to use ACCOUNT_TBL to do this. However i do recommend your way, but again you don't need a whole database like some servers, for this, you just need a new table in ACCOUNT_DBF -> They still relate to the account database if you think about it as they are unverified accounts. Table name UNVERIFIED_ACCOUNTS_TBL, this will store the hashed link, the account name, and all details inserted from the website.

I would like to also point out, some games actually check if the account exists but is unverified. To do this you'd need to do 2 SQL's (for the nooby to tsql) or a joined sql statement to check both tables at the same time for an occurance of account, whereas with the original way you'd just be looking for that single column so it is up to the OP on how he wishes to do it. I'm no noob to tsql, but i personally prefer doing it in 2 statements to make it more readable by noobs

 

[ronscript]

I've successfuly updated my SP and tables, without adding new databases. Thanks for the help

Although true, the OP said he wanted to use ACCOUNT_TBL to do this. However i do recommend your way, but again you don't need a whole database like some servers, for this, you just need a new table in ACCOUNT_DBF -> They still relate to the account database if you think about it as they are unverified accounts. Table name UNVERIFIED_ACCOUNTS_TBL, this will store the hashed link, the account name, and all details inserted from the website.

I would like to also point out, some games actually check if the account exists but is unverified. To do this you'd need to do 2 SQL's (for the nooby to tsql) or a joined sql statement to check both tables at the same time for an occurance of account, whereas with the original way you'd just be looking for that single column so it is up to the OP on how he wishes to do it. I'm no noob to tsql, but i personally prefer doing it in 2 statements to make it more readable by noobs