This is an archived copy of the forum for reference purposes

@vars in query

posted by darren 7 years ago

Hi Guys,

I have the following query...

SELECT DATE(pm.rstdtime) AS cntldate, sh.sd_no, sh.work_group,
	(
		IF(@sd_id = sh.sd_no, 
			(TIMESTAMPDIFF(SECOND, @dt, sh.data_entry_dtime) / 86400) * 86400 + LEAST(0, @dt := sh.data_entry_dtime),
			NULL + LEAST(0, @sd_id := sh.sd_no) + (@dt := sh.data_entry_dtime)
		)) AS duration
INTO OUTFILE '{$file}'
FIELDS TERMINATED BY '||'
OPTIONALLY ENCLOSED BY '\\"'
FROM tablea pm 
LEFT JOIN tableb sh 
	ON pm.sd_no = sh.sd_no,
(SELECT (@sd_id := 0)) AS a 
WHERE sh.log_text IN ('Created', 'Dispatched', 'Restored') 
AND CONVERT_TZ(sh.data_entry_dtime, 'GMT', 'Australia/NSW') <= pm.rstdtime 
ORDER BY sh.sd_no, sh.data_entry_dtime, sh.log_text
limit 10

When i run this inside SQLyog for instance everything works as expected, however when i use it via PHP (fDatatabase query) duration is null.

Its like fDatabase is manipulating the @vars?

Any help would be appreciated... thnaks.

I would file this as a bug. Last I checked there is some light translation work that Flourish does to normalize queries. It could be an error or lack of support there. Will would likely know exactly where that support or error is lacking. Whether or not it's easy to fix is another question.

posted by mattsah 7 years ago