Best way to do this. - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Development Tools

ASP.NET

Best way to do this.

Author

Topic

Eagle_f90
Constraint Violating Yak Guru

424 Posts

Posted - 2002-12-22 : 12:55:47

I need help, I know how to do what I want to do but it is very sloppy and slow. So if anyone knows a good way to do this please let me know.

I have 5 columns each named itemx (where x is 1-5 depending on the column) What I want to do is when a player sells and item and it is in say column 2 and he has 4 item for it to set column 2 to have the info of column 3 and column 3 for the info of column 4 and set column 4 to "None". I need to do this for each column and have it check to see if they have 4 items of only 2 or all 5. Thanks.

I need to do this in ASP (VBscript) and it is a website interface.

--
For those with wings, fly to your dreams

It is all or nothing there is no in-between

Remember who I was... For me

ValterBorges
Master Smack Fu Yak Hacker

1429 Posts

Posted - 2002-12-22 : 16:58:31

Is this what you're trying to do.
http://www.sqlteam.com/Forums/topic.asp?TOPIC_ID=22413

Don't understand.

quote:

when a player sells and item and it is in say column 2 and he has 4 item for it

quote:

I need to do this for each column and have it check to see if they have 4 items of only 2 or all 5.

Maybe you can post an example.

Edited by - ValterBorges on 12/22/2002 17:47:40

Eagle_f90
Constraint Violating Yak Guru

424 Posts

Posted - 2002-12-23 : 09:05:17

Ok, I will try to explain it a litter better. I have a table called items, with 5 columns (item1, item2, item3, item4, item5). Now I have a script on my web site that lets player sell his items. Now lest say he has 3 items so columns 1-3 would have some name in there and column 4 and 5 would have "N/A" as there value. Now say that same player decides to sell an item. Well that item is in column 2 (item2 is the name of the column) well since he has an item in column 3 I need to move that item name to column 2 and set column 3 to "N/A". Now I need a script that can do that but also check to make sure he has no items in column 4 and 5. And if he does move the names down one. (So name in column 4 becomes name in coulmn3) and then set the last column that had an item in it to "N/A"

Visual Example:
Item1: 1
Item2: 2
Item3: 3
Item4: N/A
Item5: N/A
Player sells item2 so table should now look like this:
Item1: 1
Item2: 3
Item3: N/A
Item4: N/A
Item5: N/A
Must also work in situations like this:
Item1: 1
Item2: 2
Item3: 3
Item4: 4
Item5: N/A
Player sells item2 so table should now look like this:
Item1: 1
Item2: 3
Item3: 4
Item4: N/A
Item5: N/A

Hope that helps.

--
For those with wings, fly to your dreams

It is all or nothing there is no in-between

Remember who I was... For me

ValterBorges
Master Smack Fu Yak Hacker

1429 Posts

Posted - 2002-12-23 : 09:10:48

When a players sells an item you can call a stored procedure that implements Rob's idea in the link above and just have your asp page refresh to show the modified data.

Edited by - ValterBorges on 12/23/2002 09:11:05

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2002-12-23 : 09:15:09

You could also try normalizing your database (someone had to say it!).

Create a table of "PlayerItems"

PlayerID, ItemID, Count

where PlayerID and ItemID are the primary key. When someone buys an itemId, you add a record to this table with a count of 1 or incremement Count by 1 if it is already there. Opposite for the sale of an item (if count >1 then decrement count by 1, otherwise delete the record).

A table of ItemID's list all of the items players can have.

- Jeff

Eagle_f90
Constraint Violating Yak Guru

424 Posts

Posted - 2002-12-23 : 09:27:46

quote:

You could also try normalizing your database (someone had to say it!).

Create a table of "PlayerItems"

PlayerID, ItemID, Count

where PlayerID and ItemID are the primary key. When someone buys an itemId, you add a record to this table with a count of 1 or incremement Count by 1 if it is already there. Opposite for the sale of an item (if count >1 then decrement count by 1, otherwise delete the record).

A table of ItemID's list all of the items players can have.

- Jeff

The table I am talking about is a stand alone table, the only info in it is playerid, item1-5. And I am not sure what your geting at with the count or how that would work.

--
For those with wings, fly to your dreams

It is all or nothing there is no in-between

Remember who I was... For me

ValterBorges
Master Smack Fu Yak Hacker

1429 Posts

Posted - 2002-12-23 : 09:42:53

Eagle_f90,

Jay is trying to tell you that if you kept a table with the following:


UserID ItemID Count
1      1      1
1      2      1
1      3      1
1      4      1
1      5      1
2      1      1
2      2      1
2      3      1
2      4      1
2      5      1

Your data would be normalized and you could write simple queries to
manage your data.

To display your data you would use a crosstab query.
http://www.sqlteam.com/item.asp?ItemID=2955

This allows you to have more than 5 items or different groups of items
for different user without having to modify code.

When player 1 removes item2 you would do an update query

Update PlayerItems
SET Count = Count - 1
WHERE UserID = @UserID and ItemID = @ItemID

The count column is there in case a user is able to purchase more than 1 quantity for each item. In your case it might not be necessary but it works just the same so why not plan for the future.

Edited by - ValterBorges on 12/23/2002 09:45:37

Eagle_f90
Constraint Violating Yak Guru

424 Posts

Posted - 2002-12-23 : 09:53:29

In the RPG I am making the player is only able to cary a max of 5 items at anyone time. That is why I have the 5 coulmns could I still do this with what you guys are saying?

--
For those with wings, fly to your dreams

It is all or nothing there is no in-between

Remember who I was... For me

ValterBorges
Master Smack Fu Yak Hacker

1429 Posts

Posted - 2002-12-23 : 11:35:57

Yes, just make sure that count is only 0 or 1.

Eagle_f90
Constraint Violating Yak Guru

424 Posts

Posted - 2002-12-23 : 11:45:36

Ok, I think I know now what I need to do. Thanks for all the help guys.

--
For those with wings, fly to your dreams

It is all or nothing there is no in-between

Remember who I was... For me

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources