Forums

Hi,

I have followed the excellent tutorial at https://www.chronoengine.com/faqs/72-ccv5/5215-how-do-i-build-a-where-statement-in-ccv5.html to create a complex WHERE statement, limiting results to those having fields that match a search string.

My current code is as follows:

<?php
  // Get search string from url
  $search = JRequest::getVar('search');

  // Get records matching the search
  if ($search <> "") {
    $dbo = \GCore\Models\Dossiers::getInstance()->dbo;
    $search = $dbo->quote($search);
    return array (":
      Name LIKE {$search} OR
      ServiceNumber = {$search} OR
      Occupation LIKE {$search}
      ");
  }
?>

This works fine for exact matches such as ServiceNumber = '157' or Occupation = 'Labourer' but how do I enable MySQL LIKE wildcards so that I can also match Name = 'John' or Name = 'Smith' when the field value of Name is 'John Smith'?

Thanks in Advance.

Hi OzNeilAu,

Please try e.g.

Name LIKE '%{$search}%'

Bob

Hi Bob,

Thanks for the prompt response as always!

I gave that a try and searched on "John" with the following response:

1064
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'John'%' OR ServiceNumber = 'John' OR Occupation LIKE 'John' ' at line 2 SQL=SELECT `Dossiers`.`id` AS `Dossiers.id`, `Dossiers`.`uniq_id` AS `Dossiers.uniq_id`, `Dossiers`.`user_id` AS `Dossiers.user_id`, `Dossiers`.`created` AS `Dossiers.created`, `Dossiers`.`modified` AS `Dossiers.modified`, `Dossiers`.`Published` AS `Dossiers.Published`, `Dossiers`.`Name` AS `Dossiers.Name`, `Dossiers`.`ServiceNumber` AS `Dossiers.ServiceNumber`, `Dossiers`.`Unit` AS `Dossiers.Unit`, `Dossiers`.`EnlistmentAge` AS `Dossiers.EnlistmentAge`, `Dossiers`.`Occupation` AS `Dossiers.Occupation`, `Dossiers`.`ServiceFrom` AS `Dossiers.ServiceFrom`, `Dossiers`.`ServiceTo` AS `Dossiers.ServiceTo`, `Dossiers`.`Height` AS `Dossiers.Height`, `Dossiers`.`Weight` AS `Dossiers.Weight`, `Dossiers`.`MainPhoto` AS `Dossiers.MainPhoto`, `Dossiers`.`EmbarkationDate` AS `Dossiers.EmbarkationDate`, `Dossiers`.`PortEmbarked` AS `Dossiers.PortEmbarked`, `Dossiers`.`ShipName` AS `Dossiers.ShipName`, `Dossiers`.`Medals` AS `Dossiers.Medals`, `Dossiers`.`DeathDate` AS `Dossiers.DeathDate`, `Dossiers`.`DeathPlace` AS `Dossiers.DeathPlace`, `Dossiers`.`DeathCause` AS `Dossiers.DeathCause`, `Dossiers`.`Memorial` AS `Dossiers.Memorial`, `Dossiers`.`NextOfKin` AS `Dossiers.NextOfKin`, `Dossiers`.`ReturnDate` AS `Dossiers.ReturnDate`, `Dossiers`.`ReturnPort` AS `Dossiers.ReturnPort`, `Dossiers`.`ReturnShip` AS `Dossiers.ReturnShip`, `Dossiers`.`Religion` AS `Dossiers.Religion`, `Dossiers`.`MedicalDetails` AS `Dossiers.MedicalDetails`, `Dossiers`.`Repatriation` AS `Dossiers.Repatriation`, `Dossiers`.`Other` AS `Dossiers.Other`, `Dossiers`.`Submit` AS `Dossiers.Submit` FROM `#__chronoengine_chronoforms_datatable_dossier` AS `Dossiers` WHERE Name LIKE '%'John'%' OR ServiceNumber = 'John' OR Occupation LIKE 'John' ORDER BY `Dossiers`.`Name`

Can you think of anything else to try?

Thanks,

Neil

Hi ozneilau,

I missed the $search = $dbo->quote($search); - that's giving you the extra quotes. You could try commenting that out or maybe $search = $dbo->quote('%'.$search.'%'); will work??

Bob

Thanks Bob,

$search = $dbo->quote('%'.$search.'%');

has solved the issue for me.