Angeleyezz Posted February 11, 2012 Share Posted 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. Quote Link to comment https://forums.phpfreaks.com/topic/256902-search-case-sensativity-problem/ Share on other sites More sharing options...
darkfreaks Posted February 11, 2012 Share Posted 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. Quote Link to comment https://forums.phpfreaks.com/topic/256902-search-case-sensativity-problem/#findComment-1317085 Share on other sites More sharing options...
Angeleyezz Posted February 12, 2012 Author Share Posted 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? Quote Link to comment https://forums.phpfreaks.com/topic/256902-search-case-sensativity-problem/#findComment-1317200 Share on other sites More sharing options...
Angeleyezz Posted February 12, 2012 Author Share Posted 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%\""; Quote Link to comment https://forums.phpfreaks.com/topic/256902-search-case-sensativity-problem/#findComment-1317202 Share on other sites More sharing options...
scootstah Posted February 12, 2012 Share Posted February 12, 2012 Unless they are large chunks of text you don't really need full text. Quote Link to comment https://forums.phpfreaks.com/topic/256902-search-case-sensativity-problem/#findComment-1317204 Share on other sites More sharing options...
Angeleyezz Posted February 12, 2012 Author Share Posted February 12, 2012 there just names, addresses, etc, invoices, etc. any idea why that code didnt work? Quote Link to comment https://forums.phpfreaks.com/topic/256902-search-case-sensativity-problem/#findComment-1317205 Share on other sites More sharing options...
scootstah Posted February 12, 2012 Share Posted February 12, 2012 Not sure, because it works for me. Quote Link to comment https://forums.phpfreaks.com/topic/256902-search-case-sensativity-problem/#findComment-1317206 Share on other sites More sharing options...
Angeleyezz Posted February 12, 2012 Author Share Posted February 12, 2012 yeah its definitally not working for me, keeps giving me the error after that query line could not execute query Quote Link to comment https://forums.phpfreaks.com/topic/256902-search-case-sensativity-problem/#findComment-1317207 Share on other sites More sharing options...
scootstah Posted February 12, 2012 Share Posted February 12, 2012 Well in that case, replace "could not execute query" with mysql_error() Quote Link to comment https://forums.phpfreaks.com/topic/256902-search-case-sensativity-problem/#findComment-1317210 Share on other sites More sharing options...
PFMaBiSmAd Posted February 12, 2012 Share Posted 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. Quote Link to comment https://forums.phpfreaks.com/topic/256902-search-case-sensativity-problem/#findComment-1317212 Share on other sites More sharing options...
Angeleyezz Posted February 12, 2012 Author Share Posted 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%)"; Quote Link to comment https://forums.phpfreaks.com/topic/256902-search-case-sensativity-problem/#findComment-1317213 Share on other sites More sharing options...
scootstah Posted February 12, 2012 Share Posted February 12, 2012 C'mon now. $result = mysql_query($query) or die(mysql_error()); Quote Link to comment https://forums.phpfreaks.com/topic/256902-search-case-sensativity-problem/#findComment-1317214 Share on other sites More sharing options...
Pikachu2000 Posted February 12, 2012 Share Posted February 12, 2012 die( "<br>Query failed: $query<br>Error: " . mysql_error() ); Quote Link to comment https://forums.phpfreaks.com/topic/256902-search-case-sensativity-problem/#findComment-1317217 Share on other sites More sharing options...
Angeleyezz Posted February 12, 2012 Author Share Posted 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 Quote Link to comment https://forums.phpfreaks.com/topic/256902-search-case-sensativity-problem/#findComment-1317218 Share on other sites More sharing options...
darkfreaks Posted February 13, 2012 Share Posted 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%\" Quote Link to comment https://forums.phpfreaks.com/topic/256902-search-case-sensativity-problem/#findComment-1317466 Share on other sites More sharing options...
scootstah Posted February 13, 2012 Share Posted 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* Quote Link to comment https://forums.phpfreaks.com/topic/256902-search-case-sensativity-problem/#findComment-1317470 Share on other sites More sharing options...
Angeleyezz Posted February 13, 2012 Author Share Posted 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. Quote Link to comment https://forums.phpfreaks.com/topic/256902-search-case-sensativity-problem/#findComment-1317693 Share on other sites More sharing options...
scootstah Posted February 13, 2012 Share Posted February 13, 2012 Can you show me what a SHOW FIELDS FROM client_information looks like? Quote Link to comment https://forums.phpfreaks.com/topic/256902-search-case-sensativity-problem/#findComment-1317696 Share on other sites More sharing options...
Angeleyezz Posted February 13, 2012 Author Share Posted 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'); ?> Quote Link to comment https://forums.phpfreaks.com/topic/256902-search-case-sensativity-problem/#findComment-1317762 Share on other sites More sharing options...
scootstah Posted February 13, 2012 Share Posted 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? Quote Link to comment https://forums.phpfreaks.com/topic/256902-search-case-sensativity-problem/#findComment-1317774 Share on other sites More sharing options...
Angeleyezz Posted February 13, 2012 Author Share Posted 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] Quote Link to comment https://forums.phpfreaks.com/topic/256902-search-case-sensativity-problem/#findComment-1317785 Share on other sites More sharing options...
scootstah Posted February 13, 2012 Share Posted 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%' Quote Link to comment https://forums.phpfreaks.com/topic/256902-search-case-sensativity-problem/#findComment-1317790 Share on other sites More sharing options...
Angeleyezz Posted February 13, 2012 Author Share Posted 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. Quote Link to comment https://forums.phpfreaks.com/topic/256902-search-case-sensativity-problem/#findComment-1317795 Share on other sites More sharing options...
litebearer Posted February 13, 2012 Share Posted 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 Quote Link to comment https://forums.phpfreaks.com/topic/256902-search-case-sensativity-problem/#findComment-1317810 Share on other sites More sharing options...
Pikachu2000 Posted February 13, 2012 Share Posted February 13, 2012 Or simply change the collation of the table/fields to one that's case insensitive; one that ends with _ci. Quote Link to comment https://forums.phpfreaks.com/topic/256902-search-case-sensativity-problem/#findComment-1317864 Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.