Out of my depth with post mysql 8 update (Warning: #1287 Setting user variables within expressions is deprecated)

Question

Now I can get my way around PHP, and I can get around basic MySQL queries, but I'm not really comfy with MySQL, and have inherited an issue that occurred today after our hosting company updated their copy of MySQL on the shared server our PHP code runs on, and suddenly no data returned on a report in the business owners web browser. As a friend of his I have access to the server and have fixed a bug in the PHP code about 2 years ago. But that's the only change made to the server since 2019, where the code was written by a subcontractor, who has since left.

So today I logged in and found the code referenced in the PHP error, and then found the PHP error with the SQL statement it was trying to run, and upon running in on the server, live in the phpmyadmin SQL tab, I got perfectly good data back and then the error (error is what I assume tanks the PHP returning any usable data to the user in the report):

Warning: #1287 Setting user variables within expressions is deprecated and will be removed in a future release. Consider alternatives: 'SET variable=expression, ...', or 'SELECT expression(s) INTO variables(s)'.

...and despite looking at 20 examples of how to rewrite things to be complaint for simple queries, it's quite intimidating when you're facing the following as the query it's trying to run:

$sql = " Select tbase.property_name, tbase.room_number, tbase.customer_first_name, tbase.customer_last_name, tbase.amount_paid, tbase.payment_date, tbase.payment_method,"
 . " case when tbase.RowNumber = 1 then tbase.refund_amount else '' end as refundamount,tbase.recipt_number,tbase.room_standard_weekly_rate, tbase.discount_amount,tbase.Weekly_tariff from (Select base.property_name, base.room_number, base.customer_first_name, base.customer_last_name, base.amount_paid, base.payment_date, base.payment_method, @row_num := IF(@prev_value=concat_ws('',base.property_name,base.room_number,base.refund_amount),@row_num+1,1) AS RowNumber," 
. "  @prev_value := concat_ws('',base.property_name,base.room_number,base.refund_amount) ,base.refund_amount,base.recipt_number,base.room_standard_weekly_rate,base.discount_amount,base.Weekly_tariff
from (SELECT distinct hmprop.`property_name` property_name,hmroom.`room_number` room_number,hmcust.customer_first_name AS customer_first_name,"
. " hmcust.customer_last_name AS customer_last_name, hmpayment.`amount_paid` amount_paid,
hmpayment.`payment_date` payment_date,hmpayment.`payment_method` payment_method, hmbook.`refund_amount` refund_amount,hmpayment.`recipt_number` recipt_number, hmroom.`room_standard_weekly_rate`,discount.`discount_amount` discount_amount,case when hmroom.`room_standard_weekly_rate` <> hmbook.`weekly_tariff` then 'Yes' else 'No' END as Weekly_tariff" 
. " FROM `hm_booking` hmbook inner join `hm_room` hmroom ON hmbook.`room_id`=hmroom.`room_id` INNER JOIN  hm_customer hmcust ON hmbook.customer_id=hmcust.customer_id
 INNER JOIN  hm_booking_payment hmpayment ON hmbook.booking_id=hmpayment.booking_id "
. " inner join `hm_property` hmprop on hmprop.`property_id`=hmroom.`property_id` LEFT JOIN hm_booking_discount discount on discount.id = hmpayment.discount_id 
where   hmprop.`property_id` IN($emp_property_id) "
. " order by hmprop.`property_name`, hmroom.`room_number` ASC ) base join (SELECT @row_num := 1)  x  join   (SELECT @prev_value := '') y) tbase";

I'd be grateful if anyone could even give me a starting point.

Answer 1

This is a sample solution. Please customize this solution as you want. (Updated 2023-12-10 8.35am IST)

    SELECT
    base.property_name,
    base.room_number,
    base.customer_first_name,
    base.customer_last_name,
    base.amount_paid,
    base.payment_date,
    base.payment_method,
    base.refund_amount,
    base.recipt_number,
    base.room_standard_weekly_rate,
    base.discount_amount,
    base.Weekly_tariff
FROM (
    SELECT
        hmprop.property_name,
        hmroom.room_number,
        hmcust.customer_first_name,
        hmcust.customer_last_name,
        hmpayment.amount_paid,
        hmpayment.payment_date,
        hmpayment.payment_method,
        hmbook.refund_amount,
        hmpayment.recipt_number,
        hmroom.room_standard_weekly_rate,
        discount.discount_amount,
        CASE WHEN hmroom.room_standard_weekly_rate <> hmbook.weekly_tariff THEN 'Yes' ELSE 'No' END AS Weekly_tariff,
        ROW_NUMBER() OVER (PARTITION BY hmprop.property_name, hmroom.room_number, hmbook.refund_amount ORDER BY hmprop.property_name, hmroom.room_number) AS RowNumber
    FROM
        hm_booking hmbook
        INNER JOIN hm_room hmroom ON hmbook.room_id = hmroom.room_id
        INNER JOIN hm_customer hmcust ON hmbook.customer_id = hmcust.customer_id
        INNER JOIN hm_booking_payment hmpayment ON hmbook.booking_id = hmpayment.booking_id
        INNER JOIN hm_property hmprop ON hmprop.property_id = hmroom.property_id
        LEFT JOIN hm_booking_discount discount ON discount.id = hmpayment.discount_id
    WHERE
        hmprop.property_id IN ($emp_property_id)
    ORDER BY
        hmprop.property_name,
        hmroom.room_number ASC
) base
WHERE
    base.RowNumber = 1;