Hi Pablo,

Its exactly what I wanted. Thanks.

I now have an additional question.

I need to show results that appear between two dates as specified by the
site visitor.

At the moment I am using the following SQL:

WHERE NextContact >= '$today' && NextContact <= '$week'";

Second I need the same scenario, however I want to search from a variety of
different fields, show those that are between the dates and are equal to one
other criteria.

Any help on these two?



-----Original Message-----
From: Pablo M. Rivas [mailto:[EMAIL PROTECTED] 
Sent: Monday, 28 June 2004 9:21 AM
Subject: Re: [PHP-DB] Dates and Count

Hello Shannon,

SD> First, I have two dates stored in a MySQL DB that I want to do a compare
SD> and then only display via a web page those records that have a 5 or
SD> day difference in them. What would be the best way to achieve this.
Depends on your mysql version...
    select * from mytable where date1 + INTERVAL 5 DAY<=date2 or date2 +
INTERVAL 5 DAY <=date1;

    select * from mytable where to_days(date1)-todays(date2)>5 or
    from the mysql manual: "For other dates before 1582, results from this
function are undefined"

    if your mysql > 4.1.1:
    select * from mytable where DATEDIFF(date1,date2)>5 or

    but take a look to optimization.. the first script took 0.0234
    secs to return 256 row of 3096 rows
    the second took 0.0413 secs to return the same
    couldn't test the third (I have a mysql  4.0.18-standard-log)
SD> Second, I want to be able to return a list of 'clients' from the MySQL
SD> that have a specific number of a particular type of entry associated
SD> them. I am assuming that the 'count' would be used, but how would I
SD> display this count, and second only return those that have the number of
SD> entries that I am looking for.

    Select count(operation_id) as howmany, client_id, client_name from
    clients left join operations on operations.client_id =
    clients.client_id group by clients.client_id having howmany=5

    Then... mysql_num_rows will give you how many clients have 5
    operations, and each row will tell you:
    howmany (always = 5), id of the client, and name of the client.

    żis this what you where looking for?...

Best regards,

PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Reply via email to