Beefy Boxes and Bandwidth Generously Provided by pair Networks
Welcome to the Monastery

Re^2: Perl DBI not storing @prev_value in query

by Beaker (Beadle)
on Jan 24, 2018 at 18:04 UTC ( [id://1207853] : note . print w/replies, xml ) Need Help??

in reply to Re: Perl DBI not storing @prev_value in query
in thread Perl DBI not storing @prev_value in query

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 = 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).

Replies are listed 'Best First'.
Re^3: Perl DBI not storing @prev_value in query
by poj (Abbot) on Jan 25, 2018 at 18:55 UTC
    I'm not sure exactly why it works

    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

    $dbh->do('SET @recent_post_rank := 0, @prev_discussion := 0');