A few months back, I was able to get some proof of concept code working. See my thread, especially the summary code in this post. I don't know whether the things I learned in that set of experiments will help, but I was able to make use of the @dmvar user-defined variable; I don't think it had to be inside of the DebugMe mySQL function to work... that's just specifically what I was trying to get at for that one. But I thus know that user-defined variables aren't out of the question in DBD::mysql.
I haven't used MySQL for many years now (not since I left Lacuna Expanse any way) so I'm by no means an expert either.
I just don't know enough about MySQL's user-defined variables. I would totally expect them to work inside of stored functions. If they work as a part of normal queries as well then it would useful if the DBD::mysql documentation at least mentioned them.
hurray I found a solution, by adding :
, (SELECT @prev_discussion:=0) pd, (SELECT @recent_post_rank:=0) rpr
SELECT x.group_discussion_id, x.comment, x.date_posted,
TO_DAYS(NOW()) - TO_DAYS(x.date_posted) AS date_posted_days
gdp.date_posted, m.username, m.realname,
@recent_post_rank := IF
@prev_discussion = gdp.group_discussion_id,
@recent_post_rank + 1,
) AS recent_post_rank,
@prev_discussion := gdp.group_discussion_id
FROM group_discussion_posts gdp
JOIN members m
ON m.memberid = gdp.memberid,
(SELECT @prev_discussion:=0) pd,
(SELECT @recent_post_rank:=0) rpr
WHERE gdp.active = 1
AND gdp.reply_to_post_id != 0
AND gdp.group_discussion_id IN (1, 2, 3)
ORDER BY gdp.group_discussion_id DESC,
WHERE recent_post_rank <= 3
It works because the user defined variables need to be initialized. Because variables are session specific they are 'retained' when using MySQL workbench. You should notice that your original query does not work in MySQL workbench on the first run but then works on subsequent runs. Using DBD you are creating a new session each time so variables never get initialized. You could use your original query by adding a set command before the execute
I have a statement including this:
@row_num := @row_num + 1 AS rownum
elsewhere in my code (in a $dbh->selectrow_array) and it works as expected, so it seems to be that it allows variables but doesn't not "store" them. Strange, as I thought DBI just sends the query to MySQL, I didn't think it needed to understand every aspect of the query?
This is tricky because I have refactored with MySQL query as my last version was very inefficient, taking 5 seconds + to execute. This one is super fast but it doesn't look like I can use it if DBI won't support it, so I'm stuck now.