Editing Drupal Search SQL I'm using the basic Drupal Search module (search.module) and I want to customize the SQL of the search. I don't know how to go about doing this.

There are two things I want to edit in the SQL:

1) Have the search return ONLY nodes with start dates (event_start_date) AFTER now. 

And

2) Return results in ascending order.

Pretty basic stuff.

Perhaps I need to use another search module entirely. I don't know.

Any thoughts or suggestions?
The Views module provides a Search view out-of-the-box (it's disabled). I would:


Turn off the default Search module.
Install Views.
Clone the default Search view in the Views administration page and call it My SITENAME Custom Search.
Add whatever filters you want to this View like the date criteria you list and adjust the deafult sorting if needed.


tip: you can easily search all the text in say the title and body field(s) of your Nodes with 1 text search box by adding a Filter of type Global: Combine Filter which uses the title and body fields in your Views: Fields section.

note for really really really massive sites you'll want to actually use a robust search engine. But as a proof of concept or small site (less than says tens of thousands of nodes) this should work Ok.

EDIT

To use the existing site search mechanism you would use hook_query_alter to change the query array to add SQL conditions to the Where clause of the query statement with whatever conditions you want. Note, that perhaps you may not have the sql fields needed for the query logic (you might thought). And if thats the case you would then need to use sql Joins to be able to reference the fields in SQL you want to do business logic on. Then add/modify a sort by sql clause, the same caveats about fields applies here again.

My Views solution above is 5 minutes worth of simple configuration and then adjusting some CSS. This solution could be 5 minutes if all the fields are in the SQL query already -- or much longer to figure out the necessary joins and syntax. The Devel module can help you inspect the raw search query. Good luck.