Some SQL / db Questions...

[fowlplay4]

Originally Posted by Torankusu

wouldnt this essentially mean that all items that the character owns with an item id of 5 would take on these chsracteristics?

Yep, using a separate table would be the more 'normalized' way to do it though. You could have a 100 enchantments for the same item if you wanted.

The tricky part is deciding how you want to query and load the information. I would probably do:

1. Load characteritem data
2. Load and apply enchantments using array of character item ids

I.e.

SELECT CharacterItems.*, Items.*
FROM CharacterItems, Items
WHERE CharacterID = 1 AND Items.ID = CharacterItems.ItemID;

SELECT CharacterItemID, Enchantment, EnchantmentValue
FROM Enchantments
WHERE CharacterItemID IN (1, 2, 3)

I wrote a function that generates an escaped IN clause with an array of data:

PHP Code:

// usage: generateInClause("column", {1,2,3});
public function generateInClause(column, items) {
  temp.clause = column @ " IN (";
  temp.last   = items.size() - 1;
  for (temp.i = 0; temp.i < items.size(); temp.i++) {
    temp.item = items[temp.i];
    if (temp.i == temp.last) {
      temp.clause @= "'" @ escapestring2(temp.item) @ "'";
    } else {
      temp.clause @= "'" @ escapestring2(temp.item) @ "',";
    }
  }
  temp.clause @= ")";
  return temp.clause.link();
}