0

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.

4
  • See LAG(1) instead of @prev_value.
    – Rick James
    Dec 9, 2023 at 1:02
  • Sorry @RickJames i dont know what that means
    – Adrian M
    Dec 9, 2023 at 14:47
  • Look for the LAG function in the "Windowing functions". It may help avoid using @row_num :=.
    – Rick James
    Dec 9, 2023 at 18:10
  • it would be easier for me to learn advanced rocketry i think :) thanks however for the attempt :)
    – Adrian M
    Dec 10, 2023 at 8:34

1 Answer 1

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;
6
  • Thanks ill give it run tomorrow, cheers, that looks more decipherable than the original by a factor of 1,000,000. Still over my head in toto, but i can follow some of the logic
    – Adrian M
    Dec 9, 2023 at 14:49
  • Update: sadly it did NOT like that at all....no returned data
    – Adrian M
    Dec 9, 2023 at 15:15
  • Update 2: it runs if i run it in phpmyadmin SQL console manually and manually enter a $emp_property_id in place of that in ($emp_property_id) near end it runs properly...but wont run from the existing PHP code. Guess ill have to find someone to pay to fix it as this is going to be the same issue across multiple PHP files with mysql queries in them...
    – Adrian M
    Dec 9, 2023 at 15:31
  • Hi @AdrianM . I've updated the query. Please replace your query with above updated one.
    – SammaDev
    Dec 10, 2023 at 3:08
  • Cheers @SammaDev and other respondents! That does indeed work in as much as it doesnt produce an error on page load, unlike the original, but the user then clicks on a generate button after selecting property and dates and it fires an ajax request with a different mysql query that im not even going to paste, because i was hoping that someones kindness here would give me a starting point (i wasnt looking for volunteers to correct all our issues, thats a LOT to ask, and im not going to)
    – Adrian M
    Dec 10, 2023 at 8:31

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.