These forums have been archived and are now read-only.

The new forums are live and can be found at https://forums.eveonline.com/

EVE Technology Lab

 
  • Topic is locked indefinitely.
 

MYSQL question [SOLVED]
Author
Previous Topic Next Topic
Nathan WAKE
Deep Core Mining Inc.
Caldari State
#1 - 2011-10-11 15:39:35 UTC  |  Edited by: Nathan WAKE
Hello everyone,

Hope this is the right section to post.

Here is what I'm trying to do : I want a full list of in game ships, with graphical references for an php application I'm preparing.

Using tables "invgroups" and "invtypes", I managed to get :


  • category ID (6 for ships)
  • group ID (different classes of ships)
  • groupName (name of the different classes)
  • typeName(name of the ships)
  • typeID (because I thought this would help me find the icon)


Here is my Mysql request :

Quote:
SELECT
invgroups.categoryID,
invgroups.groupID,
invgroups.groupName,
invtypes.groupID,
invtypes.typeName,
invtypes.typeID
FROM
invgroups
INNER JOIN invtypes ON invgroups.groupID = invtypes.groupID
WHERE
invgroups.categoryID = 6 AND
invtypes.published = 1 AND
invgroups.groupID = 25 OR
invgroups.groupID = 26 OR
invgroups.groupID = 27 OR
invgroups.groupID = 28 OR
invgroups.groupID = 29 OR
invgroups.groupID = 30 OR
invgroups.groupID = 31 OR
invgroups.groupID = 237 OR
invgroups.groupID = 324 OR
invgroups.groupID = 358 OR
invgroups.groupID = 380 OR
invgroups.groupID = 381 OR
invgroups.groupID = 419 OR
invgroups.groupID = 420 OR
invgroups.groupID = 463 OR
invgroups.groupID = 485 OR
invgroups.groupID = 513 OR
invgroups.groupID = 540 OR
invgroups.groupID = 541 OR
invgroups.groupID = 543 OR
invgroups.groupID = 547 OR
invgroups.groupID = 659 OR
invgroups.groupID = 830 OR
invgroups.groupID = 831 OR
invgroups.groupID = 832 OR
invgroups.groupID = 833 OR
invgroups.groupID = 834 OR
invgroups.groupID = 883 OR
invgroups.groupID = 893 OR
invgroups.groupID = 894 OR
invgroups.groupID = 898 OR
invgroups.groupID = 900 OR
invgroups.groupID = 902 OR
invgroups.groupID = 906 OR
invgroups.groupID = 941 OR
invgroups.groupID = 963 OR
invgroups.groupID = 1022


The "invtypes.published = 1" was for filtering and getting only the player available ships. This works well and I get a list of 340 ship names.

Now, I've tried a lot of permutations, but I am unable Oops to associate the right tables to get the icon or image reference for those ships.

The table "evegraphics" points mainly to ".blue" files I can not use, and when crossing with the "eveicons" table, I fall down to 16 results instead of the 340 with my initial request.

I hope I'm clear enough Big smile

Any idea ?

"I'm a very good housekeeper. Each time I get a divorce, I keep the house"

Zaza Gabor
Jenn Makanen
Doomheim
#2 - 2011-10-11 15:52:50 UTC
if you're using the images from http://www.eveonline.com/community/toolkit.asp then the filename is based off the typeid from invTypes
Nathan WAKE
Deep Core Mining Inc.
Caldari State
#3 - 2011-10-11 15:58:33 UTC
God....was it that simple ? I was looking throughout the tables !!! Oops


I get it now, thanks a lot Jenn.

Nathan

"I'm a very good housekeeper. Each time I get a divorce, I keep the house"

Zaza Gabor
Zeta Zhul
Preemptive Paranoia
#4 - 2011-10-11 18:07:41 UTC
You might want to check out the "IN" clause:

Quote:

SELECT
invgroups.categoryID,
invgroups.groupID,
invgroups.groupName,
invtypes.groupID,
invtypes.typeName,
invtypes.typeID
FROM
invgroups
INNER JOIN invtypes ON invgroups.groupID = invtypes.groupID
WHERE
invgroups.categoryID = 6 AND
invtypes.published = 1 AND
invgroups.groupID in (
25,26,27,28,29,30,31,237,324,358,380,381,419,420,463,485,513,540,541,543,547,659,830,
831,832,833,834,883,893,894,898,900,902,906,941,963,1022)


You can do a Select ... From ... Where ... In () or a Select ... From ... Where .... NOT IN ()

Pretty useful.
Nathan WAKE
Deep Core Mining Inc.
Caldari State
#5 - 2011-10-11 19:13:11 UTC
Zeta, I have to thank you for that useful command indeed.

And my ship count is down to 256 which seams more correct.

Thanks again Big smile

"I'm a very good housekeeper. Each time I get a divorce, I keep the house"

Zaza Gabor
Lutz Major
Austriae Est Imperare Orbi Universo
#6 - 2011-10-11 19:23:57 UTC
Zeta Zhul wrote:
You might want to check out skip the "IN" clause:

Quote:

SELECT
invgroups.categoryID,
invgroups.groupID,
invgroups.groupName,
invtypes.groupID,
invtypes.typeName,
invtypes.typeID
FROM
invgroups
INNER JOIN invtypes ON invgroups.groupID = invtypes.groupID
WHERE
invgroups.categoryID = 6 AND
invtypes.published = 1 AND
invgroups.groupID in (
25,26,27,28,29,30,31,237,324,358,380,381,419,420,463,485,513,540,541,543,547,659,830,
831,832,833,834,883,893,894,898,900,902,906,941,963,1022)


You can do a Select ... From ... Where ... In () or a Select ... From ... Where .... NOT IN ()

Pretty useful.

Fixed your post :-)
Nathan WAKE
Deep Core Mining Inc.
Caldari State
#7 - 2011-10-11 19:45:01 UTC
LOL,

Why do it simple when it can be complicated and use up server time in unnecessary queries ?

Thank you Lutz Big smile

"I'm a very good housekeeper. Each time I get a divorce, I keep the house"

Zaza Gabor
Oldin Kinrod
Center for Advanced Studies
Gallente Federation
#8 - 2011-10-12 01:02:01 UTC
Just my 2 cents as I do the same thing (also with skills). I personally find the inner join to work better for me - and I can get all the objects of a given category \o/

SELECT invTypes.* FROM invCategories INNER JOIN invGroups ON invCategories.categoryID = invGroups.categoryID INNER JOIN invTypes ON invGroups.groupID = invTypes.groupID WHERE (invCategories.categoryID = 6)
Lutz Major
Austriae Est Imperare Orbi Universo
#9 - 2011-10-12 06:39:31 UTC
Oldin Kinrod wrote:
Just my 2 cents as I do the same thing (also with skills). I personally find the inner join to work better for me - and I can get all the objects of a given category \o/

SELECT invTypes.* FROM invCategories INNER JOIN invGroups ON invCategories.categoryID = invGroups.categoryID INNER JOIN invTypes ON invGroups.groupID = invTypes.groupID WHERE (invCategories.categoryID = 6)

May I ask,

a) why do you join the invCategories?
b) why you state ' ... I ... find the inner join to work better ...'? Every query in that thread uses that join?!?