Well, I've found a solution after some more messing around. I've defined a
stored function that can do this for me. Here's the code for it:
CREATE
FUNCTION loss_points (check_id BIGINT(20) UNSIGNED)
RETURNS DOUBLE
DETERMINISTIC
READS SQL DATA
BEGIN
DECLARE done INT DEFAULT 0;
DECLARE total DOUBLE DEFAULT 0.0;
DECLARE loss DOUBLE;
DECLARE loss_cursor CURSOR FOR SELECT
IFNULL(SUM(items.basePrice *
lost.quantity * IF(items.techLevel > 1, 10, 1)), 0)+(ships.basePrice *
IF(ships.techLevel > 1, 10, 1)) AS totalValue FROM kb__killmails AS
mails LEFT JOIN kb__items_lost AS lost ON (lost.mail_id=mails.id) LEFT
JOIN kb__items AS items ON (lost.item_id=items.typeID) INNER JOIN
kb__items AS ships ON (mails.ship_id=ships.typeID) WHERE
mails.player_id=check_id GROUP BY mails.id;
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
OPEN loss_cursor;
REPEAT
FETCH loss_cursor INTO loss;
IF loss IS NOT NULL THEN
SET total = total+loss;
END IF;
UNTIL done END REPEAT;
CLOSE loss_cursor;
RETURN total;
END
(not bothering to simplify table names this time)
I would of had this sooner, but I hadn't set the default value for
'total', and so it was always returning null, because any math involving a
null value returns null. :)
If anyone has a better solution, or some reccomendations, I'll be glad to
hear them.
On Mon, September 25, 2006 2:04 pm, Matthew Walker wrote:
> I'm trying to do a rather tricky query in MySQL, and I'm a bit stumped. I
> know I can do what I want if I just offload the work to PHP, but for the
> sake of the learning, I'm trying to do it inside SQL.
>
> A bit of background: This is a 'point tracking' site for PVP in EVE
> Online. It parses kill reports, and stores them in the database. Now I'm
> trying to get it to tally up points per kill, based on the value of the
> items destroyed.
>
> Relevant Schema:
>
> CREATE TABLE `items` (
> `id` int(11) NOT NULL auto_increment,
> `typeName` char(100) default NULL,
> `graphicID` int(11) default NULL,
> `basePrice` double default NULL,
> `techLevel` tinyint(3) unsigned NOT NULL,
> PRIMARY KEY (`id`),
> UNIQUE KEY `typeName` (`typeName`),
> KEY `graphicID` (`graphicID`)
> ) ENGINE=MyISAM DEFAULT CHARSET=utf8;
>
> -- --------------------------------------------------------
>
> CREATE TABLE `lost` (
> `mail_id` bigint(20) unsigned NOT NULL,
> `item_id` bigint(20) unsigned NOT NULL,
> `quantity` bigint(20) unsigned NOT NULL,
> `location` enum('cargo','fit','drone_bay') NOT NULL
> ) ENGINE=MyISAM DEFAULT CHARSET=utf8;
>
> -- --------------------------------------------------------
>
> CREATE TABLE `mails` (
> `id` bigint(20) unsigned NOT NULL auto_increment,
> `player_id` bigint(20) unsigned NOT NULL,
> `corporation_id` bigint(20) unsigned NOT NULL,
> `alliance_id` bigint(20) unsigned NOT NULL,
> `ship_id` bigint(20) unsigned NOT NULL,
> `system_id` bigint(20) unsigned NOT NULL,
> `timestamp` datetime NOT NULL,
> `hash` varchar(32) NOT NULL,
> `mail` longtext NOT NULL,
> PRIMARY KEY (`id`)
> ) ENGINE=MyISAM DEFAULT CHARSET=utf8;
>
> I've got a query that does most of the work of generating the cost of all
> of a person's losses:
>
> SELECT IFNULL(SUM(items.basePrice * lost.quantity * IF(items.techLevel >
> 1, 10, 1)), 0)+(ships.basePrice * IF(ships.techLevel > 1, 10, 1)) AS
> totalValue FROM mails LEFT JOIN lost ON (lost.mail_id=mails.id) LEFT JOIN
> items ON (lost.item_id=items.id) INNER JOIN items AS ships ON
> (mails.ship_id=ships.id) WHERE mails.player_id=6 GROUP BY mails.id
>
> However, this returns 1 row per kill, and I would like to get it to return
> simply one value, that is a total of all the losses. Simple to do in PHP
> with what I have, but I can't find a way to do it directly in the SQL.
>
> I've tried SUM()'ing a subselect, but that returns null, so I guess I
> don't understand subselects. :)
>
> Oh, and flame my language/DB choice all you want. I know them, and they're
> good enough for me. If there isn't a way to do this in-db, I'll survive
> with doing it in the PHP.
>
> Thanks.
> --
> Matthew Walker
> Kydance Hosting & Consulting
> LAMP Specialist
>
> /*
> PLUG: http://plug.org, #utah on irc.freenode.net
> Unsubscribe: http://plug.org/mailman/options/plug
> Don't fear the penguin.
> */
>
--
Matthew Walker
Kydance Hosting & Consulting
LAMP Specialist
/*
PLUG: http://plug.org, #utah on irc.freenode.net
Unsubscribe: http://plug.org/mailman/options/plug
Don't fear the penguin.
*/
