Hello,

I have a page with "searchable=1" as a parameter so that I can make searches with keywords in the admin panel.

What I don't understand is what field is used to scan for the keywords?
I guess it's the "k_page_title" field?

But even then, it's not working 95% of the time. For some reasons, sometimes it does work and find what I typed, but most of the time it doesn't.

For example, here's the title field of a page I'm trying to find :
Julie Doe / julie.doe@example.com / 2023-10-04 09:38:30

I typed "julie" in the search field, and it's not showing. But another entry similar was found :
Julie Niral / julie.niral@example.ca / 2023-09-19 09:47:25

But not "Julie Doe".

I tried searching for any other keywords stored in that Julie Doe entry, and nothing is showing.

I really don't understand what is going on, and why one was found but not the other.
Does the search bar look for other fields than page_title?
If so, can I specify which fields I want to be used to search?

I tried comparing both entries (the ones that can be found, and the ones that cannot) and I can't find anything that could cause this in the text saved in each fields.

Here's the code of the entire top section of the "leads.php" page :

Code: Select all

<cms:template title='Leads' order="300" clonable='1' icon="graph" executable='1'>
<cms:config_list_view order='desc' orderby='publish_date' limit="50" searchable="1" exclude='default'>
  <cms:html>
        <cms:show_info heading='' >
            Vous voyez seulement les leads qui vous sont attribués.
        </cms:show_info>
    </cms:html>
   <cms:field 'k_selector_checkbox' />

   <cms:field 'form_prenom' header="Nom" search_type='text' sortable='1'>
  <a href="<cms:admin_link />"><cms:show form_prenom/>   <cms:show form_nom/></a>
 </cms:field>
   <cms:field 'form_entreprise' header="Entreprise">
  <cms:show form_entreprise/>
 </cms:field>
    <cms:field 'form_courriel' header="Courriel">
  <a href="mailto:<cms:show form_courriel/>"><cms:show form_courriel/></a>
 </cms:field>
     <cms:field 'form_telephone' header="Téléphone">
  <cms:show form_telephone/>
 </cms:field>
    <cms:field 'form_date' header="Date de l'événement">
    <cms:if form_date><cms:show form_date/></cms:if><cms:if form_heure> à <cms:show form_heure/></cms:if>
    </cms:field>

       <cms:field 'form_conferencier' header="Conférencier">
    <cms:show form_conferencier/>
   </cms:field>

           <cms:field 'author' header="Pris en charge">
            <cms:if author_en_charge == 'Non déterminé'>
  <span style="color: red;"><cms:show author_en_charge/></span>
</cms:if>
<cms:if author_en_charge != 'Non déterminé'>
  <span style="color: green;font-weight: bold;"><svg class="i" style="fill:green;"><use xlink:href="http://db.orizon.ca/edit/theme/_system/assets/open-iconic.svg#check"></use></svg> <cms:show author_en_charge/></span>
</cms:if>
 </cms:field>

 


     <cms:field 'dernier_contact' header="Dernière activité">
<cms:show_repeatable 'notes_user'>
   <cms:if k_count == k_total_records>
        <cms:date form_lead_notes_date format='%d %B %Y, %R' locale='fr_FR.UTF-8'/>
    </cms:if>
</cms:show_repeatable>
    </cms:field>


   <cms:field 'k_page_date' header="Reçu le" >
    <cms:date k_page_date format='%d %h %Y, %R' locale='fr_FR.UTF-8' />
    </cms:field>


        <cms:if k_user_access_level ge '10'>
   <cms:field 'k_actions' />
        </cms:if>

  

</cms:config_list_view>

<cms:config_form_view >
<cms:editable type="group" name="gestion_group" label="Gestion du lead" order="0" collapsed='1'/>
<cms:editable type="group" name="contact_group" label="Info du contact" order="1" collapsed='1'/>
<cms:editable type="group" name="evenement_group" label="Info sur l'événement" order="10" collapsed='1'/>
<cms:editable type="group" name="notes_group" label="Activité et notes" order="20" collapsed='1'/>
<cms:editable type="group" name="notifications" label="Notifications" order="30" collapsed='1'/>


          <cms:editable name='form_demande_type' label="Type de formulaire" type='dropdown' opt_values="Demande info | Demande direct conférencier | Ajouté manuellement" />      
          <cms:editable name='form_sent_from' label="Envoyé de la page..." type='text' /> 
                   <cms:editable name='form_browser_details' label="Détails sur le navigateur" type='text' />


          <cms:editable name='form_lead_status' label="Status" type='dropdown' opt_values="Nouveau | Contacté et en attente | En discussion active | Proposition envoyée | VENDU | Perdu" group="gestion_group"/>      
        <cms:editable name='author_en_charge' label='Pris en charge par' opt_values='Non déterminé | Tous | Nancy Brault | David Larose | Diana Trejo' type='dropdown' search_type='text' group="gestion_group"/>   
   
  
          <cms:editable name='form_entreprise' label="Entreprise" type='text' group='contact_group' />
          <cms:editable name='form_prenom' label="Prénom" type='text' group='contact_group' />
          <cms:editable name='form_nom' label="Nom" type='text' group='contact_group' />
          <cms:editable name='form_courriel' label="Courriel" type='text' group='contact_group' />
          <cms:editable name='form_telephone' label="Telephone" type='text' group='contact_group' />
          <cms:editable name='form_conferencier' label="Conférencier demandé" type='checkbox' opt_values="<cms:pages masterpage='conferenciers.php' orderby='page_title' order='asc'><cms:show prenom_conf/> <cms:show nom_conf/> ,</cms:pages> " separator=',' group='evenement_group' />  
          <cms:editable name='form_date' label="Date" type='text' group='evenement_group' />
          <cms:editable name='form_heure' label="Heure" type='text' group='evenement_group' />
          <cms:editable name='form_type' label="Type d'événement" type="dropdown" opt_values='Choisir... | Présentiel | Virtuel | Hybride | Ambassadeur et porte-parole | Spectacle corporatif ou humoriste' group='evenement_group' />
          <cms:editable name='form_adresse' label="Adresse" type='text' group='evenement_group' />
          <cms:editable name='form_participants' label="Nombre de participants" type='text' group='evenement_group' />
          <cms:editable name='form_info' label="Info sur l'événement" type='textarea' group='evenement_group' />

      <cms:repeatable name='notes_user' label="Détails des activités" group="notes_group" order="10">
          <cms:editable name='form_lead_notes' label="Notes" type='textarea' class="test"/>
          <cms:editable type='datetime' name='form_lead_notes_date' minute_steps="1" label="Date" format='dmy' allow_time='1' months='janvier, février, mars, avril, mai, juin, juillet, août, septembre, octobre, novembre, décembre' default_time="@current" />
          <cms:editable name='author' label='Créé par' opt_values='list_authors.htm' opt_selected = 'current_author.htm' group="info_base" dynamic='opt_values | opt_selected' type='dropdown' />
</cms:repeatable>

          <cms:editable name='logged_user' label="Utilisateur" type='text' order="999" />

<!--  -->
<cms:editable name='notif_row_envoi_propo' group="notifications" type='row' order='355'>
<cms:editable type='datetime' default_time='@current' name='date_envoi_notif_envoi_propo' minute_steps="1" label="ENVOI PROPOSITION" format='dmy' allow_time='1' months='janvier, février, mars, avril, mai, juin, juillet, août, septembre, octobre, novembre, décembre' class='col-xs-4' order="40"/>
      <cms:repeatable name='notif_envoi_propo' label="Destinataires" order="50" class='col-xs-4'>
      <cms:editable name="notif_envoi_propo" type="text" label="Courriel"/>
</cms:repeatable>
<cms:editable type="checkbox" name="activer_notif_envoi_propo" opt_values="Oui" label="Activer?" order="60" class='col-xs-4'/>
</cms:editable>
<cms:field 'sep_notif_envoi_propo' label="-" group="notifications" order="356">
<style type="text/css">#k_label_f_sep_notif_envoi_propo {display: none;}</style>
<hr style="border:1px solid ccc;">
</cms:field>
<!--  -->

</cms:config_form_view>
</cms:template>

Thank you very much

[trendoman]

A custom solution is required to make search really nice - autocomplete, suggestions, custom HTML for results (even cards with pic), better hiliting of keywords etc.

As to your enquiry, here are a few tips.

The search is not limited to only title. The search is done by <cms:search> tag - through all fulltext content, so found all pages that contain word 'julie' somewhere in them. A page with 'Julie Doe' must be also present in the results, unless it is in an excluded template (or not in those included as inadvertently happens with multilang solutions).

Next, about "doe": basic search can not find in the fulltext index anything less than 4 chars (3, with KK's mod) or more than 84. If search phrase were written as "doe*" then it will be found by the basic search because of the wildcard. Basic search will look for all pages that contain words starting with "doe" that are >=4 and <=84 characters long. You will be probably surprised, but basic search also can find pages with words starting with "d", e.g. 1-letter search phrase, if search phrase is written with the wildcard "*". Wildcard "*" before the searched word is not supported.

Exclude all unnecessary fields via ***searchable='0'*** and update existing fulltext field in db by resaving each page.

Exclude unneeded templates: <cms:search masterpage='NOT news.php, blog.php' >..</cms:search>

May replace Search with Filter, a very basic example viewtopic.php?f=4&t=10894#p27865

Values from checkboxes are not included by default, but Repeatable regions, Mosaic are searchable (only via fulltext i.e. not filterable)

Thanks

I have checked, and there's really no difference I can find between the 2 entries.
One shows in search result, the other doesn't.
I even checked in Phpmyadmin the data, under the "couch_data_text" table, comparing both entries, and they look the same in both the "value" and "search_value" column.

I'm using a script on my website that sends the information from a form, directly to my couch website. Injecting the data directly in the MySQL database.
Right now, I'm injecting a new line only in "couch_data_text". The entries are being added correctly and everything works perfectly, except the search in the admin. When I add an entry manually, it works. So that's probably linked.

Is there somewhere else I should add data to make sure the search bar works for these entries?

Also, I replace the last name with "Doe" for privacy purposes, the real last name is longer and I'm aware that the search bar doesn't find stuff under 4 characters.

I know this is kind of a complex issue, but I've been trying to fix this for hours and cannot find a way.

Here's my code injection part :

Code: Select all

<cms:php>

// Server Details
$servername = "localhost";
$username = "root";
$password = "root";
$dbname = "dbname";

// Create a connection
$conn = mysqli_connect($servername, $username, $password, $dbname);

// Check the connection
if (!$conn) {
     die("Connection failed: " . mysqli_connect_error());
}

echo "Connected successfully<br/>";

$entreprise = mysqli_real_escape_string($conn, $_POST['q4_entreprise']);
$prenom = mysqli_real_escape_string($conn, $_POST['q5_votreprenom']);
$nom = mysqli_real_escape_string($conn, $_POST['q5_votrenom']);
$courriel = mysqli_real_escape_string($conn, $_POST['q6_courriel']);
$telephone = mysqli_real_escape_string($conn, $_POST['q7_telephone']);
$dateyear = mysqli_real_escape_string($conn, $_POST['q10_dateetyear']);
$datemonth = mysqli_real_escape_string($conn, $_POST['q10_dateetmonth']);
$dateday = mysqli_real_escape_string($conn, $_POST['q10_dateetday']);
$datetime = mysqli_real_escape_string($conn, $_POST['q10_dateettimeinput']);
$datetimeofday = mysqli_real_escape_string($conn, $_POST['q10_dateetampm']);
$typeevent = mysqli_real_escape_string($conn, $_POST['q_typeevent']);
$adresse = mysqli_real_escape_string($conn, $_POST['q8_adressede']);
$participants = mysqli_real_escape_string($conn, $_POST['input_participants']);
$conferencier = "<cms:show profile_prenom/> <cms:show profile_nom/>";
$adresse = mysqli_real_escape_string($conn, $_POST['q8_adressede']);
$info = mysqli_real_escape_string($conn, $_POST['q11_informationsur']);
$date_value = $dateyear . "-" . $datemonth . "-" . $dateday;
$time_value = $datetime . " " . $datetimeofday;


$current_date = date("Y-m-d H:i:s"); // current date and time in 'Y-m-d H:i:s' format

$pagetitle = $current_date . "-" . $prenom . "-" . $nom;
$pagename = $current_date . "-" . $prenom . "-" . $nom;
// Transliterate characters to ASCII equivalent
$pagename = iconv('UTF-8', 'ASCII//TRANSLIT', $pagename);
// Convert to lowercase, replace spaces with dashes, remove anything that's not a dash, a number or a letter
$pagename = strtolower(preg_replace('/[^a-z0-9\-]/', '', str_replace(' ', '-', $pagename)));

// Create the actual entry
$sql = "INSERT INTO couch_pages 
(template_id, page_title, page_name, creation_date, modification_date, publish_date, status, is_master, page_folder_id, access_level, comments_count, comments_open, nested_parent_id, weight, show_in_menu, menu_text, is_pointer, pointer_link, pointer_link_detail, open_external, masquerades, strict_matching, file_name, file_ext, file_size, file_meta, creation_IP, k_order, ref_count)
VALUES 
('119', '$pagetitle', '$pagename', '$current_date', '$current_date', '$current_date', NULL, 1, -1, 0, 0, 1, -1, 0, 1, NULL, 0, NULL, NULL, 0, 0, 0, NULL, NULL, 0, NULL, '::1', 0, 1)";

if (mysqli_query($conn, $sql)) {
      $last_id = mysqli_insert_id($conn);
} else {
    error_log("Error: " . $sql . " - " . mysqli_error($conn)); // Log error
}

$queries = [
    
    "INSERT INTO couch_data_text (page_id, field_id, value, search_value) VALUES ('$last_id', '830', '', '')",//Notes manuelles user
        "INSERT INTO couch_data_text (page_id, field_id, value, search_value) VALUES ('$last_id', '832', 'Non déterminé', 'Non déterminé')",//Pris en charge par
    "INSERT INTO couch_data_text (page_id, field_id, value, search_value) VALUES ('$last_id', '817', '', '')",//info navigateur
   "INSERT INTO couch_data_text (page_id, field_id, value, search_value) VALUES ('$last_id', '806', '', '')",//URL provenance form
      "INSERT INTO couch_data_text (page_id, field_id, value, search_value) VALUES ('$last_id', '838', '', '')",//date notif
          "INSERT INTO couch_data_text (page_id, field_id, value, search_value) VALUES ('$last_id', '839', '', '')",//email notif
          "INSERT INTO couch_data_text (page_id, field_id, value, search_value) VALUES ('$last_id', '840', '', '')",//activer checkbox notif
          "INSERT INTO couch_data_text (page_id, field_id, value, search_value) VALUES ('$last_id', '831', '', '')",//author vide notes

    "INSERT INTO couch_data_text (page_id, field_id, value, search_value) VALUES ('$last_id', '805', 'Demande direct conférencier', 'Demande direct conférencier')",
    "INSERT INTO couch_data_text (page_id, field_id, value, search_value) VALUES ('$last_id', '822', 'Nouveau', 'Nouveau')",
    "INSERT INTO couch_data_text (page_id, field_id, value, search_value) VALUES ('$last_id', '808', '$entreprise', '$entreprise')",
    "INSERT INTO couch_data_text (page_id, field_id, value, search_value) VALUES ('$last_id', '809', '$nom', '$nom')",
    "INSERT INTO couch_data_text (page_id, field_id, value, search_value) VALUES ('$last_id', '810', '$courriel', '$courriel')",
    "INSERT INTO couch_data_text (page_id, field_id, value, search_value) VALUES ('$last_id', '811', '$telephone', '$telephone')",
    "INSERT INTO couch_data_text (page_id, field_id, value, search_value) VALUES ('$last_id', '821', '$prenom', '$prenom')",
    "INSERT INTO couch_data_text (page_id, field_id, value, search_value) VALUES ('$last_id', '812', '$date_value', '$date_value')",
    "INSERT INTO couch_data_text (page_id, field_id, value, search_value) VALUES ('$last_id', '813', '$time_value', '$time_value')",
    "INSERT INTO couch_data_text (page_id, field_id, value, search_value) VALUES ('$last_id', '814', '$typeevent', '$typeevent')",
    "INSERT INTO couch_data_text (page_id, field_id, value, search_value) VALUES ('$last_id', '815', '$adresse', '$adresse')",
    "INSERT INTO couch_data_text (page_id, field_id, value, search_value) VALUES ('$last_id', '816', '$info', '$info')",
    "INSERT INTO couch_data_text (page_id, field_id, value, search_value) VALUES ('$last_id', '818', '$conferencier', '$conferencier')",
    "INSERT INTO couch_data_text (page_id, field_id, value, search_value) VALUES ('$last_id', '852', '$participants', '$participants')"
];

foreach($queries as $query){
   if (mysqli_query($conn, $query)) {
} else {
    error_log("Error: " . $query . " - " . mysqli_error($conn)); // Log error
}
}

mysqli_close($conn);


</cms:php>

It's in the "k_success" block of the form. Like I said, it works super well, and everything else works, but not the search bar in the admin panel.

Any ideas where I should look next? Thanks a lot

[trendoman]

Indeed this is the source of the issue, as Couch postprocesses the submitted values in admin panel and forms a string for the fulltext table. I can see that using raw PHP code is thus suboptimal, as the Couch is not involved (with a few other minor consequences). There are 2 quick options for you - one is abandon raw php and let Couch do the persisting (and do the sanitization of POSTed values); the other is to abandon default fulltext search and replace it with filtering, which will not depend on fulltext. I see suboptimal code quite often and it is expected that 'surprises' happen. Be advised that hooks that Couch provides are not triggering upon injecting into database directly. If someone else wrote that code for you, he should've reused class KDB which is very comfortable for database inserts. Or outright 'borrow' the global $DB object and set it up to temporarily save to a different database (I concluded from the code's surprising approach that the form is on another Couch installation on the same physical server).

Ask KK to have this fixed for you, I am sure you have quite a history with that website and it would be the correct thing to do by asking his intervention. Perhaps, he's having more interesting ideas like reusing some code to form a fulltext string and update it along the way.

Thanks for taking the time to reply again.

I found the issue.
I needed to add this line too :

Code: Select all

"INSERT INTO couch_fulltext (page_id, title, content) VALUES ('$last_id', '$pagetitle', '$prenom $nom $entreprise')"

So basically, when injecting a new entry in the database, I wasn't adding anything to the "couch_fulltext" table, so I couldn't find it. After adding this line, it worked right away.

Thanks again!

[trendoman]

There's so much love in a good hint. Glad you got the solution yourself.