Order by date doesn't work

Discussion in 'MySQL' started by kost36, Dec 28, 2021.

  1. kost36

    kost36 New Member

    Joined:
    Nov 14, 2020
    Messages:
    3
    Likes Received:
    0
    Trophy Points:
    1
    I use a query to get results by the distribution's movies per year and all corresponding critics to each of them too
    the query


    Code:
    SELECT
            CONCAT('<a href="./index.php/searchid?ID_films=',
                    `mt_films`.`ID_films`,
                    '">',
                    `mt_films`.`Title1`,
                    '</a>') AS `Title`,
            `mt_films`.`ID_films` AS `ID_films`,
            `mt_films`.`distributor` AS `distributor`,
            CASE
                WHEN `mt_films`.`poster` = '-1' THEN CONCAT(`mt_films`.`ID_films`, '.jpg')
                ELSE CONCAT('000', '.jpg')
            END AS `photo`,
            `t_country`.`country` AS `country`,
            DATE_FORMAT(`mt_films`.`date_out`, '%d/%m/%Y') AS `date_out`,
            GROUP_CONCAT(DISTINCT '<a href="./index.php/people?ID_person=',
                `t_people`.`ID_person`,
                '">',
                `t_people`.`person`,
                '</a>'
                SEPARATOR ', ') AS `director`,
            CONCAT('(',
                    CONVERT( COALESCE(`mt_films`.`product_year`, '') USING UTF8),
                    ' ',
                    COALESCE(`mt_films`.`format`, ''),
                    ' ',
                    CONVERT( COALESCE(`mt_films`.`duration`, '') USING UTF8),
                    '\')') AS `id`
        FROM
            (((`t_country`
            JOIN (`t_people`
            JOIN (`mt_films`
            JOIN `st_peoplefilms` ON (`mt_films`.`ID_films` = `st_peoplefilms`.`ID_films`)) ON (`t_people`.`ID_person` = `st_peoplefilms`.`ID_person`)) ON (`t_country`.`ID_country` = `mt_films`.`id_country`))
            LEFT JOIN `st_critics` ON (`mt_films`.`ID_films` = `st_critics`.`ID_Films`))
            LEFT JOIN `t_film_critic` ON (`t_film_critic`.`ID_name` = `st_critics`.`ID_name`))
        WHERE
            `mt_films`.`date_out` LIKE '%2021%'
                AND `st_peoplefilms`.`ID_idiotita` = 1
                AND `mt_films`.`reissue` = 0
                AND `mt_films`.`poll` = 2
        GROUP BY `mt_films`.`Title1`
        ORDER BY `mt_films`.`date_out` , `mt_films`.`Title1`
    till here everything works perfect sorting all titles alphabetically by distribution date
    But when I add

    Code:
    case when `t_film_critic`.`film_critic` is not null then
    concat('Read critics by ', group_concat(distinct '<a href="./index.php?  option=com_content&view=article&id=',
    `st_critics`.`code_critics`,
    '"','>\n{modal article="',
    `st_critics`.`code_critics`,
    '" title="',`mt_films`.`Title1`,' <br> Critic by: ',
    `t_film_critic`.`name`,'" width="750" }',
    `t_film_critic`.`name`,' {/modal}\n</a>'
    order by `t_film_critic`.`name` ASC separator ', '))
    else 'There is no available critic yet' end AS `film_critic`,
    It works but without date sorting
    Could you please help to find a solution?
     

Share This Page

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice