Using Declare in MySQL

Question

I have been building a forum or blog if you will for my web portfolio with PHP and MySQL. I'm pretty decent with PHP but MySQL has always been a problem for me because I have never had a reason to use MySQL or any database before this, not MySQL error messages are the worst I have error seen. This is the query I am trying to use for add new post to the forum/blog thing.

START TRANSACTION;

DECLARE postKey int;

INSERT INTO posts(post_subject, post_content, post_date, post_by) 
SELECT ?, ?, NOW(), user_id FROM users WHERE user_name = ? LIMIT 1;

SET postKey = LAST_INSERT_ID();

INSERT INTO juct_tags_posts(post_key, tag_key) 
SELECT postKey, tag_id FROM tags WHERE tag_name = ? LIMIT 1;
INSERT INTO juct_tags_posts(post_key, tag_key) 
SELECT postKey, tag_id FROM tags WHERE tag_name = ? LIMIT 1;
INSERT INTO juct_tags_posts(post_key, tag_key) 
SELECT postKey, tag_id FROM tags WHERE tag_name = ? LIMIT 1;

COMMIT;

The first insert is the post_data. The next three inserts are to a junction table of tag_id and post_id. The problem is this. Prepare failed: (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 'DECLARE postKey int;INSERT INTO posts(post_subject, post_content, post_date, pos' at line 1 I have no idea what's causing the problem. Also, if this help when the query is prepare its set into the string like this.

START TRANSACTION;DECLARE postKey int;INSERT INTO posts(post_subject, post_content, post_date, post_by) SELECT ?, ?, NOW(), user_id FROM users WHERE user_name = ? LIMIT 1;SET postKey = LAST_INSERT_ID();INSERT INTO juct_tags_posts(post_key, tag_key) SELECT postKey, tag_id FROM tags WHERE tag_name = ? LIMIT 1;INSERT INTO juct_tags_posts(post_key, tag_key) SELECT postKey, tag_id FROM tags WHERE tag_name = ? LIMIT 1;INSERT INTO juct_tags_posts(post_key, tag_key) SELECT postKey, tag_id FROM tags WHERE tag_name = ? LIMIT 1;COMMIT;

See this Q&A http://stackoverflow.com/q/9974325/1415724 and related links on the mysql.com website https://dev.mysql.com/doc/refman/5.7/en/commit.html --- https://dev.mysql.com/doc/refman/5.7/en/declare-handler.html — Funk Forty Niner, Feb 17 '17 at 17:28
Thanks so much for that. I just realize there a lot more to the declare keyword. I though it was just a keyword used to declare variables in a transaction but I was way off and there is alot more to it. I have been looking at sql all wrong and seeing it more like dos and less like a programming language. — Geoff Taylor, Feb 17 '17 at 19:40

score 0 · Accepted Answer · answered Feb 20 '17 at 21:59

I've been sick the last couple days so I haven't been doing much coding but I took the time from my computer learn more MySQL and came across a solution the solved this problem. The main tables used are

Posts (post_id int(11), post_subject varchar(255), post_content text, post_date datetime, fk_post_by int(11))
Tags (tag_id int(11), tag_name varchar(255))
Junction_Posts_Tags(junc_id int(11), fk_post_id int(11), fk_tag_id int(11))

My new solution uses 2 'stored procedures' and a completed reworked transaction.

Stored procedure for adding new row to Post table

addPost(IN msg text, IN sub varchar(255), IN poster varchar(255), OUT post int)
BEGIN
INSERT INTO posts(post_content, post_subject, post_date, post_by) 
SELECT msg, sub, NOW(), user_id 
FROM users 
WHERE user_name = poster;
SET post = LAST_INSERT_ID();
END

The tags table contains predefined value and I don't want to allow new tags to be added by users. Users can select multiple tags for one post though hence the need for the junction table. This is the stored procedure for adding a row for junction table.

addPostTag(IN tag varchar(255), IN post int)
BEGIN
INSERT INTO juct_tags_posts(post_key, tag_key) 
SELECT post_id, 
(SELECT tag_id 
FROM tags 
WHERE tag_name = tag) 
FROM posts 
WHERE post_id = post;
END

These two procedure are not done though, I still want to add error handling once I have worked out the DECLARE action HANDLER condition statement. The SQL statement I use in PHP is broken up into three parts now.

set AUTOCOMMIT = 0;
START TRANSACTION;
CALL addPost(?,?,?, @post_id);

This begins the transaction and creates the row for the Post table in memory. Next is

CALL addPostTag(?, @post_id);

I run this sql for every tag selected by the user. I tried storing @post_id as an alias, but I kept getting as error when I used it in the procedure call. Then if no errors are returned, I commit everything with this.

COMMIT;

I posted this answer because I didn't find any solutions to this specific scenario when I made the question. Hope it helps someone and thanks again to Fred -ii- for the help.

Using Declare in MySQL

1 Answers1