CODEWITHSUNDEEP
mysqlsqlstring-comparison
arik
arik

Reputation: 29370

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: 425

Views: 1301514

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: 80111

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: 606

risnandar
risnandar

Reputation: 5573

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: 10003

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

Redacted
Redacted

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: 332731

Use:

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

Reference:

Upvotes: 216

Related Questions