interrupt the execution of the stored procedure (mysql) in php

Question

I wrote a stored procedure (mysql)

CREATE PROCEDURE `set_SiteAttendance`(IN _UserName VARCHAR(20)  CHARSET utf8, IN _EventDesc VARCHAR(250)  CHARSET utf8, IN _EventTime BIGINT(20))
BEGIN

    DECLARE _EventID INT;
    DECLARE cursorGetID CURSOR FOR

        SELECT id
        FROM client_pages
        WHERE name = _EventDesc
        LIMIT 0, 1;

    -- try to add a new record to the database
    INSERT INTO client_pages (id, name) SELECT (IFNULL(MAX(id), 0) + 1), _EventDesc FROM client_pages;

    -- get id from the database records
    OPEN cursorGetID;
    FETCH cursorGetID INTO _EventID;

    -- set the data on the visit of the page in the database
    INSERT INTO login_history VALUES (NULL, _UserName, _EventID, _EventTime);

END

When I call it using MySQL Workbench, it works correctly.

CALL set_SiteAttendance('MyName', 'page#1', 100);

When I call it using php, then the stored procedure aborts on the INSERT statement:

$query = "CALL set_SiteAttendance('$user_name', '$user_page', $user_ticks)";
mysql_query($query);

mysql_error() call issues

Duplicate entry 'page#1' for key 'name'

why it happens and how to fix?

Does the `name` column have a UNIQUE property set on it? If so, when you created it in workbench, you then cannot create it again from PHP. Delete the row you added with workbench, using workbench and try the PHP code again — RiggsFolly, Sep 21 '16 at 09:44
Possible duplicate of [How to call a MySQL stored procedure from within PHP code?](http://stackoverflow.com/questions/3966747/how-to-call-a-mysql-stored-procedure-from-within-php-code) — e4c5, Sep 21 '16 at 09:53
The logic of my stored procedure to work correctly. When I call a stored procedure in MySQL Workbench no errors occur. The basic logic is: 1. I am trying to write _EventDesc client_pages the table with the new ID 2.a) if the table client_pages such a record is not, it will be added 2.b) if the table client_pages such a record already exists, then it will not be added 3. Further tables of client_pages get ID _EventDesc record 4. write down the ID table login_history — Zhihar, Sep 21 '16 at 10:21
As you can see - everything works fine Naturally, if there is already _EventDesc in client_pages, it is not written and the error will be issued. But this should not stop the execution of a stored procedure, you start running the following commands. And this is not happening. Why? — Zhihar, Sep 21 '16 at 10:21
If `id` is `AUTO_INCREMENT`, then `SELECT ... max(id) ...` is the _wrong_ way to set a new id. — Rick James, Sep 22 '16 at 05:58

score 0 · Answer 1 · answered Sep 21 '16 at 09:51

Well, that's pretty obvious. You have a unique index on name, so your query INSERT INTO client_pages (id, name) SELECT (IFNULL(MAX(id), 0) + 1), _EventDesc FROM client_pages; can only be run once with same _EventDesc parameter.

Remove the index or change the logic of your procedure.

score 0 · Answer 2 · answered Sep 22 '16 at 14:45

0

I decided my problem:

INSERT IGNORE INTO client_pages (id, name) SELECT (IFNULL(MAX(id), 0) + 1), _EventDesc FROM client_pages;

Error (name was found) was ignored and procedure is not interrupted

answered Sep 22 '16 at 14:45

Zhihar

1,306
1
22
45

interrupt the execution of the stored procedure (mysql) in php

2 Answers2