RE: [PHP-DB] Dates and Count
Hi, $query=select * from clients where date between $date1 AND $date2; zareef ahmed --- Shannon Doyle [EMAIL PROTECTED] wrote: 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? Cheers, Shannon -Original Message- From: Pablo M. Rivas [mailto:[EMAIL PROTECTED] Sent: Monday, 28 June 2004 9:21 AM To: [EMAIL PROTECTED] 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 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 -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php = Zareef Ahmed :: A PHP Developer in Delhi(India). Homepage :: http://www.zasaifi.com __ Do you Yahoo!? Yahoo! Mail is new and improved - Check it out! http://promotions.yahoo.com/new_mail -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP-DB] Dates and Count
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? Cheers, Shannon -Original Message- From: Pablo M. Rivas [mailto:[EMAIL PROTECTED] Sent: Monday, 28 June 2004 9:21 AM To: [EMAIL PROTECTED] 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 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 -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
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 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
