case sensitivity in search

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

hrubos
Forum Contributor
Posts: 172
Joined: Sat Oct 07, 2006 3:44 pm

case sensitivity in search

Post by hrubos »

Dear all,

I want to search name in database and output, but I have to type name exactly (first letter is big) .
So how to solve if I want type all letters, which are small or big.
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

Use string manipulation functions of the database? Change the field type to not have case sensitivity? It's hard to recommend much without knowing more information of how your search works and the database/table you are searching..
User avatar
Ollie Saunders
DevNet Master
Posts: 3179
Joined: Tue May 24, 2005 6:01 pm
Location: UK

Post by Ollie Saunders »

Code: Select all

SELECT * FROM `table` WHERE `searchField` LIKE '$search'
LIKE is case insensitive by default

For your information:
AAA = big letters = uppercase
aaa = small letters = lowercase

Something that is case sensitive distinguishes between differing case.
Something that is case insensitive doesn't care what case the letters are.

@mods: help this guy out and rename the thread title methinks.
hrubos
Forum Contributor
Posts: 172
Joined: Sat Oct 07, 2006 3:44 pm

Post by hrubos »

function to search, I have written

I have problem that it only is true follow example :

in database, table person , field name : Kevin
so if I search : Kevin --> it runs
but if I search : kevin --> nothing runs

but I want when I search Kevin or kevin, or KeVin, or KEVIN ==: it all runs

Thank in advice !!!
User avatar
Ollie Saunders
DevNet Master
Posts: 3179
Joined: Tue May 24, 2005 6:01 pm
Location: UK

Post by Ollie Saunders »

show your code please
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

..and the table schema.
hrubos
Forum Contributor
Posts: 172
Joined: Sat Oct 07, 2006 3:44 pm

Post by hrubos »

Code: Select all

<form method = "POST" action = "edit_sDisplay.php">
<table border="0" width="100%" id="table1">
	<tr>
		<td width="8%"> </td>
		<td width="10%"><b><font face="Arial" size="2">Name</font></b></td>
		<td><input type="text" name= "name"  size="30" style="font-family:Arial">	</td>
</tr>
<tr> <td> </td> </tr>

	<tr>
		<td width="50"> </td>
		<td> </td>
		<td><input type="submit" value="   Submit   " name="submit">
  </td>
		<td> </td>
	</tr>

</table>
pageRequire.php

Code: Select all

$name = $_POST['name'];
db_connect();


$query = "SELECT name
          FROM person
          WHERE  name = '$name'";

$result  = mysql_query($query);
table person

id_person | name
1 | Kevin
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

Nothing in your post suggests it's case sensitive.

Code: Select all

SHOW CREATE TABLE person
will give you a table schema, post that please.

The only thing that scares me about your code is that it's open to SQL injection which you've been warned about previously.
hrubos
Forum Contributor
Posts: 172
Joined: Sat Oct 07, 2006 3:44 pm

Post by hrubos »

oh, Thank you very much for your advice in last topic.thank thank

ah, do you want it

Code: Select all

CREATE TABLE `student` (
  `id_student` int(10) NOT NULL auto_increment,
  `rc_student` varchar(6) collate latin2_czech_cs NOT NULL,
  `last_name` varchar(15) collate latin2_czech_cs NOT NULL,
  `first_name` varchar(15) collate latin2_czech_cs NOT NULL,
  `street` varchar(15) collate latin2_czech_cs NOT NULL,
  `city` varchar(15) collate latin2_czech_cs NOT NULL,
  `country` varchar(15) collate latin2_czech_cs NOT NULL,
  `number_telefone` varchar(12) collate latin2_czech_cs NOT NULL,
  `faculty` varchar(15) collate latin2_czech_cs NOT NULL,
  `year_school` varchar(1) collate latin2_czech_cs NOT NULL,
  PRIMARY KEY  (`id_student`),
  KEY `rc_student` (`rc_student`,`last_name`,`first_name`)
) ENGINE=MyISAM AUTO_INCREMENT=29 DEFAULT CHARSET=latin2 COLLATE=latin2_czech_cs
So I want to search last_name in table student

feyd | reformatted to readable form
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

Unless it's a quirk of the czech character set, I don't see anything in that which would make the search case sensitive.

Where are you finding this problem?
hrubos
Forum Contributor
Posts: 172
Joined: Sat Oct 07, 2006 3:44 pm

Post by hrubos »

I would like to know how solve if the words which I type to search, isn't diffrent between big letters or small betters
example :
in database name : Kevin
when I type "kevin" , it can be found.but in fact I can't do it.
It is my problem.
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

Post your code that is detecting this problem. None of the information you've posted so far should be doing a case sensitive search unless it's a quirk of the czech character set as I've said. Similarly, none of the code you've posted would detect case sensitivity. There has to be something more.
hrubos
Forum Contributor
Posts: 172
Joined: Sat Oct 07, 2006 3:44 pm

Post by hrubos »

I have formular for user type name . It is in page1.php

Code: Select all

<form method = "POST" action = "edit_sDisplay.php">
<table border="0" width="100%" id="table1">
	<tr>
		<td width="8%"> </td>
		<td width="10%"><b><font face="Arial" size="2">Jméno</font></b></td>
		<td><input type="text" name= "last_name"  size="30" style="font-family:Arial">	</td></tr>
<tr><td width="50"> </td>
		<td> </td>
		<td><input type="submit" value="   Submit   " name="submit"></td></tr>
</table>
then I use command sql SELECT to search last_name

Code: Select all

$last_name = $_POST['last_name'];
db_connect();
$query = "SELECT last_name,first_name
          FROM student
          WHERE  last_name = '$last_name'";
$result  = mysql_query($query);
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

That code does not detect the problem; it's the same as the previously posted code.
hrubos
Forum Contributor
Posts: 172
Joined: Sat Oct 07, 2006 3:44 pm

Post by hrubos »

But it is all I have for method search.

So would you mind suggesting me about step for the search method ???

Thank much
Post Reply