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.
(caveat: not an SQL or mySQL expert)
| [reply] [Watch: Dir/Any] [d/l] [select] |
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.
| [reply] [Watch: Dir/Any] |
hurray I found a solution, by adding :
, (SELECT @prev_discussion:=0) pd, (SELECT @recent_post_rank:=0) rpr
as follows:
SELECT x.group_discussion_id, x.comment, x.date_posted,
x.username, x.realname,
TO_DAYS(NOW()) - TO_DAYS(x.date_posted) AS date_posted_days
FROM (
SELECT gdp.group_discussion_post_id,
gdp.group_discussion_id, gdp.comment,
gdp.date_posted, m.username, m.realname,
@recent_post_rank := IF
(
@prev_discussion = gdp.group_discussion_id,
@recent_post_rank + 1,
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,
gdp.group_discussion_post_id DESC
) x
WHERE recent_post_rank <= 3
Hopefully this might help someone else in the future. I'm not sure exactly why it works, but it creates a subquery to select each user defined variable, and an alias for each (which is never used). | [reply] [Watch: Dir/Any] [d/l] [select] |
$dbh->do('SET @recent_post_rank := 0, @prev_discussion := 0');
poj | [reply] [Watch: Dir/Any] [d/l] |
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?
| [reply] [Watch: Dir/Any] [d/l] |
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.
| [reply] [Watch: Dir/Any] |