I've been googling for a while and am still unsuccessful.

I'm trying to grab all the users from a table based on the first letter. Anyone have any ideas on how to do that SELECT statement?

"SELECT * FROM users WHERE username LIKE 'letter%'"

But maybe there are smarter ways, how to do it...

PHP Code:

$start_letter = 'A'; //Make sure to sanitize this data if coming from user input

//Example sanitization assuming a URL of the form filename.php?start_letter=A
$start_letter = substr(preg_replace('/[^a-z]/i','',$_GET['start_letter']),0,1);

$results = mysql_query("SELECT field_names FROM table_name WHERE search_field_name LIKE '".$start_letter."%'");

if ($results && mysql_num_rows($results) > 0) {
  while ($a_result = mysql_fetch_object($results)) {
    //Do your thing with the results.
  }
} 


Is there a way to make it case insensitive?

Is there a way to make it case insensitive?

Use lower() in the SQL where clause with an strtolower() php call on the parameter.

PHP Code:

$results = mysql_query("SELECT field_names FROM table_name WHERE lower(search_field_name) LIKE '".strtolower($start_letter)."%'"); 


So when you start with a lowercase, it checks both upper and lower, but if you start with upper, it only checks fields with upper? That seems to be the case when I've been messing around with it, but I'm not for certain.

no. The sql part lower() tells the DB to cast any content of the field to a lowercase.
Then the seed is forced to lower case too, so you don't have to care about it

Instead of coding this in php you can change the collation of the field you are searching in the database eg if your collation is set to latin1_bin change it to utf8_unicode_ci the ci stands for case insensitive

My code does do a case-insensitive search. No changes needed.

I don't think the LIKE clause in mysql is case sensitive.

Originally Posted by mgraphic

I don't think the LIKE clause in mysql is case sensitive.

When all else fails, consult the documentation.

http://dev.mysql.com/doc/refman/5.0/...nsitivity.html

May want to check yourself before you start suggesting such stuff to a mod with 2000+ posts, zxcvbnm60...mgraphic was simply informing readers that the code provided with LIKE as the query is case-insensitive.

Besides, almost every post on a forum could be answered with 1) see the documentation, or 2) search Google, so those are useless replies. The third, not-so-useless, option is to hire a coder.

You will find many people who do not explicitly assert something, but, instead, suggest that the thing is likely. It is a matter of delivery and not accuracy.

Good luck as you familiarize yourself with the forum and welcome to WT.

Originally Posted by JeremyMiller

You will find many people who do not explicitly assert something, but, instead, suggest that the thing is likely. It is a matter of delivery and not accuracy.

Pardon me JeremyMiller (and mgraphic). The above response was not directed towards mgraphic per se, but towards any readers that may be looking for definitive answers to a question. Though the regular audience of these forums may understand the customary nuances of communication that you describe, the casual reader will not. The intent of my comment was collaborative rather than correcting.

Thanks for the welcome, good sir.