2. PHPRunner General questions
  3. datetime field based searching using EQUALS option not working in Adva
This topic is locked

datetime field based searching using EQUALS option not working in Adva

 		11/16/2009 5:36:10 AM
PHPRunner General questions
A
 anu author

Hi All,
I have developed web interface using

MySQL database on Linux server

PHP5.2 evaluation version
Except one following issue everything is working fine.
Information:

=========

Table name :scripts

Column name: jobstamp datatype datetime

Scripts page -job_stamp properties settings :date

View as datetime
In scripts table I have 5 records matching Job_stamp 2009-11-12 06:42:15
Scripts -list page is showing all records.
Problem:

===========

In Scripts-Advanced search page :I have tried to search the records using following option;
Job_stamp: EQUALS 2009-11-12 (selected this date using datepicker)
But No results were found.
Only EQULAS is failing,so to search the records using EQUALS option based on above datetime field

which funtion is the best option?
strtotime() or DATE_FORMAT
In which page or which file I need to add funtion /event?and how to call this funtion,so that advanced search page

will use this funtion and search the records added only on that exact date.
Please help!!
Thanks,
--Anu
D
 		danaci 11/16/2009



Hi All,
I have developed web interface using

MySQL database on Linux server

PHP5.2 evaluation version
Except one following issue everything is working fine.
Information:

=========

Table name :scripts

Column name: jobstamp datatype datetime

Scripts page -job_stamp properties settings :date

View as datetime
In scripts table I have 5 records matching Job_stamp 2009-11-12 06:42:15
Scripts -list page is showing all records.
Problem:

===========

In Scripts-Advanced search page :I have tried to search the records using following option;
Job_stamp: EQUALS 2009-11-12 (selected this date using datepicker)
But No results were found.
Only EQULAS is failing,so to search the records using EQUALS option based on above datetime field

which funtion is the best option?
strtotime() or DATE_FORMAT
In which page or which file I need to add funtion /event?and how to call this funtion,so that advanced search page

will use this funtion and search the records added only on that exact date.
Please help!!
Thanks,
--Anu


what is O/S?
A
 		anu author 11/16/2009



what is O/S?


Hi,
WindowsXP ,

Database on Linux CentOS 5

Web server also on same linux machine.
--Anu
D
 		danaci 11/16/2009

if O/S windows set date time format from regional setting.
Admin 11/16/2009

Some extra steps are required if you need to make datetime field perform as a date during the search.
One of possible workarounds - add a new calculated field to your SQL query, converting datetime to date.
Example:
select ...

datetimefield,

date(datetimefield) as datetimefield2

from ...
A
 		anu author 11/16/2009

Hi ,
Calculated column alone not solved the problem.
I have created a calculated field,after which EQUALS search returned the records added exactly

on selected day.

But list page displaying time as 12:00 ignoring the original time in the datetime field.
For example :
SELECT

OrderID,

CustomerID,

EmployeeID,

OrderDate,

DATE(OrderDate) as OrderDate2

From orders;
In search and list page I have included OrderDate2 column,search was successful,but

List page displaying time as 12:00 for all records which is wrong.
OrderDate2 returned following time :11/17/2009 12:00:00 AM
But actual time is : 2009-11-17 12:19:00
So to get rid of this problem,
In List page,I have included only original column OrderDate (edit as date)

View as custom typed following expression

$mydate = date('Y-m-d H:i:s');
In Search page,I have included only calculated field i.e OrderDate2(edit as date)

View as custom and typed same expression.

Search returned record is showing exactly converted date and time in list page

2009-11-17 12:19:00
I think this is the common problem observed by any phprunner user,who will use unix datetime datatype for columns.

This conversion is worth mentioning in phprunner tips&tricks or in manual(or in livedemos) .

Finally advanced search-EQUALS-datetime field is working successfully.
Thanks for the help!!
Regards,
---Anu