Hi,

I am trying to implement a dynamic search-field with ajax. The clonable template I want to search in has different editables that should be searched trough. So I would like to have one search input field searching through a set of editables at once.

The solution would be very simple using something like:

Code: Select all

<cms:set q="<cms:gpc 'q' />" />
<cms:pages masterpage='searchable_template.php' custom_field="first_name=<cms:show q /> OR last_name=<cms:show q /> OR street=<cms:show q /> OR ..." >
    ... output all found pages as table ...
</cms:pages>

Unfortunately the custom_field option only has the | separator which combines the filters with AND.

How can I achieve this with e.g. a sql select?

Thanks,
Olliwalli

Hi olliwalli,

If you add return_sql='1' to the end of your cms:pages tag so it looks like this

Code: Select all

<cms:set q="<cms:gpc 'q' />" />
<cms:pages masterpage='searchable_template.php' custom_field="first_name=<cms:show q />| last_name=<cms:show q />|street=<cms:show q />" return_sql='1'>
    ... output all found pages as table ...
</cms:pages>

This will output an SQL select query, most notably giving you the field_id of each editable variable. Once you have this code you can change the AND to OR in the sql code. You can then format it using the <cms:query> tag

Code: Select all

<cms:query sql="INSERT-SQL-QUERY" limit='100'>    ... output all found pages as table ...
</cms:query> 

Hi Keakie

Thanks for this very interesting hint!

I have found already a solution where I run cms:pages with the option ids_only='1' several times and then consolidate these lists together with cms:php.

Using this approach I was able to create a multi-substing-search: When somebody types in e.g. "omas uster" it finds a record with firstname="Thomas" and lastname="Muster".

Here is my solution, if someone can make use of it. Probably I will use the SQL-Variant later, this might give better performance:

Code: Select all

<!-- Getting the search srting sent via POST-->
<cms:set search_string="<cms:gpc 'search' method='post' />" />

<!-- Creating substrings where a blank is -->
<cms:set search_strings="<cms:php>echo trim(implode('|', explode(' ', '<cms:show search_string />')),'|');</cms:php>" />

<cms:set found_ids="" scope='global' />

<!-- Cycling through all substrings -->
<cms:each search_strings>
   <cms:set tmp_ids="" scope='global' />

   <!-- Search in multiple editables with OR: For each substring look at the relevant editables and getting ids for all found cloned pages, create a list of all ids, even if ids are listed multiple times -->
   <cms:set tmp_ids="<cms:show tmp_ids />,<cms:pages masterpage='anfragen.php' custom_field="gewerk=<cms:show item />" ids_only='1' />" scope='global' />
   <cms:set tmp_ids="<cms:show tmp_ids />,<cms:pages masterpage='anfragen.php' custom_field="status=<cms:show item />" ids_only='1' />" scope='global' />
   <cms:set tmp_ids="<cms:show tmp_ids />,<cms:pages masterpage='anfragen.php' custom_field="datum_anfrage=<cms:show item />" ids_only='1' />" scope='global' />
   <cms:set tmp_ids="<cms:show tmp_ids />,<cms:pages masterpage='anfragen.php' custom_field="preis=<cms:show item />" ids_only='1' />" scope='global' />
   <cms:set tmp_ids="<cms:show tmp_ids />,<cms:pages masterpage='anfragen.php' custom_field="lead_id=<cms:show item />" ids_only='1' />" scope='global' />
   <cms:set tmp_ids="<cms:show tmp_ids />,<cms:pages masterpage='anfragen.php' custom_field="nachname=<cms:show item />" ids_only='1' />" scope='global' />
   <cms:set tmp_ids="<cms:show tmp_ids />,<cms:pages masterpage='anfragen.php' custom_field="vorname=<cms:show item />" ids_only='1' />" scope='global' />

   <!-- Cleaning the list of found ids with array_unique(), so every page is listed only once -->
   <cms:set tmp_ids="<cms:php>echo trim(implode(',', array_unique(explode(',', '<cms:show tmp_ids />'))),',');</cms:php>" scope='global' />

   <!-- The substrings should be connected with AND, so I use array_intersect() -->
   <cms:if found_ids != ''>
      <cms:set found_ids="<cms:php>echo trim(implode(',', array_intersect(explode(',', '<cms:show tmp_ids />'), explode(',', '<cms:show found_ids />'))),',');</cms:php>" scope='global' />
   <cms:else/>
      <cms:set found_ids=tmp_ids scope='global' />
   </cms:if>
</cms:each>

<!-- Outputting the search result -->
<cms:pages masterpage='anfragen.php' id="<cms:show found_ids />" >
   <tr id="<cms:show k_page_id />" ic-get-from="<cms:link masterpage='app/api.php' />anfragen/<cms:show k_page_id />/view" ic-target="#details">
      <td><cms:show gewerk /></td>
      <td><cms:php>echo str_replace(" ", "&nbsp;", "<cms:show status />");</cms:php></td>
      <td><cms:show datum_anfrage /></td>
      <td><cms:show preis /></td>
      <td><cms:show lead_id /></td>
      <td><cms:show nachname />, <cms:show vorname /></td>
   </tr>
</cms:pages>

Thanks for posting your solution, I'm gonna borrow this