[echira]

mmmm just some random things i used that made it easy for fixing/updating your databases while using a website like ROempire.com (such as fixing monster drops, etc)

this is a query i used when finding items or their database names when searching for their itemID (IID) (ROempire item search)

specify all the itemID's the monster drops in the 's and then hit execute (query analyzer) and it'll bring back all the table information from the correct tables, and only tables where it's valid.

Quote

--declare variables
declare @iid int declare @iid2 int declare @iid3 int declare @iid4 int declare @iid5 int declare @iid6 int declare @iid7 int declare @iid8 int

--set item ids to search for. searches for max of 8 at a time because... a mob can drop up to 8 things.
set @iid = '7146'
set @iid2 = '505'
set @iid3 = '999'
set @iid4 = '984'
set @iid5 = '985'
set @iid6 = '607'
set @iid7 = '608'
set @iid8 = '504'

--query.
IF EXISTS ( select ID from event where id=@iid OR id=@iid2 OR id=@iid3 OR id=@iid4 OR id=@iid5 OR id=@iid6 OR id=@iid7 OR id=@iid8  )
select * from event where id=@iid OR id=@iid2 OR id=@iid3 OR id=@iid4 OR id=@iid5 OR id=@iid6 OR id=@iid7 OR id=@iid8
IF EXISTS ( select ID from heal where id=@iid OR id=@iid2 OR id=@iid3 OR id=@iid4 OR id=@iid5 OR id=@iid6 OR id=@iid7 OR id=@iid8  )
select * from heal where id=@iid OR id=@iid2 OR id=@iid3 OR id=@iid4 OR id=@iid5 OR id=@iid6 OR id=@iid7 OR id=@iid8
IF EXISTS ( select ID from armor where id=@iid OR id=@iid2 OR id=@iid3 OR id=@iid4 OR id=@iid5 OR id=@iid6 OR id=@iid7 OR id=@iid8  )
select * from armor where id=@iid OR id=@iid2 OR id=@iid3 OR id=@iid4 OR id=@iid5 OR id=@iid6 OR id=@iid7 OR id=@iid8
IF EXISTS ( select ID from armorTB where id=@iid OR id=@iid2 OR id=@iid3 OR id=@iid4 OR id=@iid5 OR id=@iid6 OR id=@iid7 OR id=@iid8  )
select * from armorTB where id=@iid OR id=@iid2 OR id=@iid3 OR id=@iid4 OR id=@iid5 OR id=@iid6 OR id=@iid7 OR id=@iid8
IF EXISTS ( select ID from armorTM where id=@iid OR id=@iid2 OR id=@iid3 OR id=@iid4 OR id=@iid5 OR id=@iid6 OR id=@iid7 OR id=@iid8  )
select * from armorTM where id=@iid OR id=@iid2 OR id=@iid3 OR id=@iid4 OR id=@iid5 OR id=@iid6 OR id=@iid7 OR id=@iid8
IF EXISTS ( select ID from armorMB where id=@iid OR id=@iid2 OR id=@iid3 OR id=@iid4 OR id=@iid5 OR id=@iid6 OR id=@iid7 OR id=@iid8  )
select * from armorMB where id=@iid OR id=@iid2 OR id=@iid3 OR id=@iid4 OR id=@iid5 OR id=@iid6 OR id=@iid7 OR id=@iid8
IF EXISTS ( select ID from armorTMB where id=@iid OR id=@iid2 OR id=@iid3 OR id=@iid4 OR id=@iid5 OR id=@iid6 OR id=@iid7 OR id=@iid8  )
select * from armorTMB where id=@iid OR id=@iid2 OR id=@iid3 OR id=@iid4 OR id=@iid5 OR id=@iid6 OR id=@iid7 OR id=@iid8
IF EXISTS ( select ID from bow where id=@iid OR id=@iid2 OR id=@iid3 OR id=@iid4 OR id=@iid5 OR id=@iid6 OR id=@iid7 OR id=@iid8  )
select * from bow where id=@iid OR id=@iid2 OR id=@iid3 OR id=@iid4 OR id=@iid5 OR id=@iid6 OR id=@iid7 OR id=@iid8
IF EXISTS ( select ID from guest where id=@iid OR id=@iid2 OR id=@iid3 OR id=@iid4 OR id=@iid5 OR id=@iid6 OR id=@iid7 OR id=@iid8  )
select * from guest where id=@iid OR id=@iid2 OR id=@iid3 OR id=@iid4 OR id=@iid5 OR id=@iid6 OR id=@iid7 OR id=@iid8
IF EXISTS ( select ID from bothhand where id=@iid OR id=@iid2 OR id=@iid3 OR id=@iid4 OR id=@iid5 OR id=@iid6 OR id=@iid7 OR id=@iid8  )
select * from bothhand where id=@iid OR id=@iid2 OR id=@iid3 OR id=@iid4 OR id=@iid5 OR id=@iid6 OR id=@iid7 OR id=@iid8
IF EXISTS ( select ID from card where id=@iid OR id=@iid2 OR id=@iid3 OR id=@iid4 OR id=@iid5 OR id=@iid6 OR id=@iid7 OR id=@iid8  )
select * from card where id=@iid OR id=@iid2 OR id=@iid3 OR id=@iid4 OR id=@iid5 OR id=@iid6 OR id=@iid7 OR id=@iid8
IF EXISTS ( select ID from weapon where id=@iid OR id=@iid2 OR id=@iid3 OR id=@iid4 OR id=@iid5 OR id=@iid6 OR id=@iid7 OR id=@iid8  )
select * from weapon where id=@iid OR id=@iid2 OR id=@iid3 OR id=@iid4 OR id=@iid5 OR id=@iid6 OR id=@iid7 OR id=@iid8
IF EXISTS ( select ID from special where id=@iid OR id=@iid2 OR id=@iid3 OR id=@iid4 OR id=@iid5 OR id=@iid6 OR id=@iid7 OR id=@iid8  )
select * from special where id=@iid OR id=@iid2 OR id=@iid3 OR id=@iid4 OR id=@iid5 OR id=@iid6 OR id=@iid7 OR id=@iid8

oh another one... it pretty much makes dovac's "make account" query in the user guide simpler, but that's my job! make shit easier!

Quote

--declare vars
declare @id varchar(24)
declare @pass varchar(24)
declare @email varchar(50)
declare @gender tinyint
declare @aid int

--set vars
set @id   =  ''  -- up to 24 chars for login
set @pass   =  ''  -- up to 24 chars for password
set @email   =  ''  -- up to 50 chars for email
set @gender  = ''  -- 0 for female, 1 for male

--login query (user/pass/banned?)
INSERT INTO login (ID, passwd, grade, isConfirmed) VALUES (@id,@pass,1,3)

--get AID from login and set as var
SET @aid = 'SELECT AID from login where ID = @id'

--account query (gender)
INSERT INTO account (AID, Name, RegNum, zipcode, sex, Email, News, nation) VALUES (@aid, @id, 1, '00000', @gender, @email, 0, 'NA')

searching for an item that a mob drops?

Quote

--declare variables
declare @drop varchar(24)

--put drop name in the 's
--wildcards supported, use % for wildcard.
SET @drop = '%apple'

--search queries
IF EXISTS ( SELECT item1 from monmakingitem where item1 LIKE @drop )
SELECT name, item1, percent1 FROM monmakingitem WHERE item1 LIKE @drop
IF EXISTS ( SELECT item2 from monmakingitem where item2 LIKE @drop )
SELECT name, item2, percent2 FROM monmakingitem WHERE item2 LIKE @drop
IF EXISTS ( SELECT item3 from monmakingitem where item3 LIKE @drop )
SELECT name, item3, percent3 FROM monmakingitem WHERE item3 LIKE @drop
IF EXISTS ( SELECT item4 from monmakingitem where item4 LIKE @drop )
SELECT name, item4, percent4 FROM monmakingitem WHERE item4 LIKE @drop
IF EXISTS ( SELECT item5 from monmakingitem where item5 LIKE @drop )
SELECT name, item5, percent5 FROM monmakingitem WHERE item5 LIKE @drop
IF EXISTS ( SELECT item6 from monmakingitem where item6 LIKE @drop )
SELECT name, item6, percent6 FROM monmakingitem WHERE item6 LIKE @drop
IF EXISTS ( SELECT item7 from monmakingitem where item7 LIKE @drop )
SELECT name, item7, percent7 FROM monmakingitem WHERE item7 LIKE @drop
IF EXISTS ( SELECT item8 from monmakingitem where item8 LIKE @drop )
SELECT name, item8, percent8 FROM monmakingitem WHERE item8 LIKE @drop

>_<


searching for items but only know the gist of the name and not all?

Quote

--declare variables
declare @drop varchar(24)

--put drop name in the 's
--wildcards supported, use % for wildcard.
SET @drop = '%ygg%'

--search queries
IF EXISTS ( select [name] from event where [name] LIKE @drop )
select * from event where [name] LIKE @drop
IF EXISTS ( select [name] from heal where [name] LIKE @drop )
select * from heal where [name] LIKE @drop
IF EXISTS ( select [name] from armor where [name] LIKE @drop )
select * from armor where [name] LIKE @drop
IF EXISTS ( select [name] from armorTB where [name] LIKE @drop )
select * from armorTB where [name] LIKE @drop
IF EXISTS ( select [name] from armorTM where [name] LIKE @drop )
select * from armorTM where [name] LIKE @drop
IF EXISTS ( select [name] from armorMB where [name] LIKE @drop )
select * from armorMB where [name] LIKE @drop
IF EXISTS ( select [name] from armorTMB where [name] LIKE @drop )
select * from armorTMB where [name] LIKE @drop
IF EXISTS ( select [name] from bow where [name] LIKE @drop )
select * from bow where [name] LIKE @drop
IF EXISTS ( select [name] from guest where [name] LIKE @drop )
select * from guest where [name] LIKE @drop
IF EXISTS ( select [name] from bothhand where [name] LIKE @drop )
select * from bothhand where [name] LIKE @drop
IF EXISTS ( select [name] from card where [name] LIKE @drop )
select * from card where [name] LIKE @drop
IF EXISTS ( select [name] from weapon where [name] LIKE @drop )
select * from weapon where [name] LIKE @drop
IF EXISTS ( select [name] from special where [name] LIKE @drop )
select * from special where [name] LIKE @drop

>

Quote

declare @search varchar(50) declare @result varchar(50) --declare variables

set @search = 'gm-echira' --place the charactername you're looking to look up the guild for

set @result = ( --search databases for guildname based on guilded character
SELECT    [character].[dbo].[GuildInfoDB].[Name] AS [GuildName]
FROM        [character].[dbo].[GuildInfoDB]
WHERE    [character].[dbo].[GuildInfoDB].[GDID] =
                          (SELECT    [character].[dbo].[GuildMInfo].[GDID]
                            FROM          [character].[dbo].[GuildMInfo]
                            WHERE      ([character].[dbo].[GuildMInfo].[CharName] = @search)))

if (@result IS NULL)
begin set @result = 'Character not guilded' SELECT @result as [Not Guilded] end --if not guilded, state

else
begin SELECT @result as [Guild Name] end --otherwise, state result

--enjoy
--echira

:inlove:

This post has been edited by echira: 20 October 2004 - 04:14 AM

[Nyarlathotep]

Thx So Much!

Very Useful!

But im looking for a command to MODIFY an alreading existing ENTRIE in a "x" table...

Its for updating tables...

Any Idea?

[equinoz]

UPDATE x SET y = 'z'

x = table name
y = column name
z = new value

[echira]

@Nyarlathotep
http://forum.asb-sak...showtopic=27281

i'm going to be adding an "UPDATE" section for editing SQL queries soon.

[Nyarlathotep]

Thx a lot!!

Nice Guide BTW :lol:

<_<

[andz]

:lol: Great Work

[klowda]

Umm, when I'm done inputing the the Account Query where do I save it? :D

I tried a few stuff and got it to work :)

This post has been edited by klowda: 12 August 2006 - 11:19 AM