Radicore Forum
Fast Uncompromising Discussions. FUDforum will get your users talking.

Home » RADICORE » How To » Failing searches, caused by null-able date fields
Failing searches, caused by null-able date fields [message #1367] Tue, 10 June 2008 09:24 Go to next message
ikatz is currently offline  ikatz
Messages: 40
Registered: December 2007
Location: Durham, NH
Member
I was seeing some very odd search behavior, which caused most of my searches to return 0 results ("Nothing was found matching this profile"). The behavior was really confusing because some of the searches would return results the first time, but clicking "search" a second time and submitting the same search criteria would return no results.

By logging the SQL queries, I was able to determine that my problem is being caused by 2 date fields in this particular table which can be null. Radicore is inventing search criteria from the date fields, even when I leave them blank.

Note: The table I am working on here is called task, and is part of a project manager I am working on -- not the Radicore menu system.

SELECT SQL_CALC_FOUND_ROWS task.*, 
  project.name as project_valrep, 
  parent_task.name as parent_task_name 
FROM task 
  LEFT JOIN project 
    ON (project.project_id=task.project_id) 
  LEFT JOIN task AS parent_task 
    ON (parent_task.task_id=task.parent_task_id) 
WHERE task.name LIKE 'fred%' 
  AND task.start_date<='2008-06-10 00:00:00' 
  AND task.end_date>='2008-06-10 23:59:59'    
LIMIT 10 OFFSET 0


Start_date and end_date are my null-able fields. At first I thought that Radicore was simply generating default conditions on the date fields, since both were null. But what is really confusing to me is the fact that the 2 conditions are different

  AND task.start_date<='2008-06-10 00:00:00' 
  AND task.end_date>='2008-06-10 23:59:59'    


Are task.start_date and task.end_date reserved in Radicore? How can I prevent these conditions from being added to the search criteria?
Re: Failing searches, caused by null-able date fields [message #1368 is a reply to message #1367] Tue, 10 June 2008 12:54 Go to previous messageGo to next message
AJM is currently offline  AJM
Messages: 2350
Registered: April 2006
Location: Surrey, UK
Senior Member
If a database table contains both a START_DATE and END_DATE field then when a search screen is initiated a field called CURR_OR_HIST is automatically added to the $fieldspec array, with the default value of 'C', to activate the feature described in http://www.tonymarston.net/php-mysql/infrastructure-faq.html #faq53. If you do not want this feature then you can remove this CURR_OR_HIST field from the data which is returned by the search screen using code in the _cm_changeConfig() method such as:
if ($GLOBALS['mode'] == 'search') {
    unset($this->fieldspec['curr_or_hist']);
} // if


icon14.gif  Re: Failing searches, caused by null-able date fields [message #1369 is a reply to message #1368] Tue, 10 June 2008 14:37 Go to previous messageGo to next message
ikatz is currently offline  ikatz
Messages: 40
Registered: December 2007
Location: Durham, NH
Member
Perfect. Thanks!

While I'm thinking about it, is there a list somewhere of field names that Radicore treats specially?

[Updated on: Tue, 10 June 2008 14:38]

Report message to a moderator

Re: Failing searches, caused by null-able date fields [message #1370 is a reply to message #1369] Tue, 10 June 2008 19:30 Go to previous message
AJM is currently offline  AJM
Messages: 2350
Registered: April 2006
Location: Surrey, UK
Senior Member
Take a look at http://www.tonymarston.net/php-mysql/infrastructure-faq.html #faq89.

Previous Topic: Use update2 pattern when I have two tables parent->child
Next Topic: Screen structure reference for filepicker-ed images
Goto Forum:
  


Current Time: Mon Jun 17 18:21:50 EDT 2024

Total time taken to generate the page: 0.01017 seconds