It depends on what you want to achieve. But looking at your query, I guess this should work:
SELECT
specify fields here instead of using *
FROM " . TBL_MAINTEXT . "
WHERE
( from_datetime <= NOW()
AND to_datetime >= NOW()
AND code = '" . $db->escape_string($code) . "'
)
OR
( start_time <= CURTIME()
AND end_time >= CURTIME()
AND message_type = 'DAILY'
)
OR
( start_time <= CURTIME()
AND end_time >= CURTIME()
AND message_day = DAYOFWEEK(NOW())
AND message_type = 'WEEKLY'
)
OR
( start_time <= CURTIME()
AND end_time >= CURTIME()
AND message_day = DAYOFMONTH(CURRENT_DATE)
AND message_type = 'MONTHLY'
)
OR
( message_default = 'Y'
)
ORDER BY message_default
Limit 1
Also, a tip: You don’t have to concatenate multiple-line strings. Your code will be much easier to read this way:
$query = "SELECT *
FROM " . TBL_MAINTEXT . " WHERE
from_datetime <= NOW()
AND to_datetime >= NOW()
AND code = '" . $db->escape_string($code) . "'
OR
start_time <= CURTIME()
AND end_time >= CURTIME()
AND message_type = 'DAILY'
OR
start_time <= CURTIME()
AND end_time >= CURTIME()
AND message_day = DAYOFWEEK(NOW())
AND message_type = 'WEEKLY'
OR
start_time <= CURTIME()
AND end_time >= CURTIME()
AND message_day = DAYOFMONTH(CURRENT_DATE)
AND message_type = 'MONTHLY'
OR
message_default = 'Y'
ORDER BY message_default ACE Limit 1";