[Release]Give DP through CharName SQL

01/10/2011 15:26 RebeccaBlack#1

I know this script is kind of silly, and probably badly coded, but I wanted to try. It works, so I thought I'd give it out to others. ._.

Code:

USE PS_GameData
DECLARE
@CharName varchar(30),
@UserUID int,
@UserID varchar(18),
@Point int,
@Points int,
@Point2 int

--Change "Char Name" to the name of the Character who you're giving DP to
SET @CharName = 'CharName'
--Change "PointsToGive" to the amount of DP you want to give to that Character
SET @Point = 'PointsToGive'

SELECT @UserUID=U.UserUID, @UserID=U.UserID
	FROM PS_GameData.dbo.Chars AS C INNER JOIN PS_UserData.dbo.Users_Master AS U
	ON C.UserUID = U.UserUID AND C.UserID = U.UserID
	WHERE C.CharName = @CharName AND C.Del=0;
IF @@ROWCOUNT=0
	 BEGIN
	  PRINT 'Character was not found.';
	  return;
	 END
ELSE 
BEGIN
SET @Point2=(SELECT Point FROM PS_UserData.dbo.Users_Master WHERE UserUID=@UserUID)
UPDATE PS_UserData.dbo.Users_Master
SET Point=(Point+@Point)
WHERE UserUID=@UserUID
SET @Points=(SELECT Point FROM PS_UserData.dbo.Users_Master WHERE UserUID=@UserUID)
PRINT @CharName + 's current amount of points: ' + convert(varchar(30), @Point2) + '
Account belonging to - ' + @CharName + ' - had its points updated. 
Account name connected to the toon was: "' + @UserID + '"
The accounts current amount of points: ' + convert(varchar(30), @Points)
END

If anything is wrong with it, let me know. Still learning. ._.

01/10/2011 15:27 .Scy#2

is this php or lua?

01/10/2011 15:31 RebeccaBlack#3

It's SQL o.o;
I've no idea how to code PHP sadly. Still have a lot to learn. :\

01/12/2011 11:41 Danco1990#4

Not a bad concept, but when people donate, don't they give you the userid? It does come in handy if you get a charid instead of the userid, but normally i just run a select on that char to find out the userid.

01/12/2011 12:03 RebeccaBlack#5

Yeah they do give their UserID when they donate. D:
I think this was for when we used to have item in game that you could trade for AP (Before this was done automatically)
That, and I wanted to test myself on what I could do

01/12/2011 18:13 Bаne#6

Quote:

Originally Posted by tnelis

It's SQL o.o;
I've no idea how to code PHP sadly. Still have a lot to learn. :\

Would you like some help??

-Bane

01/13/2011 10:38 RebeccaBlack#7

Help with? D:
If you want to teach, I'm more than willing to learn. =p

01/25/2011 02:33 kalle801#8

I got a failure here.

can u help me pls?

This is the message:

Quote:

Msg 468, Level 16, State 9, Line 15
Cannot resolve the collation conflict between "Chinese_PRC_Stroke_CI_AS" and "Korean_Wansung_CI_AS" in the equal to operation.

01/25/2011 02:55 betalfa#9

...
SELECT @UserUID=U.UserUID, @UserID=U.UserID
FROM PS_GameData.dbo.Chars AS C INNER JOIN PS_UserData.dbo.Users_Master AS U
ON C.UserUID = U.UserUID AND C.UserID = U.UserID
WHERE C.CharName = @CharName AND C.Del=0;
...
AND C.UserID = U.UserID dont put it on the code.. u are compare 2 times da same thing

01/25/2011 08:23 RebeccaBlack#10

Quote:

Originally Posted by kalle801

I got a failure here.

can u help me pls?

This is the message:

We got this error recently when we remade our PS_UserData.dbo.Users_Master table. Check the collation on your UserID column.

Use google on how to do this, I don't want to make a mistake and tell you the wrong way. Be sure to backup before trying anything!

01/25/2011 17:01 kalle801#11

Quote:

Originally Posted by betalfa

...
SELECT @UserUID=U.UserUID, @UserID=U.UserID
FROM PS_GameData.dbo.Chars AS C INNER JOIN PS_UserData.dbo.Users_Master AS U
ON C.UserUID = U.UserUID AND C.UserID = U.UserID
WHERE C.CharName = @CharName AND C.Del=0;
...
AND C.UserID = U.UserID dont put it on the code.. u are compare 2 times da same thing

Thank you, it Works. ;)

01/25/2011 17:06 kalle801#12

maybe is it Possible to write a php Script, where i only have to insert a CharName, and click "Send" and after send the ap points are instant on this account?

With folowing script:

Quote:

USE PS_GameData
DECLARE
@CharName varchar(30),
@UserUID int,
@UserID varchar(18),
@Point int,
@Points int,
@Point2 int

--Change "Char Name" to the name of the Character who you're giving DP to
SET @CharName = '%'
--Change "PointsToGive" to the amount of DP you want to give to that Character
SET @Point = '12500'

SELECT @UserUID=U.UserUID, @UserID=U.UserID
FROM PS_GameData.dbo.Chars AS C INNER JOIN PS_UserData.dbo.Users_Master AS U
ON C.UserUID = U.UserUID
WHERE C.CharName = @CharName AND C.Del=0;
IF @@ROWCOUNT=0
BEGIN
PRINT 'Character nicht gefunden.';
return;
END
ELSE
BEGIN
SET @Point2=(SELECT Point FROM PS_UserData.dbo.Users_Master WHERE UserUID=@UserUID)
UPDATE PS_UserData.dbo.Users_Master
SET Point=(Point+@Point)
WHERE UserUID=@UserUID
SET @Points=(SELECT Point FROM PS_UserData.dbo.Users_Master WHERE UserUID=@UserUID)
PRINT @CharName + 's Derzeitigen Punkte: ' + convert(varchar(30), @Point2) + '
Der Account - ' + @CharName + ' - hat seine Punkte bekommen.
Verbundener Accountname zum Charakter: "' + @UserID + '"
Aktuelle Punkte des Accounts: ' + convert(varchar(30), @Points)
END