Jump to content
mlukac89

Search function only return values with exact match

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

Edited by mlukac89

Share this post


Link to post
Share on other sites

What does a

SHOW CREATE TABLE blog
return?

Share this post


Link to post
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

Edited by mlukac89

Share this post


Link to post
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.

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
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." 

Share this post


Link to post
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.

Share this post


Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.


×
×
  • 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.