MySQL Query Guidance Needed

Matthew Walker rorith at kydance.net
Mon Sep 25 15:26:52 MDT 2006


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



More information about the PLUG mailing list