MySQL several queries using if

Question

I am right now writing a few MySQL commands, which allow me to generate better statistics. My problem is now that under special circumstances a few queries should be called and under other circumstances not.

my current attempt: (I shorted it)

SET @var1=0, @var2=0;
SELECT @var1 := `id` FROM `sg_playerstats` ORDER BY `id` ASC LIMIT 0, 1;
SELECT @var2 := `Last Updated` FROM `sg_playerranking` LIMIT 0, 1;
IF @var1 > @var2 THEN (

TRUNCATE `sg_playerranking`;

SET
    @Rank := 0;

INSERT INTO
    `sg_playerranking`
SELECT
    @Rank := @Rank + 1 AS `Rank`,
    t.*,
    @var1 AS `Last Updated`
FROM
    (
        SELECT
            `player` AS `Player`,
        FROM
            `sg_playerstats`
        GROUP BY
            `player`
        ORDER BY
            `Score` DESC, ...
    ) t;  )
END IF

SELECT `Rank`, ... FROM `sg_playerranking` WHERE 1

The If statement is not working (Syntax error. Every single query part itself is working!) I did some research but could not find anything but that!

The parentheses make no difference!

EDIT:

Now i am really confused!

My code is:

CREATE PROCEDURE generate_statistics (OUT param1 INT)
BEGIN

    SET @var1=0, @var2=0;
    SELECT @var1 := `id` FROM `sg_playerstats` ORDER BY `id` ASC LIMIT 0, 1;
    SELECT @var2 := `Last Updated` FROM `sg_playerranking` LIMIT 0, 1;
    IF @var1 > @var2 THEN

    TRUNCATE `sg_playerranking`;

    SET
        @Rank := 0;

    INSERT INTO
        `sg_playerranking`
    SELECT
        @Rank := @Rank + 1 AS `Rank`,
        t.*,
        @var1 AS `Last Updated`
    FROM
        (
            SELECT
                `player` AS `Player`,
                SUM(1) AS `Games`,
                SUM(`points`) AS `Points`,
                SUM(`points`) / SUM(1) AS `Points per Game`,
                SUM(IF(`position` = 1, 1, 0)) AS `Wins`,
                SUM(IF(`position` = 1, 1, 0)) / SUM(1) AS` Wins per Game`,
                SUM(IF(`position` = 1, 0, 1)) AS `Loses`,
                SUM(IF(`position` = 1, 0, 1)) / SUM(1) AS `Loses per Game`,
                SUM(`kills`) AS `Kills`,
                SUM(`kills`) / SUM(1) AS `Kills per Game`,
                SUM(`death`) AS `Deaths`,
                SUM(`death`) / SUM(1) AS `Deaths per Game`,
                SEC_TO_TIME(SUM(`time`) / 1000) AS `Time played`,
                ROUND(sqrt(SUM(`points`)) + 10 * SUM(`points`) / SUM(1) + 10 * sqrt(SUM(IF(`position` = 1, 1, 0))) + 100 * SUM(IF(`position` = 1, 1, 0)) / SUM(1) - 5 * sqrt(SUM(IF(`position` = 1, 0, 1))) - 50 * SUM(IF(`position` = 1, 0, 1)) / SUM(1) + 7.5 * sqrt(SUM(`kills`)) + 75 * SUM(`kills`) / SUM(1) - 3.75 * sqrt(SUM(`death`)) - 37.5 * SUM(`death`) / SUM(1), 2) AS `Score`
            FROM
                `sg_playerstats`
            GROUP BY
                `player`
            ORDER BY
                `Score` DESC,
                `Points per Game` DESC,
                `Wins per Game` DESC,
                `Loses per Game` ASC,
                `Kills per Game` DESC,
                `Deaths per Game` ASC,
                `Points` DESC,
                `Wins` DESC,
                `Loses` ASC,
                `Kills` DESC,
                `Deaths` ASC,
                `Games` DESC,
                `Time played` DESC,
                `Player` DESC
        ) t;
    END IF;

    SELECT `Rank`, `Player`, `Games`, `Points`, `Points per Game`, `Wins`, `Wins per Game`, `Loses`, `Loses per Game`, `Kills`, `Kills per Game`, `Deaths`, `Deaths per Game`, `Time played`, `Score` INTO param1 FROM `sg_playerranking` WHERE 1
END;

This is the error i get:

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 4

Well it makes no difference if I take DECLARE!

(Sorry for the long query!)

EDIT2:

I finally solved it! This code is working!

DELIMITER //

CREATE PROCEDURE gamestats ()
BEGIN
    SET
        @var1 = 1,
        @var2 = 0;

    SELECT
        @var1 := `id`
    FROM
        `sg_playerstats`
    ORDER BY
        `id` DESC
    LIMIT
        0,
        1;

    SELECT
        @var2 := `Last Updated`
    FROM
        `sg_playerranking`
    LIMIT
        0,
        1;

    IF
        @var1 > @var2
    THEN
        TRUNCATE
            `sg_playerranking`;
        SET
            @Rank := 0;
        INSERT INTO
            `sg_playerranking`
        SELECT
            @Rank := @Rank + 1 AS `Rank`,
            t.*,
            @var1 AS `Last Updated`
        FROM
            (
                SELECT
                    `player` AS `Player`,
                    SUM(1) AS `Games`,
                    SUM(`points`) AS `Points`,
                    SUM(`points`) / SUM(1) AS `Points per Game`,
                    SUM(IF(`position` = 1, 1, 0)) AS `Wins`,
                    SUM(IF(`position` = 1, 1, 0)) / SUM(1) AS` Wins per Game`,
                    SUM(IF(`position` = 1, 0, 1)) AS `Loses`,
                    SUM(IF(`position` = 1, 0, 1)) / SUM(1) AS `Loses per Game`,
                    SUM(`kills`) AS `Kills`,
                    SUM(`kills`) / SUM(1) AS `Kills per Game`,
                    SUM(`death`) AS `Deaths`,
                    SUM(`death`) / SUM(1) AS `Deaths per Game`,
                    SEC_TO_TIME(SUM(`time`) / 1000) AS `Time played`,
                    ROUND(sqrt(SUM(`points`)) + 10 * SUM(`points`) / SUM(1) + 10 * sqrt(SUM(IF(`position` = 1, 1, 0))) + 100 * SUM(IF(`position` = 1, 1, 0)) / SUM(1) - 5 * sqrt(SUM(IF(`position` = 1, 0, 1))) - 50 * SUM(IF(`position` = 1, 0, 1)) / SUM(1) + 7.5 * sqrt(SUM(`kills`)) + 75 * SUM(`kills`) / SUM(1) - 3.75 * sqrt(SUM(`death`)) - 37.5 * SUM(`death`) / SUM(1), 2) AS `Score`
                FROM
                    `sg_playerstats`
                GROUP BY
                    `player`
                ORDER BY
                    `Score` DESC,
                    `Points per Game` DESC,
                    `Wins per Game` DESC,
                    `Loses per Game` ASC,
                    `Kills per Game` DESC,
                    `Deaths per Game` ASC,
                    `Points` DESC,
                    `Wins` DESC,
                    `Loses` ASC,
                    `Kills` DESC,
                    `Deaths` ASC,
                    `Games` DESC,
                    `Time played` DESC,
                    `Player` DESC
            ) t;
    END IF;
END;
//
DELIMITER ;

What? happens when you do use DECLARE statements? – DWright Jan 21 '13 at 07:16 — DWright, Jan 21 '13 at 07:16
Sweet, glad you got it working. – DWright Jan 21 '13 at 15:13 — DWright, Jan 21 '13 at 15:13

score 1 · Accepted Answer · answered Jan 21 '13 at 06:21

1

In Mysql, the if statement is only applicable to stored procedures. It is not available in regular sql batches.

answered Jan 21 '13 at 06:21

DWright

8,871
4
34
52

1

The above question is not a `Procedure` ? – Sahal Jan 21 '13 at 06:23
How do i make it a procedure? And how do I call it then? – BrainStone Jan 21 '13 at 06:24
Take a look at: http://dev.mysql.com/doc/refman/5.0/en/create-procedure.html. If you scroll down, you can see examples of stored procedures. – DWright Jan 21 '13 at 06:26
Also, look at http://dev.mysql.com/doc/refman/5.0/en/call.html for how to call as stored procedure. Scrolling down will also show some examples. – DWright Jan 21 '13 at 06:29
I tried and got an really confusing error. Please check the start post! – BrainStone Jan 21 '13 at 06:56

MySQL several queries using if

1 Answers1

Linked