?
Solved

PHP/MySQL: Need reliable, flexible string search

Posted on 2012-09-18
9
Medium Priority
?
423 Views
Last Modified: 2012-09-19
I'm trying to come up with a search routine that's flexible enough for users to only enter a bit of a name string.

Right now I have this procedure:
BEGIN

SET @p1 = inSearchString;
SELECT * FROM contact
WHERE
t_lastname_en LIKE CONCAT('%', @p1, '%')
OR
t_firstname_en LIKE CONCAT('%', @p1, '%');

END

IN inSearchString varchar(255)

However, this only returns for a full name match with correct capitalization. (That is, "Karl" will return, but "K" or "arl" will not return. Please help!
0
Comment
Question by:kpisor
  • 3
  • 2
  • 2
  • +2
9 Comments
 
LVL 111

Expert Comment

by:Ray Paseur
ID: 38412303
Suggest you take advantage of the "big guns" in search.  Produce a web page that contains the data you want clients to be able to search.  Then feed the page to Google and set up a Google site search.  It's likely that Google will be able to return the expected and desired results with no effort on your part at all, except to add a Google search to your site!
0
 

Author Comment

by:kpisor
ID: 38412365
Well, this is actually searching confidential client records, only available in a password-protected HTTPS-locked part of the site...not appropriate to expose to the real world...
0
 
LVL 26

Expert Comment

by:arober11
ID: 38412463
Search is horrid, if your serious about adding natural language search capabilities to your app,  then you may want to look at Apache Solr or Microsoft's FAST search engines (the latter is now only bundled with Sharepoint), or at least read some of the background documentation, if you want to build your own toy.
0
NEW Veeam Backup for Microsoft Office 365 1.5

With Office 365, it’s your data and your responsibility to protect it. NEW Veeam Backup for Microsoft Office 365 eliminates the risk of losing access to your Office 365 data.

 
LVL 84

Expert Comment

by:Dave Baldwin
ID: 38412753
Why not just do it in PHP?  'qdat' is the variable passed from a form.  You may need to limit the number of responses.  This is taken from one of my pages where it is used with an 'autosuggest' form.

<?php 
$qdat = $_GET["qdat"];
require_once "db.inc.php";
//mysql_connect('localhost', 'mysql_user', 'mysql_password');
$dbh = mysql_pconnect($dbhost, $dbuser, $dbpass);
mysql_select_db($dbname);

$result = mysql_query("SELECT * FROM `contact ` WHERE t_lastname_en LIKE '%$qdat%' OR t_firstname_en  LIKE '%$qdat%");
if (!$result) {
   echo 'Could not run query: ' . mysql_error();
   exit;
}
echo '<br>';
$row = mysql_fetch_array($result, MYSQL_NUM);
if($row == null) echo '<i>No match.</i>';
else echo '<a href='."$row[0]>".$row[1].'</a><br>';
while ($row = mysql_fetch_array($result, MYSQL_NUM)) {
	echo '<a href='."$row[0]>".$row[1].'</a><br>';
	}
?>

Open in new window

0
 

Author Comment

by:kpisor
ID: 38412799
Thanks, I already have the form to collect the search string and to display the output data. How is the above different from
WHERE
t_lastname_en LIKE CONCAT('%', @p1, '%')
OR
t_firstname_en LIKE CONCAT('%', @p1, '%');

-> If it really is different, then I'd like to know how.
-> Perhaps concatenating the search string before it is fed to MySQL will make a difference? Please advise.
0
 
LVL 111

Expert Comment

by:Ray Paseur
ID: 38413874
If you have to search for yourself, there are a couple of PHP functions that can help you.  Please read these descriptions over and consider how adding a couple of columns to your data base tables might facilitate the search.  If you have further questions, please post back and I'll show you what I might do with the "fuzzy logic" matching.
http://php.net/manual/en/function.soundex.php
http://php.net/manual/en/function.metaphone.php

Best regards, ~Ray
0
 
LVL 84

Expert Comment

by:Dave Baldwin
ID: 38414661
It's different because it assembles the query in PHP where you can change it until you get what you want and because it is known to work.
0
 
LVL 82

Accepted Solution

by:
hielo earned 2000 total points
ID: 38415685
If all you want is a quick "fix" to what you posted, try:
BEGIN

SET @p1 = LCASE(inSearchString);
SELECT * FROM contact 
WHERE 
	( LCASE(`t_lastname_en`) LIKE CONCAT(@p1, '%') OR LCASE(`t_lastname_en`) LIKE CONCAT('%', @p1) )
OR
	( LCASE(`t_firstname_en`) LIKE CONCAT(@p1, '%') OR LCASE(`t_firstname_en`) LIKE CONCAT('%', @p1) );

END

Open in new window



However, if I were in your shoes and changing the db/table structure is an option I would consider using a FULLTEXT index and use mysql's FULLTEXT searching capabilities.

In case you are not familiar with it, you might find these useful:
http://www.mysqltutorial.org/mysql-full-text-search.aspx
http://www.tech-evangelist.com/2008/01/18/mysql-fulltext-01/
http://devzone.zend.com/26/using-mysql-full-text-searching/

Regards,
Hielo
0
 

Author Comment

by:kpisor
ID: 38416041
Hielo,

Thank you! That was exactly what I was looking for. I don't need the FULLTEXT searching capabilities now, but your reference articles are very welcome.

Thanks again,

Karl
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this blog, we’ll look at how improvements to Percona XtraDB Cluster improved IST performance.
The title says it all. Writing any type of PHP Application or API code that provides high throughput, while under a heavy load, seems to be an arcane art form (Black Magic). This article aims to provide some general guidelines for producing this typ…
The viewer will learn how to dynamically set the form action using jQuery.
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.
Suggested Courses
Course of the Month14 days, 15 hours left to enroll

839 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question