Stored Function Not Working

[Tenaciousmug]

Ok I'm trying to create a stored function to grab a username based on the user id that is given. My syntax is as follows:

 

CREATE FUNCTION get_username(user_id BIGINT(20))
RETURNS VARCHAR(35)
BEGIN
DECLARE user_name VARCHAR(35);


SELECT username
INTO user_name
FROM user
WHERE user_id = user_id;


RETURN(user_name);
END;

 

 

However, it is saying this error:

MySQL said:

#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 4

 

I'm not sure what's wrong with the word DECLARE since it's nesting inside a BEGIN and END statement..

 

Also, I don't know if anyone knows any other efficient way of doing this so I don't have to create a stored function. I know you can join a user table from a forum post table to grab the username instead, but the thing is I need to grab both the topic username and last post username... but you can only join the user table using either the topic author or the post author... but I need BOTH usernames so I don't have to run two SQL statements.

Here is my SQL statement for that thus far:

 

SELECT DISTINCT t.id, t.title, t.sticky, t.locked, u.username
FROM forum_topic t
JOIN user u ON u.user_id = t.author_id
LEFT JOIN forum_post p ON p.topic_id = t.id
WHERE t.cat_id = 2
ORDER BY p.date DESC

 

But I also want to grab another username that p.author_id is in AND t.author_id.. I just don't know how to??

 

Any help on this would be much appreciate. Thank you!

[mikosiko]

just join the user table again using a different alias

[Tenaciousmug]

Thank you! I wasn't aware that you could do that. That works like a charm, but now my DISTINCT isn't exactly working the way I want it to. If the board has two posts in it, it pulls that thread twice even with the DISTINCT on the id... So I'm confused.

This is my SQL statement now:

 

SELECT DISTINCT t.id, t.title, t.sticky, t.locked, u.username AS author, pu.username AS lposter
FROM forum_topic t
JOIN user u ON u.user_id = t.author_id
LEFT JOIN forum_post p ON p.topic_id = t.id
LEFT JOIN user pu ON pu.user_id = p.author_id
WHERE t.cat_id =2
ORDER BY p.date DESC

 

Now this is what is returning:

 

id title sticky locked author lposter

 

5 Another Topic 0 0 peanut peanut

3 Another Topic 0 0 peanut draco

3 Another Topic 0 0 peanut peanut

2 Testing123 0 0 peanut peanut

 

However, topic 3 is only suppose to be pulling once as Draco is the last poster... But it's pulling all the posts from it. So I basically need to LIMIT 1 on the pu.username.. Is there a way of doing that??

Edited January 4, 2013 by Tenaciousmug

[cpd]

You should really be using inner join to save resources.

[Tenaciousmug]

Uhm thanks for the suggestion on switching my left joins. I have done that, but that didn't help me at all with my last question... Wasn't very helpful towards my goal, but thanks anyway.

[cpd]

I've just created a similar database based on your query and written the following to retrieve the necessary data.

 

SELECT `ft`.`title`, `opening_post_user`.`name`, (
  SELECT `name` 
  FROM `forum_post` `fp`
  INNER JOIN `user` `last_post_user` ON `last_post_user`.`id` = `fp`.`userId`
  WHERE `fp`.`forumTopicId` = `ft`.`id`
  ORDER BY `fp`.`id` DESC
  LIMIT 1
) `last_post_name`
FROM `forum_topic` `ft`
INNER JOIN `user` `opening_post_user` ON `opening_post_user`.`id` = `ft`.`userId`

 

It does use a sub-query but in this instance I feel its fine.

Edited January 4, 2013 by cpd

[Tenaciousmug]

Thank you for the response. (: I did this query in CodeIgniter and got everything working along with post count and such too