<div>
mysql&gt; SELECT * FROM tbl_name WHERE DATE_SUB(CURDATE(),INTERVA<wbr />L 6 MONTH) &gt;= gapmtDate;<br />

</div>


<div>
Hi Rik-Legger<br />
<br />
I've tried your query and while it returns records it shows clients who have had an appointment in the last six months. I need to list clients whose last recorded visit was recorded six months or more ago.<br />
<br />

</div>


<div>
Perhaps this will give you the correct result:<br />
<br />
mysql&gt; SELECT * FROM tbl_name WHERE DATE_SUB(CURDATE(),INTERVA<wbr />L 6 MONTH) &gt;&nbsp;gapmtDate;
</div>


<div>
No Rik-Legger it gives me the same result.<br />
<br />
Here is my query<br />

<pre><code class="code-1 nolinks" id="code-20-35797787-1"><span>mysql&gt; select groomappointments.gapmtDate, clients.firstname, clients.lastname</span>
<span>    -&gt; from groomappointments,clients</span>
<span>    -&gt; WHERE date_sub(CURDATE(), INTERVAL 6 MONTH)&gt;gapmtDate</span>
<span>    -&gt; AND clients.clientid = groomappointments.gapmtClient;</span>
</code></pre>
</div>


<div>
Can you show me one of the results you are getting back, <br />
i'm curious what the gapmtDate value looks like.
</div>


<div>
Ok here is the result of the query<br />

<pre><code class="code-1 nolinks" id="code-20-35797846-1"><span>+------------+-----------+----------+</span>
<span>| gapmtDate  | firstname | lastname |</span>
<span>+------------+-----------+----------+</span>
<span>| 2010-09-26 | Karl      | Adams    |</span>
<span>+------------+-----------+----------+</span>
<span>1 row in set (0.00 sec)</span>
</code></pre>
<br />
If I execute the following query you can see that the client has appointments in the last six months
<pre><code class="code-10 nolinks" id="code-20-35797846-2"><span>mysql&gt; select groomappointments.gapmtDate, clients.firstname, clients.lastname</span>
<span>    -&gt; from groomappointments,clients</span>
<span>    -&gt; where clients.clientid = groomappointments.gapmtClient</span>
<span>    -&gt; AND clients.clientid=19;</span>
<span>+------------+-----------+----------+</span>
<span>| gapmtDate  | firstname | lastname |</span>
<span>+------------+-----------+----------+</span>
<span>| 2010-09-26 | Karl      | Adams    |</span>
<span>| 2011-03-07 | Karl      | Adams    |</span>
<span>| 2011-05-18 | Karl      | Adams    |</span>
<span>+------------+-----------+----------+</span>
<span>3 rows in set (0.00 sec)</span>
</code></pre>
<br />
<br />
<br />
<br />

</div>


<div>
Try this:<br />
<br />
SELECT * FROM clients WHERE clientid IN (SELECT gapmtClient FROM groomappointments WHERE date_sub(CURDATE(), INTERVAL 6 MONTH)&gt;gapmtDate GROUP BY gapmtClient HAVING count(gapmtClient)= 0)<br />

</div>


<div>
SELECT * FROM clients WHERE clientid IN (SELECT gapmtClient FROM groomappointments WHERE date_sub(CURDATE(), INTERVAL 6 MONTH) &lt;&nbsp;gapmtDate GROUP BY gapmtClient HAVING count(gapmtClient)= 0)<br />

</div>


<div>
Hi liveaspankaj<br />
<br />
This doesn't work as it gives me an empty set even when there are clients who only have one appointment that was recorded on 2010-09-16.<br />
<br />
<br />
<br />

</div>


<div>
Sorry liveaspankaj<br />
<br />
Neither queries work I wish we could edit comments :-)
</div>


<div>
Please see this article.<br />
<a href="https://www.experts-exchange.com/Web_Development/Web_Languages-Standards/PHP/A_201-Handling-date-and-time-in-PHP-and-MySQL.html" rel="ugc">https://www.experts-exchange.com/Web_Development/Web_Languages-Standards/PHP/A_201-Handling-date-and-time-in-PHP-and-MySQL.html</a><br />
<br />
I like to handle the date part of things in PHP - I just find the syntax easier to understand, so if I were looking to find a date that is 6 months ago, I would do something like this:<br />
<br />
$old = date('c', strtotime('Today - 6 months'));<br />
<br />
You can see how that is interpreted here:<br />
<a href="http://www.laprbass.com/RAY_strtotime.php?s=Today+-+6+months" rel="ugc">http://www.laprbass.com/RAY_strtotime.php?s=Today+-+6+months</a><br />
<br />
Any DATE value that is less than $old is older than 6 months. &nbsp;Any DATE value that is more than $old is newer than 6 months.
</div>


<div>
Thanks for the points :)<br />
<br />
Glad I could help.<br />

</div>


<div>
<div class="content wysiwyg-content">
Hi all,<br />
<br />
I have a table that records appointments for clients which among other fields has a single DATE field &nbsp;called gapmtDate that records the date of the appointment.<br />
<br />
Can anyone tell me how I can find all clients whose last visit was &nbsp;greater than six months ago from todays date.<br />
<br />
Many thanks in advance.
</div>
</div>


Hi all,

I have a table that records appointments for clients which among other fields has a single DATE field  called gapmtDate that records the date of the appointment.

Can anyone tell me how I can find all clients whose last visit was  greater than six months ago from todays date.

Many thanks in advance.

PHP Mysql Find all clients whose last visit is six months or greater

PHP is a widely-used server-side scripting language especially suited for web development, powering tens of millions of sites from Facebook to personal WordPress blogs. PHP is often paired with the MySQL relational database, but includes support for most other mainstream databases. By utilizing different Server APIs, PHP can work on many different web servers as a server-side scripting language.

MySQL is an open source, relational database management system that runs as a server providing multi-user access to a number of databases. Acquired by Oracle in 2009, it is frequently used in combination with PHP installations, powering most of the WordPress installations.