Jump to content


Photo

Search function only return values with exact match

php mysql pdo

Best Answer requinix, 16 April 2017 - 09:23 AM

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. Go to the full post


  • Please log in to reply
7 replies to this topic

#1 mlukac89

mlukac89
  • Members
  • PipPipPip
  • Advanced Member
  • 40 posts
  • LocationCroatia
  • Age:34

Posted 16 April 2017 - 07:35 AM

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, 16 April 2017 - 07:41 AM.


#2 requinix

requinix
  • Administrators
  • Forgotten Administrator
  • 8,693 posts
  • LocationWA

Posted 16 April 2017 - 08:36 AM

What does a
SHOW CREATE TABLE blog
return?

#3 mlukac89

mlukac89
  • Members
  • PipPipPip
  • Advanced Member
  • 40 posts
  • LocationCroatia
  • Age:34

Posted 16 April 2017 - 09:05 AM

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, 16 April 2017 - 09:18 AM.


#4 requinix

requinix
  • Administrators
  • Forgotten Administrator
  • 8,693 posts
  • LocationWA

Posted 16 April 2017 - 09:23 AM   Best Answer

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.

#5 mlukac89

mlukac89
  • Members
  • PipPipPip
  • Advanced Member
  • 40 posts
  • LocationCroatia
  • Age:34

Posted 16 April 2017 - 09:31 AM

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



#6 Jacques1

Jacques1
  • Gurus
  • Turtles all the way down
  • 3,841 posts

Posted 16 April 2017 - 09:39 AM

utf8_general_ci has problems.



#7 mlukac89

mlukac89
  • Members
  • PipPipPip
  • Advanced Member
  • 40 posts
  • LocationCroatia
  • Age:34

Posted 16 April 2017 - 10:11 AM

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



#8 Jacques1

Jacques1
  • Gurus
  • Turtles all the way down
  • 3,841 posts

Posted 16 April 2017 - 10:28 AM

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.






0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users