Welcome Guest, Not a member yet? Register   Sign In
Databases: searching for a word in entries containing multiple words using 'like'
#11

Okay, so, as my actual code involves a complicated JOIN and I couldn't get the SQL query to work, what I've ended up doing is the following:

Code:
$this->db->where('title LIKE ', $search.'%');
$this->db->or_where('title LIKE ', '% '.$search);
$this->db->or_where('title LIKE ', '% '.$search.'%');
$this->db->or_where('title LIKE ', '% '.$search.' %');



CI's inbuilt like and or_like seem to be pretty limited when it comes to arguments (before, after, both, none), but utilising where and or_where does the trick for my needs.

Thanks for all your help and suggestions pointing me in the right direction.
Reply
#12

(This post was last modified: 05-18-2021, 08:22 PM by wdeda.)

If you were using CI 4 the query would be very simple, as below:

PHP Code:
public function getXpt()
    {
        $db $this->db;
        $search 'Taylor';        
        $query 
$db->table('names')
        ->where('name'$search)
        ->orLike('name'$search.'%')
        // here the 'join' would enter
        ->orderBy('name''ASC')        
        
->get();

        return $query->getResult(); 

Table is about music and film artists.

Attached Files Thumbnail(s)
   
Reply
#13

(This post was last modified: 05-18-2021, 10:11 PM by John_Betong.)

These functions may be of interest which are used in my DasaBookCafe.tk book search:

Code:
/* =================================
#  Validate and clean input text
# $params = 'search for multiple words go here';
==================== */
function getParams
(
  $params=NULL
)
:array
{
  $result = NULL;

  # REMOVE DOUBLE SPACES
    while (strpos($params, '  ') ) {
      $params = str_replace('  ',  ' ', $params);
    }

  # CONVERT TO ARRAY
    $result = explode(' ', $params);
    if( empty($result) ):
      $result[] = $params;
    endif;

  return (array) $result;
}//


/* ============================================
#  Parse User's input input into a string
============================================== */
function getSqlWhere
(
  $aParams, $cols
)
:string
{
  $result = '';

  foreach($aParams as $i2 => $param):
    if($i2 > 0):
      $result .= ' AND ';
    endif;
    $result .= "
               CONCAT($cols)
               LIKE '%$param%'
              ";
  endforeach;

  return (string) $result;
}


/* ===========================================
# Conbine two SQL Statements - ORDER IMPORTANT
=========================================== */
function getCombinedCount
(
  $table,
  $sWhere,
  $cols
)
:string
{
  $uLIMITREX = uLIMITREX;
  $result = <<< ____TMP
    SELECT COUNT(*) AS `recNo`
    FROM  `$table`
    WHERE  $sWhere
    ;
____TMP;

  return (string) $result;
}

/* ===========================================
# Conbine two SQL Statements - ORDER IMPORTANT
=========================================== */
function getCombinedRows
(
  $table,
  $sWhere,
  string $cols,
  $uLIMITREX
)
:string
{
  $result = <<< ____TMP
    SELECT $cols 
    FROM   `$table` 
    WHERE  $sWhere 
    LIMIT  0, $uLIMITREX;
____TMP;

  return (string) $result;
}
Reply




Theme © iAndrew 2016 - Forum software by © MyBB