Search function only return values with exact match

[mlukac89]

Hi

 

I have a issue with function for searching a title of a blog.

 

I have a title in table "My blog 123", and if i try to search for "my blog" i don't get any results so its case sensitive, but when try to search "My blog" or "My" i got results. Even with searching "blog" i don't get any result, like it try to search from first letter only.

 

So how can i fix this to search if i type "blog" or "my blog" to give me results with all titles with "blog" or "Blog" in title ignoring uppercase or lowercase letters or position in sentence.

if (isset($_POST['submit'])) {
  
  $term = $_POST['search'];

  $stmt = $dbh->prepare("SELECT * FROM blog WHERE heading LIKE :term");
  $stmt->bindValue(':term', '%'.$term.'%', PDO::PARAM_STR);
  $stmt->execute();
  $data = $stmt->fetchAll(PDO::FETCH_ASSOC);

  print_r($data);

}

Edited April 16, 2017 by mlukac89

[requinix]

What does a

SHOW CREATE TABLE blog

return?

[mlukac89]

Its showing this

array(1) { 
  [0]=> array(4) 
  { 
    ["Table"]=> string(4) "blog" 
    [0]=> string(4) "blog" 
    ["Create Table"]=> string(404) "CREATE TABLE `blog` ( `id` int(11) NOT NULL AUTO_INCREMENT, `heading` varchar(255) COLLATE utf8_bin NOT NULL, `text` text COLLATE utf8_bin NOT NULL, `image` varchar(255) COLLATE utf8_bin NOT NULL, `status` varchar(255) COLLATE utf8_bin NOT NULL, `created_date` varchar(255) COLLATE utf8_bin NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8 COLLATE=utf8_bin" 
    [1]=> string(404) "CREATE TABLE `blog` ( `id` int(11) NOT NULL AUTO_INCREMENT, `heading` varchar(255) COLLATE utf8_bin NOT NULL, `text` text COLLATE utf8_bin NOT NULL, `image` varchar(255) COLLATE utf8_bin NOT NULL, `status` varchar(255) COLLATE utf8_bin NOT NULL, `created_date` varchar(255) COLLATE utf8_bin NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8 COLLATE=utf8_bin" 
  } 
}

btw when i press submit button without enter any search term it give me all data from blog table

Edited April 16, 2017 by mlukac89

[requinix]

Case-sensitivity in MySQL is dictated by the collation used.

 

All of yours are

COLLATE utf8_bin

utf8_bin, which means UTF-8 (good) with binary comparisons (bad). Your table has that as a default. Your database probably has it as a default too.

 

Change everything that you want to be case-insensitive to use a case-insensitive collation. Do

SHOW COLLATION WHERE Charset = 'utf8';

and pick one of the *_ci collations by reading the names and using common sense. If you're not confident in your choice, post the list.

 

Then use ALTER TABLE statements to change the columns. ALTER TABLE can also do the table's default collation and ALTER DATABASE will do the database's.

[mlukac89]

Thanks, i changed from utf8_bin to utf8_general_ci and now all works fine.

[Jacques1]

utf8_general_ci has problems.

[mlukac89]

So switching to utf8_unicode_ci is better but is slower as they said. This will have impact on many blog post for slower loading ?

 

"The disadvantage of utf8_unicode_ci is that it is a little bit slower than utf8_general_ci." 

[Jacques1]

It's somewhat absurd to worry about subtle performance differences between collations when at the same time you use LIKE with a "%...%" pattern, which is friggin' slow (since it cannot use any indexes).

 

In fact, you don't even have indexes, so clearly performance isn't your concern.