Ajax autocomplete not case insensitive

[eluser]zulubanslee[/eluser]
I'm doing an ajax autocomplete but it's not case insensitive

It's here if you want to look at it:
http://golocalseo.net/doug2/bk3/

In the search by city and state, it requires the first letter to be capitalized, since that is how it is in the database naturally. I have used this identical code several times and it has worked fine. This time, no dice but I don't know why. I want the search to be case insensitive.

Here is my controller:

Code:

$q=$_POST["city_string"];
  $a = '';
   $this->db->select('city, state_name');
   $this->db->distinct();
   $this->db->from('zip_code');
   $this->db->like('city', $q, 'after');
   $city_state_result = $this->db->get();

   foreach( $city_state_result->result() as $yadda)
   {
    $a = $yadda->city  .', '.$yadda->state_name.'<br/>';
    echo $a;
   }

[eluser]zulubanslee[/eluser]
Incidentally, I know you're not supposed to make database calls from the controller, but I wanted to keep it simple until I get it working.

[eluser]zulubanslee[/eluser]
Well I decided to try a different tack. I decided to go with writing my own mysql statments, but now my bound parameters aren't working. Here is my controller now.

Code:

$q=$_POST["city_string"];
  $a = '';
  $sql= "SELECT DISTINCT city, state_name
      FROM zip_code
      WHERE lower(city) LIKE ?%";

  
  $city_state_result = $this->db->query($sql, array('city'=>$q));

This is the error message I got.

Code:

<p>Error Number: 1064</p><p>You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '%' at line 3</p><p>SELECT DISTINCT city, state_name
      FROM zip_code
      WHERE lower(city) LIKE 'x'%

[eluser]Samus[/eluser]
You could use ucwords().

It capitalizes the first letter of every word in a string, then you could send that version of to your query.

http://php.net/manual/en/function.ucwords.php

[eluser]zulubanslee[/eluser]
It'll work for the time being, but i really want it entirely case insensitive. Thanks for your input.

[eluser]CroNiX[/eluser]
Try:

Code:

SELECT DISTINCT city, state_name
FROM zip_code
WHERE BINARY city LIKE ?%

[eluser]zulubanslee[/eluser]
That did not work. Here is what I ended up going with.

Code:

$q=$_POST["city_string"];
   $a = '';
   $sql= "SELECT DISTINCT city, state_name
      FROM zip_code
      WHERE city  COLLATE UTF8_GENERAL_CI LIKE ".$this->db->escape($q.'%')."";  
      
  $city_state_result = $this->db->query($sql, array($q));

[eluser]gRoberts[/eluser]
The reason being, is from all of your examples, I have not seen you force the query going in to lower case?

So your forcing the column to lower, using the `lower` function in MySQL but your then passing the value to match against in normal case.

Instead, pass your value but use `strtolower` to force it to lower case.

[eluser]zulubanslee[/eluser]
You're absolutely right, and what you suggested worked. I've never had to specify case sensitivity before with a select, though.

[eluser]CroNiX[/eluser]
That depends on the datatype and collation of the field.

http://dev.mysql.com/doc/refman/5.0/en/c...ivity.html