Search Case Sensativity Problem

Angeleyezz · February 11, 2012

Once again the php noob returns with another boggle..... :-\

I got my client search working, but when it searches the database, it is only searching case sensative, and its not searching partial words. like for example using last names, if i search for Angeleyezz it will find Angeleyezz, but if i search angeleyezz it wont find it, nor will it find it if i search it as angel, ang, eye, etc etc etc. same goes for all fields that i am searching, name, address, telephone number, city, etc. how do i change this?

my form code is here:

<form method="get" action="search_client_function.php">
    <input type="text" name="search_term" value="search">
    <input type="submit" name="search" value="search" />
    </form>

my search_client_function.php code is here:

<?php
$title = "Search Results"; 
$con = mysql_connect("localhost","root","");
if (!$con)
  {
  die('Could not connect: ' . mysql_error());
  }
  
include('includes/header.php');

mysql_select_db("terra_elegante_operations", $con);



$searchfor = $_GET['search_term'];


$query = "select * from client_information where concat (account_number,name_first,name_last,address,city,state,zipcode,telephone,telephone_alt,email)  like \"%$searchfor%\"";

$result = mysql_query($query) or die("Couldn't execute query");
?>
<br /><br />
<table border="2" cellspacing="0" cellpadding="3" width="960" bordercolor="#000000">
  <tr bgcolor="#e6e6e6" align="center">
    <td><font face="verdana" size="2" color="#000000"><b>Account Number</b></font></td><td><font face="verdana" size="2" color="#000000"><b>First Name</b></font></td><td><font face="verdana" size="2" color="#000000"><b>Last Name</b></font></td><td><font face="verdana" size="2" color="#000000"><b>Address</b></font></td><td><font face="verdana" size="2" color="#000000"><b>City</b></font></td><td><font face="verdana" size="2" color="#000000"><b>State</b></font></td><td><font face="verdana" size="2" color="#000000"><b>Zipcode</b></td><td><font face="verdana" size="2" color="#000000"><b>Telephone #</b></font></td><td><font face="verdana" size="2" color="#000000"><b>Telephone Alt</b></font></td>
<?php
while($row = mysql_fetch_array($result))
{
?>
<tr>
<td><font face="verdana" size="1" color="#000000"><?php echo $row['0'] ?></font></td><td><font face="verdana" size="1" color="#000000"><?php echo $row['1'] ?></font></td><td><font face="verdana" size="1" color="#000000"><?php echo $row['2'] ?></font></td><td><font face="verdana" size="1" color="#000000"><?php echo $row['3'] ?></font></td><td><font face="verdana" size="1" color="#000000"><?php echo $row['4'] ?></font></td><td><font face="verdana" size="1" color="#000000"><?php echo $row['5'] ?></font></td><td><font face="verdana" size="1" color="#000000"><?php echo $row['6'] ?></font></td><td><font face="verdana" size="1" color="#000000"><?php echo $row['7'] ?></font></td><td><font face="verdana" size="1" color="#000000"><?php echo $row['8'] ?></font></td>
</tr>
<?php
}
?>
</table>



<br /><br /><br /><br /><br /><br /><br /><br /><br /><br /><br /><br /><br /><br /><br /><br /><br /><br /><br /><br />


<?php
include('includes/footer.php');
?>

I know its a bit sloppy, but I'm a php noob =\ this is the only way i could get it to work lol.

darkfreaks · February 11, 2012

have you tried using

MATCH(account_number,name_first,name_last,address,city,state,zipcode,telephone,telephone_alt,email) AGAINST(%$searcgfor%)

is much more efficient using full text search than a LIKE comparison.

Angeleyezz · February 12, 2012

could not execute query. each field is a varchar for the most part, should i make them full texts in the sql database?

Angeleyezz · February 12, 2012

here is the line

$query = "select * from client_information match (account_number,name_first,name_last,address,city,state,zipcode,telephone,telephone_alt,email)  against \"%$searchfor%\"";

scootstah · February 12, 2012

Unless they are large chunks of text you don't really need full text.

Angeleyezz · February 12, 2012

there just names, addresses, etc, invoices, etc.

any idea why that code didnt work?

scootstah · February 12, 2012

Not sure, because it works for me.

Angeleyezz · February 12, 2012

yeah its definitally not working for me, keeps giving me the error after that query line could not execute query

scootstah · February 12, 2012

Well in that case, replace "could not execute query" with

mysql_error()

PFMaBiSmAd · February 12, 2012

Back to your concat() method. If any of the columns are binary strings or numerical data types, the result is a binary string and the comparison will be case-sensitive. The mysql documentation for the concat() function shows how to cast any such fields to a CHAR type so that the resulting comparison would be case-insensitive.

Angeleyezz · February 12, 2012

Fatal error: Call to undefined function diemysql_error()

$query = "select * from client_information match concat (account_number,name_first,name_last,address,city,state,zipcode,telephone,telephone_alt,email)  against (%$searchfor%)";

scootstah · February 12, 2012

C'mon now.

$result = mysql_query($query) or die(mysql_error());

Pikachu2000 · February 12, 2012

die( "<br>Query failed: $query<br>Error: " . mysql_error() );

Angeleyezz · February 12, 2012

oops lol, sorry, lol.

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 '(account_number,name_first,name_last,address,city,state,zipcode,telephone,teleph' at line 1

im using php 5.3.4, mysql 5.1.53

darkfreaks · February 13, 2012

you had a syntax error where you put a backslash in front of the leading double quote.

select * from client_information where concat (account_number,name_first,name_last,address,city,state,zipcode,telephone,telephone_alt,email)  like "%$searchfor%\"

scootstah · February 13, 2012

you had a syntax error where you put a backslash in front of the leading double quote.
select * from client_information where concat (account_number,name_first,name_last,address,city,state,zipcode,telephone,telephone_alt,email)  like "%$searchfor%\"

The backslashes need to be there because he used double quotes on the whole string. In fact, that isn't even part of the error.

Maybe it's just late but I don't see any syntax error. *shrug*

Angeleyezz · February 13, 2012

yeah this string works fine, but its not allowing me to do it how i want to do it. i get the syntax errors with the match and against, not the where and like.

scootstah · February 13, 2012

Can you show me what a

SHOW FIELDS FROM client_information

looks like?

Angeleyezz · February 13, 2012

This is the whole script so far, not very organized, but ill tweak it later once i get it functioning correctly. Right now all im worried about is functionality of my essential components, later ill clean up the code before i add the bells and whistles. "if i ever learn how to do them lol"

<?php
$title = "Search Results"; 
$con = mysql_connect("localhost","root","");
if (!$con)
  {
  die('Could not connect: ' . mysql_error());
  }
  
include('includes/header.php');

mysql_select_db("terra_elegante_operations", $con);



$searchfor = $_GET['search_term'];


$query = "select * from client_information where concat (account_number,name_first,name_last,address,city,state,zipcode,telephone,telephone_alt,email)  like \"%$searchfor%\"";

$result = mysql_query($query) or die(mysql_error());
?>
<br /><br />
<table border="2" cellspacing="0" cellpadding="3" width="960" bordercolor="#000000">
  <tr bgcolor="#e6e6e6" align="center">
    <td><font face="verdana" size="2" color="#000000"><b>Account Number</b></font></td><td><font face="verdana" size="2" color="#000000"><b>First Name</b></font></td><td><font face="verdana" size="2" color="#000000"><b>Last Name</b></font></td><td><font face="verdana" size="2" color="#000000"><b>Address</b></font></td><td><font face="verdana" size="2" color="#000000"><b>City</b></font></td><td><font face="verdana" size="2" color="#000000"><b>State</b></font></td><td><font face="verdana" size="2" color="#000000"><b>Zipcode</b></td><td><font face="verdana" size="2" color="#000000"><b>Telephone #</b></font></td><td><font face="verdana" size="2" color="#000000"><b>Telephone Alt</b></font></td>
<?php
while($row = mysql_fetch_array($result))
{
?>
<tr>
<td><font face="verdana" size="1" color="#000000"><?php echo "<a class=\"bubble_nav\" href='show_client.php?id=$row[0]'>$row[0]</a\>"; ?></td><td><font face="verdana" size="1" color="#000000"><?php echo $row['1'] ?></font></td><td><font face="verdana" size="1" color="#000000"><?php echo $row['2'] ?></font></td><td><font face="verdana" size="1" color="#000000"><?php echo $row['3'] ?></font></td><td><font face="verdana" size="1" color="#000000"><?php echo $row['4'] ?></font></td><td><font face="verdana" size="1" color="#000000"><?php echo $row['5'] ?></font></td><td><font face="verdana" size="1" color="#000000"><?php echo $row['6'] ?></font></td><td><font face="verdana" size="1" color="#000000"><?php echo $row['7'] ?></font></td><td><font face="verdana" size="1" color="#000000"><?php echo $row['8'] ?></font></td>
</tr>
<?php
}
?>
</table>



<br /><br /><br /><br /><br /><br /><br /><br /><br /><br /><br /><br /><br /><br /><br /><br /><br /><br /><br /><br />


<?php
include('includes/footer.php');
?>

scootstah · February 13, 2012

Right, but I'd like to see how your table is setup. If you have phpMyAdmin, or the mysql command line tool can you run this query:

SHOW FIELDS FROM client_information

and post the results please?

Angeleyezz · February 13, 2012

Field Type Null Key Default Extra

account_number int(10) unsigned zerofill NO PRI NULL auto_increment

name_first varchar(48) NO NULL

name_last varchar(48) NO NULL

address varchar(50) NO MUL NULL

city varchar(25) NO NULL

state varchar(4) NO NULL

zipcode varchar(5) NO NULL

telephone varchar(10) NO UNI NULL

telephone_alt varchar(10) YES NULL

email varchar(25) YES NULL

[td][/td]

scootstah · February 13, 2012

Okay. What happens if you execute this query in phpMyAdmin or the mysql command line tool?

select * from client_information where concat (account_number,name_first,name_last,address,city,state,zipcode,telephone,telephone_alt,email) like '%angeleyezz%'

Angeleyezz · February 13, 2012

it goes through, but doesnt find anything, however if you search Angeleyezz it comes up its a case sensative search, and thats what im trying to change.

litebearer · February 13, 2012

a thought ...

temp convert field content to upper case AND use upper case for your criteria. this will address the case problem while searching yet leave both the original field content and original search content unchanged

Pikachu2000 · February 13, 2012

Or simply change the collation of the table/fields to one that's case insensitive; one that ends with _ci.

Sign In

Search Case Sensativity Problem

Recommended Posts

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Join the conversation

Important Information