MySQL syntax wanted

[Dormilich]

Hi,

basicly I want to increase a counter variable for a given "id" in a MySQL table (using UPDATE)

_stats (id, date, hits, visits)

this won't work, if the entry does not exist. currently I'm checking for the existence of the "id" before I update and create a new entry if the "id" doesn't exist.

my question now, is there a way to accomplish that in one query?

thanks

[Dormilich]

after som asking and consulting google it seems like I need a combination of TRIGGER and a stored procedure…

I suppose, I only need to declare (create) the trigger/stored procedure once, but I'm not sure (I couldn't find a clear statement for that, though the examples imply that), could someone clarify that?

looking further into it

Dormi

[Dormilich]

I'm having trouble with the trigger

CREATE TRIGGER 
    add_today
BEFORE UPDATE
ON 
    `_stats`
FOR EACH ROW
    BEGIN
        CALL count_dates(@dates); // this procedure works
        IF
            @dates = 0
        THEN
            CALL make_my_day(); // this procedure works too
    END$$ // $$ is set as temporary delimiter

I get the message

#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 13 (line numbers are equivalent)
further, phpmyadmin shows a error sign on the IF … THEN … construct.

anyone an idea what's wrong here?

info: MySQL 5.0.45 via UNIX socket, phpMyAdmin 2.11.9.1

EDIT: the stored procedures:

CREATE PROCEDURE
    count_dates (OUT param INT)
    BEGIN
        SELECT
            COUNT(`date`)
        FROM
            `_stats`
        WHERE
            `date` = CURDATE()
        INTO
            param;
    END$$
 
CREATE PROCEDURE
    make_my_day ()
    BEGIN
        INSERT INTO
            `_stats`
            (`id`, `date`, `visits`, `hits`)
        VALUES
            ('', CURDATE(), 0, 0);
    END$$

[r035198x]

See the mysql refmanual for Insert and check if INSERT ... ON DUPLICATE KEY UPDATE will help.

[Dormilich]

I eventually figured out an END IF statement was missing (well, I'm not yet finished testing)

ON DUPLICATE KEY UPDATE won't help me here, since I don't use INSERT in the first place.

[r035198x]

So how are you creating the new entry if the id does not exist?

[Dormilich]

CALL make_my_day();

that's what the Trigger is supposed to do.

[r035198x]

Why not use the insert/on duplicate key update instead of the trigger. I think that a trigger is overkill for this.

[Dormilich]

that may be true, but
- I always want to learn new stuff
- when I initially asked about the problem, the only answer I got was "you can try a trigger" and I've had not heard about the insert … on duplicate key update before (I'm just familiar with the basic update/insert/select/delete stuff)

[r035198x]

If it's for learning then ok. Triggers do place a bit of overhead on the database and I would want to minimize them as much as possible.

[Dormilich]

thanks for the info. unfortunately, the MySQL manual is not a very exhaustive one, I had quite some difficulty getting all the info I needed.