[eluser]tim1965[/eluser]
Hi
This is probably a non CI related post but i thought i would post it here for help as this is such a great community for help.
I am struggling with a query i need to put together for searching a range of dates and prices. I already have an existing query that works great but i need to incorporate this functionality and i am going round in circles.
My existing query looks like this
$sql = "SELECT WHATEVER
FROM master_reference
JOIN master_details ON master_reference.id = master_details.id
JOIN master_pictures ON master_reference.id = master_pictures.id and master_pictures.photo_desc = 'main'
JOIN master_charges ON master_reference.id = master_charges.id
WHERE completed ='1'
AND master_details.region LIKE '$region'
AND master_details.country LIKE '$country'";
$query = $this->db->query($sql);
if($query->num_rows() >0)
{
$row=$query->result_array();
return $row;
}
I now want to add the ability to search for a price between a start and end date from this table
See next post
CREATE TABLE `master_charges` (
`id` mediumint(20) NOT NULL,
`chargesdatecreated` timestamp NOT NULL default '0000-00-00 00:00:00' on update CURRENT_TIMESTAMP,
`start_date1` date default NULL,
`start_date2` date default NULL,
`start_date3` date default NULL,
`start_date4` date default NULL,
`start_date5` date default NULL,
`start_date6` date default NULL,
`start_date7` date default NULL,
`start_date8` date default NULL,
`start_date9` date default NULL,
`start_date10` date default NULL,
`end_date1` date default NULL,
`end_date2` date default NULL,
`end_date3` date default NULL,
`end_date4` date default NULL,
`end_date5` date default NULL,
`end_date6` date default NULL,
`end_date7` date default NULL,
`end_date8` date default NULL,
`end_date9` date default NULL,
`end_date10` date default NULL,
`price_per_week_1` smallint(5) default NULL,
`price_per_week_2` smallint(5) default NULL,
`price_per_week_3` smallint(5) default NULL,
`price_per_week_4` smallint(5) default NULL,
`price_per_week_5` smallint(5) default NULL,
`price_per_week_6` smallint(5) default NULL,
`price_per_week_7` smallint(5) default NULL,
`price_per_week_8` smallint(5) default NULL,
`price_per_week_9` smallint(5) default NULL,
`price_per_week_10` smallint(5) default NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
So i need to search the start and end date in the table and compare to the POST values and then check the price_per_week value is higher than the POST price per week value. However i need to check all 10 rows (all have different field names). I can run a UNION to flatten this out into a temp table whereby all 10 rows have the same coluumn names. But i cannot figure out how to merge the UNION (see below) into the above query to get i to work.
See next post
