CODEWITHSUNDEEP
mysqlsqlstring-comparison
arik
arik

Reputation: 29240

MySQL query String contains

I've been trying to figure out how I can make a query with MySQL that checks if the value (string $haystack ) in a certain column contains certain data (string $needle), like this:

SELECT *
FROM `table`
WHERE `column`.contains('{$needle}')

In PHP, the function is called substr($haystack, $needle), so maybe:

WHERE substr(`column`, '{$needle}')=1

Upvotes: 423

Views: 1296296

Answers (8)

CommentUser
CommentUser

Reputation: 104

The accepted answer would be correct for MySQL alone, but since the question is using:

  • a variable,
  • appears to be using {$needle} as a replacement tag, and
  • it mentions PHP

it appears the author wanted to construct the MySQL query using PHP.

Since the question was asked 12 years ago, current practice would be to use preprepared statements to prevent SQL injection.

Here is an example with PHP:

function check_connection ($user, $pass, $db = 'test', $host = '127.0.0.1', $charset = 'utf8mb4') {
     if (isset($GLOBALS['conn']) && is_object($GLOBALS['conn']) && ($GLOBALS['conn'] instanceof PDO)) {
          if (same_database($db) === true) {
               $connection = &$GLOBALS['conn']; 
          }
          else {
               $GLOBALS['conn'] = pdo_connect($user, $pass, $db, $host, $charset);
               $connection = &$GLOBALS['conn'];       
          }
     }
     else {
          $GLOBALS['conn'] = pdo_connect($user, $pass, $db, $host, $charset);
          $connection = &$GLOBALS['conn'];
     }

     return $connection;
}

function pdo_connect ($user, $pass, $db, $host, $charset){    
     $dsn = "mysql:host=$host;dbname=$db;charset=$charset";
     $options = [
       PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION,
       PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC
     ];
     try {
       return new PDO($dsn, $user, $pass, $options);
     } 
     catch (\PDOException $e) {
       throw new \PDOException($e->getMessage(), (int)$e->getCode());
     }
}

function same_database($db) {
    if (isset($GLOBALS['conn']) && is_object($GLOBALS['conn']) && ($GLOBALS['conn'] instanceof PDO)) {
        $sql = "SELECT DATABASE() AS 'database'";
        $sth = $GLOBALS['conn']->prepare($sql);
        $sth->execute();
        if (strcasecmp(trim($sth->fetchAll(PDO::FETCH_ASSOC)['0']['database']), trim($db)) === 0) { 
            return true;
        }
    }

    return false;
}
    
$conn = check_connection($user, $pass, $db, $host, $charset);

$sql = "
     SELECT *
     FROM `table`
     WHERE `column` like :needle
";

// Concatenating the % wildcard before and after our search variable
$bind = array(
     ':needle' => '%'.$needle.'%'
);

$sth = $conn->prepare($sql);
$sth->execute($bind);

// Being redundant about fetch_assoc incase it was not set in pdo() options
$result = $sth->fetchAll(PDO::FETCH_ASSOC);

// You would use rowCount(), instead of fetchAll(), if it is NOT a SELECT statement
// $sth->rowCount();

print_r($result);

Here are two resources for building PHP PDO statements:

Upvotes: -5

Wolph
Wolph

Reputation: 80011

Quite simple actually:

SELECT *
FROM `table`
WHERE `column` LIKE '%{$needle}%'

The % is a wildcard for any characters set (none, one or many). Do note that this can get slow on very large datasets so if your database grows you'll need to use fulltext indices.

Upvotes: 603

risnandar
risnandar

Reputation: 5563

Mine is using LOCATE in mysql:

LOCATE(substr,str), LOCATE(substr,str,pos)

This function is multi-byte safe, and is case-sensitive only if at least one argument is a binary string.

In your case:

SELECT * FROM `table`
WHERE LOCATE('{$needle}', `column`) > 0

Upvotes: 51

chris
chris

Reputation: 9993

WHERE `column` LIKE '%$needle%'

Upvotes: 72

Andres
Andres

Reputation: 83

You probably are looking for find_in_set function:

Where find_in_set($needle,'column') > 0

This function acts like in_array function in PHP

Upvotes: 5

Joshua Powell
Joshua Powell

Reputation: 974

In addition to the answer from @WoLpH.

When using the LIKE keyword you also have the ability to limit which direction the string matches. For example:

If you were looking for a string that starts with your $needle:

... WHERE column LIKE '{$needle}%'

If you were looking for a string that ends with the $needle:

... WHERE column LIKE '%{$needle}'

Upvotes: 14

Alejandro Moreno
Alejandro Moreno

Reputation: 5718

be aware that this is dangerous: 

WHERE `column` LIKE '%{$needle}%'

do first:

$needle = mysql_real_escape_string($needle);

so it will prevent possible attacks.

Upvotes: 4

OMG Ponies
OMG Ponies

Reputation: 332521

Use:

SELECT *
  FROM `table`
 WHERE INSTR(`column`, '{$needle}') > 0

Reference:

Upvotes: 215

Related Questions