Some SQL / db Questions...

[Torankusu]

Originally Posted by fowlplay4

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();
} 


oh wow, thanks a lot.

I feel better knowing that what I wanted to do is in fact possible, and will post some results in a few days when I have a day off to actually write up the functions.

Was not sure exactly how to store the information
such as
-modifications, stages, enchantments, etc
that would be unique to:
-a specific character (or even just account..)
-and a specific item a character owned
(even if they owned multiples of that exact item, that did not share those same modifications, stages, and/or enchantments)

but this gives me a lot of information to structure around. :]

thanks a lot.

I should have asked outright, but is there no real downside to storing information in the column for modifications or enchantments like this? :

ENCHANTMENTS
{str,1},{dex,2}

and accessing it similar to an array:

example = {str,1}, {dex,2}

echo(example[0][0]); // str
echo(example[0][1]); // 1
echo(example[1]); // "dex,2"

I won't be able to write anything up and test it until I have a day off, but I'm trying to gather enough info to set up something the best and most correct way, and so far you've been a great help.

thanks again.