1

I'm trying to execute a query similar to this:

SET @last_date = NULL;
SELECT
    IF(dates.first_entry = 1, DATE_FORMAT(FROM_UNIXTIME(col_id_122), '%W, %M %d'), NULL) start_date,
    DATE_FORMAT(FROM_UNIXTIME(col_id_122), '%H:%i') start_time,
    col_id_122 start_epoch,
    DATE_FORMAT(FROM_UNIXTIME(col_id_124), '%W, %M %d') end_date,
    DATE_FORMAT(FROM_UNIXTIME(col_id_124), '%H:%i') end_time,
    col_id_124 end_epoch,
    col_id_126 session_title,
    col_id_128 name_this_field,
    col_id_130 entry_group,
    first_entry,
    entries entries_for_date FROM exp_channel_grid_field_288
NATURAL JOIN (
    SELECT row_id, CASE WHEN jc = CAST(COALESCE(@last_date, 1) AS DATE) THEN 0 ELSE 1 END first_entry, @last_date := jc start_date FROM (
        SELECT row_id, CAST(FROM_UNIXTIME(col_id_122) AS DATE) jc FROM exp_channel_grid_field_288 ORDER BY jc
    ) d
) dates
RIGHT JOIN (
    SELECT COUNT(jc) entries, jc FROM ( SELECT row_id, CAST(FROM_UNIXTIME(col_id_122) AS DATE) jc FROM exp_channel_grid_field_288 ORDER BY jc ) c GROUP BY jc
) entries ON dates.start_date = entries.jc
;

I have also tried the following:

SELECT * FROM (
SELECT
    NULL start_date,
    NULL start_time,
    NULL start_epoch,
    NULL end_date,
    NULL end_time,
    NULL end_epoch,
    NULL title,
    NULL name_this_field,
    NULL entry_group,
    NULL first_entry,
   NULL entries_for_date
FROM ( SELECT @last_date := NULL ) blank
UNION SELECT
    DATE_FORMAT(FROM_UNIXTIME(col_id_122), '%W, %M %d') start_date,
    DATE_FORMAT(FROM_UNIXTIME(col_id_122), '%H:%i') start_time,
    col_id_122 start_epoch,
    DATE_FORMAT(FROM_UNIXTIME(col_id_124), '%W, %M %d') end_date,
    DATE_FORMAT(FROM_UNIXTIME(col_id_124), '%H:%i') end_time,
    col_id_124 end_epoch,
    col_id_126 title,
    col_id_128 name_this_field,
    col_id_130 entry_group,
    first_entry,
    entries entries_for_date FROM exp_channel_grid_field_288
NATURAL JOIN (
    SELECT row_id, CASE WHEN jc = @last_date THEN 0 ELSE 1 END first_entry, @last_date := jc start_date FROM (
        SELECT row_id, CAST(FROM_UNIXTIME(col_id_122) AS DATE) jc FROM exp_channel_grid_field_288 ORDER BY jc
    ) d
) dates
RIGHT JOIN (
    SELECT COUNT(jc) entries, jc FROM ( SELECT row_id, CAST(FROM_UNIXTIME(col_id_122) AS DATE) jc FROM exp_channel_grid_field_288 ORDER BY jc ) c GROUP BY jc
) entries ON dates.start_date = entries.jc
) date LIMIT 1, 18446744073709551615;

They both execute as if the @session_variable is not being used. Does Expression Engine do something to escape the @?

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.