+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: Archived Discussions (https://forum.codeigniter.com/forumdisplay.php?fid=20)
+--- Forum: Archived Development & Programming (https://forum.codeigniter.com/forumdisplay.php?fid=23)
+--- Thread: MySQL Query not working (Case sensitive issue) (/showthread.php?tid=27123)
MySQL Query not working (Case sensitive issue) - El Forum - 02-01-2010
[eluser]rich.a.coy[/eluser]
Hello,
I'm trying to make a relatively simple search form but my query is not working as expected. Below is my code. I've only been using CI and MVC for about 3 weeks.
What do I need to add to make the query not case sensitive? Right now searching for "rich" does not return the "Richard" or "Rich" that are there and properly returned if searched for with "Rich"
Thanks in advance any help you can offer.
(I've stripped out the unessential code from the sections below.)
The form to enter the search term :
Code:
<form action="search" method="post"><input name="searchbox" type="text" value=""/><input type="submit" value="Search"/></form>The Search Controller:
Code:
function index()
{
$query = array();
// Get search results
if($query = $this->search_model->search_results())
{
$data['matches'] = $query;
}
$this->load->view('app/app_template', $data);
}The Search Model:
Code:
function search_results() {
$search_term = $this->input->post('searchbox');
if ($search_term != "") {
$sql = "SELECT * FROM user_profiles WHERE fname LIKE '%".$this->db->escape_like_str($search_term)."%'";
$query = $this->db->query($sql);
return $query->result();
}
}Finally, the Search View:
Code:
<h3>Your Search Results</h3>
<?php if(isset($matches)) : foreach($matches as $row) : ?>
<h2><?php echo $row->fname; ?></h2>
<?php endforeach; ?>
<?php else : ?>
<h2>No Matches Found.</h3>
<?php endif; ?>
</div>MySQL Query not working (Case sensitive issue) - El Forum - 02-01-2010
[eluser]danmontgomery[/eluser]
The only way I'm aware of to get a case-sensitive search from LIKE is if you're using a case-sensitive collation (Something _bin or _cs)
MySQL Query not working (Case sensitive issue) - El Forum - 02-01-2010
[eluser]AndresC[/eluser]
Hi, you can do this (if you haven't done it yet...):
echo your $sql string and paste it in you phpmyadmin app (or whatever you use) to see if the query returns something. If it does not, maybe is the type of field you choose. In mysql database use the utf8_unicode_ci type rather than utf8 because the last one is case sensitive. Hope it help.
MySQL Query not working (Case sensitive issue) - El Forum - 02-01-2010
[eluser]rich.a.coy[/eluser]
Perfect, Thanks! I changed the field to utf8_unicode_ci and now it works as expected.
Are there any disadvantages of utf8_unicode_ci that I need to know about? This will be a large database (hopefully) and want it to be as optimized as possible. Should I set all text fields to utf8_unicode_ci, or just the ones I plan to search against?
Rich