Calculate price of room based on date
A list of the price is already set in the WordPress table.
I am trying to get the price of the room based on the date selected for booking.
$query_charge = "select SUM(DATEDIFF( case when '2020-06-16' > `charge_from` then '2020-06-16' else `charge_from` end, case when '2020-06-28' < `charge_to` then '2020-06-28' else `charge_to` end) * `room_adv_cost`) from `$table` where `room_id` = '8289' AND `charge_to` >= '2020-06-16' and `charge_from` <= '2020-06-28' "; $val = $wpdb->get_var( $wpdb->prepare($query_charge) );
The query returns the sum in negative value in PHPMyAdmin in my local system which is not the problem.
When I try the same query in WordPress the result is either 0 or null.
How can I get a proper result?