Need a second pair of eyes..

Okay I have two table: items and guildarmory

the items table are the items you own (weapons and armor) it is 'o' if you don't have anything there. slota-slotf

guildarmory table is the weapons and armor it owns. o means nothing there. slota-slotz

Well....

I'm making a query so you can donate to the armory.. so far I have:

<form action="guildarmoryact.cfm?guildid=<cfoutput>#url.guildid#</cfoutput>" method="post">
<cfquery name="getweps" datasource="userlogin">
SELECT * from Items
where user_ID = '#session.user_ID#'
</cfquery>
<SELECT SIZE="1" NAME="donate">
<cfoutput query="getweps">

<CFIF slota is not 'o'>
<OPTION VALUE="#SlotA#">#SlotA#</OPTION>
</CFIF>

all the way through slotf

</cfoutput>

Then the action page is:

<cfquery name="where" datasource="userlogin">
SELECT * from GuildArmory
where guildid = '#URL.guildid#'
</cfquery>
<cfoutput query="where">
<cfif slota is 'o'>

<cfquery name="updatearmory" datasource="userlogin">
update Guildarmory
set SlotA = '#form.donate#'
where guildid = '#URL.guildid#'


</cfquery>
</cfif>


Now.. how in the world would i write a query to subtract it from your items since you donated it? How do i know what slot to subtract it from? it's probably something simple but ive been at iit a while and just need a second pair of eyes please!

Well even though this really is an SQL question, the answer is pretty easy.

I am assumming each table has a primary key. So once you have selected your item from the 'ITEM' table and added it to the armory table, you should use the ITEM primary key to do an update statement to set whatever columns to 'o' to show you no longer own it; using the primary key in your where clause.

The table is set up like this:
ID - primary
User_ID - 1
SlotA - Weapon
SlotB - o
SLotC - o
SlotD - o
SlotE - o
SlotF - o

When I add it i'm just taking the "weapon" name and not really what slot it came from so i dont know what slot to set to 'o'. Maybe I misunderstand what you're saying. Can i get an example?

Repeated columns like that are a red flag that something in your design is wrong. What happens if you want to allow a user more weapons, do you keep adding more columns...'WeaponG...WeaponT..."? Also unless all users have all weapons your going to have a ton of empty columns.

What you really want to do is have 3 tables...a user table, a weapon table, and a table that relates users to their weapons.

table user
userID,userName

table weapons
weaponID, weaponName

table users_weapons
userID, weaponID

This way if you have a userID, you can query users_weapons to determine what weaponID's they are associated with.

Well actually they are only allowed to store a certain amount of weapons. Most people generally have 6, but guilds have way more then that, so they are having to get rid of some sometimes. Porbably would be better your way now that I think about it but would it be impossible to do it my way? Not planning on re-coding it all and it works fine for me.

You're of course free to do it however you want, but this very likely will cause headaches for you down the line.

But to answer the question, no, there's no way to know which slot the weapon came from unless you explicitly keep track of it along with the weapon. An alternative would be to query the table for a user and loop through the weapons for that user until you find which slot the current weapon is in.

kiteless it quite right, you really don't want to be designing your database that way (even if you have a limited number of items). Bust out your basic database book and look up 3rd level of normalization, that will put your database on the right path (and save you HUGE headaches in the future.