MySQL syntax wanted

Dormilich

Hi,

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

Expand|Select|Wrap|Line Numbers

_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

Expand|Select|Wrap|Line Numbers

 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:

Expand|Select|Wrap|Line Numbers

 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

Expand|Select|Wrap|Line Numbers

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.

MySQL syntax wanted

re: MySQL syntax wanted

re: MySQL syntax wanted

re: MySQL syntax wanted

re: MySQL syntax wanted

re: MySQL syntax wanted

re: MySQL syntax wanted

re: MySQL syntax wanted

re: MySQL syntax wanted

re: MySQL syntax wanted

re: MySQL syntax wanted

Similar MySQL Database bytes

/bytes/development

/bytes/it

/bytes/about