Jump to content

Search function only return values with exact match


mlukac89

Recommended Posts

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);

}

17b335ed2cfc4a328b9ae9bbd7e5f1ed.jpeg

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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." 

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

Archived

This topic is now archived and is closed to further replies.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.