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 n°2 (last one, I promise) [SOLVED]
Author
Previous Topic Next Topic
Nathan WAKE
Deep Core Mining Inc.
Caldari State
#1 - 2011-10-12 15:06:47 UTC
Hello again,

As I got really useful tips and help with my last question, permit me to ask one more time for your contribution.

Let's say, I want to write a MYSQL request to get the Abaddon (or any other ship, but let's take this one as an example) attributes like low, med, high, riggs slots and drone capacity, as well as the race name and ID.

Here is what I wrote (and it works).

Quote:
SELECT
dgmtypeattributes.typeID,
invtypes.typeName,
dgmattributetypes.attributeName,
dgmtypeattributes.attributeID,
dgmtypeattributes.valueInt,
dgmtypeattributes.valueFloat,
invtypes.groupID,
invtypes.raceID,
chrraces.raceName
FROM
dgmtypeattributes
INNER JOIN dgmattributetypes ON dgmattributetypes.attributeID = dgmtypeattributes.attributeID
INNER JOIN invtypes ON dgmtypeattributes.typeID = invtypes.typeID
INNER JOIN invgroups ON invtypes.groupID = invgroups.groupID
INNER JOIN chrraces ON invtypes.raceID = chrraces.raceID
WHERE
invtypes.typeName = "Abaddon" AND
dgmtypeattributes.attributeID IN (12,13,14,283,1137)


And here is the result :

Quote:

typeID,typeName,attributeName,attributeID,valueInt,valueFloat,groupID,raceID,raceName
24692,Abaddon,lowSlots,12,7,,27,4,Amarr
24692,Abaddon,medSlots,13,4,,27,4,Amarr
24692,Abaddon,hiSlots,14,8,,27,4,Amarr
24692,Abaddon,droneCapacity,283,,75,27,4,Amarr
24692,Abaddon,rigSlots,1137,3,,27,4,Amarr


With that I have all I need, BUT...

I want to create a new table out of my request (I am not displaying anything in PHP, just reordering things in a new table for dev purpose), but I want it ordered this way :

Quote:
typeID,typeName,lowSlots,medSlots,hiSlots,droneCapacity,rigSlots,raceID,racename
24692,Abaddon,7,4,8,75,3,4,Amarr


I've tried many permutation and switches in MYSQL (order by, x AS y and so on), but never got what I want.

I'm thinking of populating this bloody ship table by hand, the problem is I'll need the same kind of thing for modules, charges, drones and subsystems. Oops

Any idea ?

Thank in advance


Nathan

"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
#2 - 2011-10-12 15:29:00 UTC
That isn't so easy in SQL. You have to query for each column

SELECT inv.typeID, inv.typeName, low.valueInt lowSlots, med.valueInt medSlots, high.valueInt hiSlots, drones.valueFloat droneCapacity, rigs.valueInt rigSlots, races.raceID, races.raceName
FROM invTypes inv
LEFT JOIN chrRaces races ON inv.raceID = races.raceID
LEFT JOIN dgmTypeAttributes low ON inv.typeID = low.typeID and low.attributeID = 12
LEFT JOIN dgmTypeAttributes med ON inv.typeID = med.typeID and med.attributeID = 13
LEFT JOIN dgmTypeAttributes high ON inv.typeID = high.typeID and high.attributeID = 14
LEFT JOIN dgmTypeAttributes drones ON inv.typeID = drones.typeID and drones.attributeID = 283
LEFT JOIN dgmTypeAttributes rigs ON inv.typeID = rigs.typeID and rigs.attributeID = 1137

WHERE inv.typeName = 'Abaddon'

You should also use LEFT JOIN, otherwise a missing value (e.g. no drone capacity) will give you an empty result
Steve Ronuken
Fuzzwork Enterprises
Vote Steve Ronuken for CSM
#3 - 2011-10-12 17:00:06 UTC
This is what's known as a pivot table.

What I'd suggest doing, if this is for a one off, is: Use a spreadsheet program, then import the results.

Most have pivot table functionality, and they make this kind of thing a lot easier. (if you were using oracle 11gr2, you could do it natively, but even express edition is a little heavier than most people would want.)


It's a /little/ harder when you want to pull out a result which isn't in a row by itself, but not impossible. (cheat. stick it in a concantenated column with name, then split it after pivoting. )



Or you could look at the file from
https://forums.eveonline.com/default.aspx?g=posts&t=14787

might have what you want, which you could then import.

Woo! CSM XI!

Fuzzwork Enterprises

Twitter: @fuzzysteve on Twitter
Nathan WAKE
Deep Core Mining Inc.
Caldari State
#4 - 2011-10-12 20:21:51 UTC  |  Edited by: Nathan WAKE
Thanks to both of you, I have what I need now (and improved my SQL skills a bit) . Thank you very very much !!

@Lutz - thanks for the "ready to use" query and the tip on the JOINT. I modified your query and got exactly what I needed.

@Steve : thanks for the link and the useful info - and yes, it's a one time export - at least until CCP adds new ships or modifies one that already exists.

This is for a "little app" I'll soon be releasing on this forum.

Thanks again to both of you

Nath

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

Zaza Gabor
Dragonaire
Here there be Dragons
#5 - 2011-10-13 03:35:13 UTC
Looks like you already solved your problem but thought I'd give another one anyway since at some point CCP will add some new ships etc. ADOdb has a pivot function that works with all DBs it supports. I've not used it because I don't have a need but I know another developer that was DB guy thought it was pretty cool that it had it.
http://phplens.com/lens/adodb/docs-adodb.htm

Finds camping stations from the inside much easier. Designer of Yapeal for the Eve API. Check out the Yapeal PHP API Library thread.
Nathan WAKE
Deep Core Mining Inc.
Caldari State
#6 - 2011-10-13 07:26:03 UTC
Thank you Dragonaire for that useful link. I followed it and had a look at the page...

I am afraid that is way above my actual skills at php and Mysql Oops.

How dare I say that on a forum... I'm not usually that modest Big smile . But I will certainly dig in that direction (as well as in the direction of the PHP EvE API libraries) when I'l feel confident enough for bigger apps. Bookmarked !!

Thanks again.

Nathan

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

Zaza Gabor
Tonto Auri
Vhero' Multipurpose Corp
#7 - 2011-10-14 04:37:30 UTC  |  Edited by: Tonto Auri
Nathan WAKE wrote:
I want to create a new table out of my request (I am not displaying anything in PHP, just reordering things in a new table for dev purpose), but I want it ordered this way :

Don't. Just don't. You're creating a problem for yourself and your end-users going this dirty road.
If you absolutely unable to live without a sugar pile, create a view to your data in the way you want them, but don't reorder database.

Two most common elements in the universe are hydrogen and stupidity. -- Harlan Ellison