1

I'm using the following SQL query to populate a filter form for a jobs site. The query displays all locations used in the jobs listed on the site.

I'm only displaying jobs up to 45 days old on the site BUT the query is showing locations for ALL jobs, even those over 45 days old.

Does anyone know how I could amend this query to only show job locations from jobs that are 45 days old or less?

{exp:query sql='
    SELECT DISTINCT field_id_19 
    AS job_location 
    FROM exp_channel_data 
    WHERE field_id_19 <> "" 
    ORDER BY job_location
'}
    <option value="{job_location}" {if super_search_job_location == "{job_location}"}selected="selected"{/if}>{job_location}</option>
{/exp:query}

Thanks in advance,

Tom

3

You can try below query to display entries of last 45 Days.

SELECT DISTINCT field_id_19 AS HomeTitle FROM exp_channel_data t1 INNER JOIN exp_channel_titles t2 ON t1.entry_id = t2.entry_id WHERE t1.field_id_19 <> "" AND ((UNIX_TIMESTAMP() - t2.entry_date) < 3888000)
| improve this answer | |
  • That's perfect, thank you so much @Harsh Barach – Tom Perkins Feb 14 '18 at 10:31
  • Glad to help you.. – Harsh Barach Feb 14 '18 at 11:15

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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