Re: Dates and Count

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



Hello Shannon,


SD> First, I have two dates stored in a MySQL DB that I want to do a compare on
SD> and then only display via a web page those records that have a 5 or greater
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 to_days(date2)-todays(date1)>5
    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 DATEDIFF(date1,date2)<-5

    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 DB
SD> that have a specific number of a particular type of entry associated with
SD> them. I am assuming that the 'count' would be used, but how would I first
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,
 Pablo

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


[Index of Archives]     [PHP Home]     [PHP Users]     [Postgresql Discussion]     [Kernel Newbies]     [Postgresql]     [Yosemite News]

  Powered by Linux