5 replies to this topic

[BlaineSch]

Okay so I have two tables:

tags
    id, display_name
tagLinks
    id, rid, tid

Tags are simply it's just an auto increment id, and a display name I set.
tagLinks references two tables to bring them together. Also with a recurring id.

I made a stored function to make inserting easier.

DELIMITER $$

CREATE FUNCTION `getTagId`(myName varchar(255)) RETURNS int(11)
BEGIN
    SET @result = (SELECT id FROM `tags` WHERE `display_name`=myName OR `id`=myName);
    If @result IS NULL Then
        INSERT INTO `tags` (`display_name`) VALUES (myName);
        RETURN last_insert_id();
    ELSE 
        RETURN @result;
    END If;
END

Basically it trys to find a current "tag" based on the id or display name. If it does not exist, create it and give me that id.

I'm using it so I can insert references cleaner.

So the actual code I see in my program is short and clean:

INSERT INTO `tagLinks` (`rid`, `tid`) VALUES
(1, getTagId('Random Tag Name1')),
(1, getTagId('Random Tag Name2')),
(1, getTagId('Random Tag Name3')),
(1, getTagId('Random Tag Name4'))

This works great in mysql workbench, however when implemented on the server I can't run stored procedures or functions. I ask my host and apparently cPanel has a bug where it can't add it "execute". Not sure how I was running it in workbench though. The host said they will fix the cPanel bug in "a week or so". I asked them to try and add the permission since I don't have access but not a response yet.

My question is, do you see an alternative to what I am doing? That's just as clean?

[wim DC]

That's quite an annoying problem :/
The only, incredibly dirty, solution i can come up with is creating an extra table that has 2 columns, 1 number, 1 not-nullable varchar.
And instead of

INSERT INTO `tagLinks` (`rid`, `tid`) VALUES

(1, getTagId('Random Tag Name1'))

You use the extra table.

INSERT INTO `DummyTable` (`rid`, `tagname`) VALUES

(1, 'Random Tag Name1')

The trick is to put a trigger on this new table BEFORE INSERT.
In the trigger body you can then put the code from the function to get the appropriate ID, and then perform an insert on tagLinks table.

Unfortunately there is no way in MySQL to stop an insert statement from a trigger.

So to prevent the DB from inserting into the dummytable and creating unneeded data, there is the not-nullable column in the newTable.
In this BEFORE INSERT trigger you set the 2nd parameter to null. It will cause an error and the record won't be inserted.

PROS:
- I think this works :)

CONS:
- Extra table (empty tho)
- Ugly prevention of insert by intentially causing an not-null-error


You could propably skip the nullable part and just clear the whole dummytable every time it inserts there. This will leave max 1 record (the new one) in the table, however.

A trigger AFTER INSERT can't solve this as you appearantly can't remove the newly inserted there (I THINK)

[WingedPanther]

Why not use an after insert trigger to delete the record from the dummy table?

[wim DC]

I read somewhere this wasn't possible..

[BlaineSch]

Hm, looks interesting. I've heard some programmers talking about how complex triggers were, however they seem to compare to constructors and destructors for queries. I think this is an acceptable work around.

[bakhtn]

i wish i could understand what you guys are talking , I have just started learning abouth programming in php with mysql. wish me luck.