Jump to content

Recommended Posts

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.

Link to comment
https://forums.phpfreaks.com/topic/256902-search-case-sensativity-problem/
Share on other sites

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.

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

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%\"

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*

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');
?>

 

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]

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%'

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

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.