Pages: [1] :: one page |
|
Author |
Thread Statistics | Show CCP posts - 0 post(s) |
Ochawe Bake
Minmatar The Baked Group
|
Posted - 2010.07.19 19:27:00 -
[1]
I see from the item database there is 11 Categories. Is there anything in the invTypes tables to separate these in each category.
|
Tonto Auri
Vhero' Multipurpose Corp
|
Posted - 2010.07.19 19:37:00 -
[2]
Originally by: Ochawe Bake I see from the item database there is 11 Categories.
SELECT COUNT(*) FROM invcategories i WHERE i.`published` = 1;
22
Quote: Is there anything in the invTypes tables to separate these in each category.
You're looking into wrong direction. invGroups is the table you want. Then the groupID in invTypes, indeed. -- Thanks CCP for cu |
Ochawe Bake
Minmatar The Baked Group
|
Posted - 2010.07.19 20:16:00 -
[3]
Thanks, but I know I can do it that way (guess I should of said that). I wanted to do 1 find to return lets say all ships, then export in my own DB ship table and then the rest of the categorizes the same way. It don't look like it can be done that way, so I guess I'll have to do it the long way,
|
Tonto Auri
Vhero' Multipurpose Corp
|
Posted - 2010.07.19 21:33:00 -
[4]
SELECT COUNT(*) FROM `invTypes` i LEFT OUTER JOIN `invGroups` ig ON ig.`groupID` = i.`groupID` LEFT OUTER JOIN `invCategories` ic ON ic.`categoryID` = ig.`categoryID` WHERE ic.`categoryID` = 6 AND i.`published` = 1;
226
Was that hard? -- Thanks CCP for cu |
Ochawe Bake
Minmatar The Baked Group
|
Posted - 2010.07.19 22:31:00 -
[5]
Originally by: Tonto Auri Was that hard?
If ur going to be an ass then don't help me at all. Really man you could of said it a different way. I ****ing sorry I don't know sql as much as you. My god dam BAD !!!
|
Gar Karath
Caldari
|
Posted - 2010.07.19 23:15:00 -
[6]
Generally .. ignore that Troll.
What you want to do is a little bit tricky. Since the groups ( i assume you want the invGroups not invCategories, which is a bit different from the link you gave) are ordered in parent-child relations. So if you want to find every item in a group you'd have to check the parentGroups recursively. MySQL doesn't support recursion to my knowledge.
You can convert the groups to a different relation (take a look for 'nested sets'). If you're interested feel free to contact me, I can support you with a php script to convert the parent-child relation into nested sets.
After the conversion it is pretty easy to select certain groups. (i.e 'All Ships', 'All Frigates', 'All Rigs' etc.)
|
Catari Taga
Centre Of Attention Middle of Nowhere
|
Posted - 2010.07.19 23:24:00 -
[7]
Originally by: Gar Karath Generally .. ignore that Troll.
What you want to do is a little bit tricky. Since the groups ( i assume you want the invGroups not invCategories, which is a bit different from the link you gave) are ordered in parent-child relations. So if you want to find every item in a group you'd have to check the parentGroups recursively. MySQL doesn't support recursion to my knowledge.
You can convert the groups to a different relation (take a look for 'nested sets'). If you're interested feel free to contact me, I can support you with a php script to convert the parent-child relation into nested sets.
After the conversion it is pretty easy to select certain groups. (i.e 'All Ships', 'All Frigates', 'All Rigs' etc.)
To be frank I have no idea what you are talking about, but if you want to filter all ships do as Tonto posted... --
Originally by: CCP the data does not seem to support that polished quality sells better than new features
|
Gar Karath
Caldari
|
Posted - 2010.07.19 23:58:00 -
[8]
Originally by: Catari Taga
Originally by: Gar Karath Generally .. ignore that Troll.
What you want to do is a little bit tricky. Since the groups ( i assume you want the invGroups not invCategories, which is a bit different from the link you gave) are ordered in parent-child relations. So if you want to find every item in a group you'd have to check the parentGroups recursively. MySQL doesn't support recursion to my knowledge.
You can convert the groups to a different relation (take a look for 'nested sets'). If you're interested feel free to contact me, I can support you with a php script to convert the parent-child relation into nested sets.
After the conversion it is pretty easy to select certain groups. (i.e 'All Ships', 'All Frigates', 'All Rigs' etc.)
To be frank I have no idea what you are talking about, but if you want to filter all ships do as Tonto posted...
Actually nevermind my explanation. I was somehow stuck with invMarketGroups and somehow had in mind invGroups was organized the same way.
|
Ochawe Bake
Minmatar The Baked Group
|
Posted - 2010.07.20 11:15:00 -
[9]
Thanks Gar Karath for your reply. I'm only using mysql as a backend so Tonto Auri was no help at all. If he would of read the question right all I wanted to know is anything in the invTypes tables (Not the invGroups table) to separate these 11 categorizes. I did find a way to do this but not as quickly as I wanted to.
|
Gar Karath
Caldari
|
Posted - 2010.07.20 15:20:00 -
[10]
Originally by: Ochawe Bake Thanks Gar Karath for your reply. I'm only using mysql as a backend so Tonto Auri was no help at all. If he would of read the question right all I wanted to know is anything in the invTypes tables (Not the invGroups table) to separate these 11 categorizes. I did find a way to do this but not as quickly as I wanted to.
Not directly. But you can go via the invGroups table.
SELECT * FROM invTypes AS t1, invGroups AS t2, invCategories AS t3 WHERE t1.groupID = t2.groupID AND t2.categoryID = t3.categoryID
Simply add the category restriction then.
|
|
Lutz Major
Austriae Est Imperare Orbi Universo
|
Posted - 2010.07.20 20:26:00 -
[11]
Originally by: Gar Karath SELECT * FROM invTypes AS t1, invGroups AS t2, invCategories AS t3 WHERE t1.groupID = t2.groupID AND t2.categoryID = t3.categoryID
And how does THIS differ from Tontos query?
As I see it you want the items under the market root categories (ammunition, blueprints, ...) This is not an easy query. At least not with MySQL as it lacks recursion. Try to query the invMarketGroups table where there are no parentGroupIDs. Then you get those 11 root groups. The iterate over the sub-groups and so on...
|
Ochawe Bake
Minmatar The Baked Group
|
Posted - 2010.07.21 00:51:00 -
[12]
Thanks guys. Worked on it all day setting up relationships and lookups, but got it going on now. Cheers.
One more thing. downloaded my Asset and the items I have in pos shows an locationID that is not in the staStations table. Which I can understand. Is there any place to get the locationID of pos.
|
Dragonaire
Caldari Corax. Circle-Of-Two
|
Posted - 2010.07.21 02:53:00 -
[13]
Look at the moons that's what it uses and you should have a look at http://wiki.eve-id.net/APIv2_Corp_AssetList_XML where you'll find info on how to do the SQL query to ref the right tables to get the locationID you are looking for. -- Finds camping stations from the inside much easier. Designer of Yapeal for Eve API.
|
Catari Taga
Centre Of Attention Middle of Nowhere
|
Posted - 2010.07.21 09:11:00 -
[14]
Originally by: Lutz Major As I see it you want the items under the market root categories (ammunition, blueprints, ...) This is not an easy query. At least not with MySQL as it lacks recursion. Try to query the invMarketGroups table where there are no parentGroupIDs. Then you get those 11 root groups. The iterate over the sub-groups and so on...
There are 12 root groups now with the planetary stuff, and if you want to do it in a single query I'd simply do something like this:
SELECT(CASE WHEN m1.parentGroupID IS NULL THEN m1.marketGroupName WHEN m2.parentGroupID IS NULL THEN m2.marketGroupName WHEN m3.parentGroupID IS NULL THEN m3.marketGroupName WHEN m4.parentGroupID IS NULL THEN m4.marketGroupName WHEN m5.parentGroupID IS NULL THEN m5.marketGroupName ELSE '# needs deeper recursion #' END) marketCategoryName, i.typeName FROM invtypes i LEFT JOIN invMarketGroups m1 ON m1.marketGroupID = i.marketGroupID LEFT JOIN invMarketGroups m2 ON m1.parentGroupID = m2.marketGroupID LEFT JOIN invMarketGroups m3 ON m2.parentGroupID = m3.marketGroupID LEFT JOIN invMarketGroups m4 ON m3.parentGroupID = m4.marketGroupID LEFT JOIN invMarketGroups m5 ON m4.parentGroupID = m5.marketGroupID WHERE i.published = 1 AND i.marketGroupID IS NOT NULL ORDER BY marketCategoryName, i.typeName --
Originally by: CCP the data does not seem to support that polished quality sells better than new features
|
|
|
|
Pages: [1] :: one page |
First page | Previous page | Next page | Last page |