使用来自不同表的两个值作为查询中的日期范围

Question

I have the following code:

<?php 
$result = $db->query("select sum(hol_count) as TOTALBOOKEDHOLIDAYS from holidays");
$row = $result->fetch_row();
echo $row[0];
?>

This does a count of hol_count and returns the calculated figure. I have a separate table called 'config' and two columns: 'hol_year_start' and 'hol_year_end' which will set the date range. I am trying include these two values in the query but have encounter a number of errors while trying a number of different ways to achieve this. I have tried to define the two values as variables and then use those variables with BETWEEN to do the count between the who date ranges. I keep getting an error regarding $row = $result->fetch_row(); as well as trying the following:

("select sum(hol_count) as TOTALBOOKEDHOLIDAYS from holidays
BETWEEN
(SELECT hol_year_start FROM config)
AND
(SELECT hol_year_end FROM config) )"

I know this is probably something very simple but I just cant seem to define the two values as variables then use them in the query to retrieve the required data.

What would be the correct way to define the two values from the config table then use them in the query to count hol_count from the holiday table?

Answer 1

You look you are missing the WHERE in you query. Also the subselects must return only one result. In case you are using MySQL, you would need to set LIMIT 1.

SELECT SUM(hol_count) AS TOTALBOOKEDHOLIDAYS FROM holidays 
WHERE date_field_name 
BETWEEN (SELECT hol_year_start FROM config LIMIT 1) 
AND (SELECT hol_year_end FROM config LIMIT 1)