Search problem 1

[craigglenn]

This seemed like a simple issue but has proven difficult for me.

I have a directory on a site I manage. Wordpress3/MySQL 5.0.7/PHP 5.1.6

I have 1K plus company_name's with a '-' in the name.

My problem is if I include the '-' I get results ie adams-green returns the company I was looking for but if I search 'adams green' it will not.

Here is the code I use with my attempt to resolve with a simple OR statement. It works fine for me in straight sql but not in my PHP code. I am sure it is a syntax issue but can't track it down. I have include the code below. I do not get an error or NO RESULTS message. the page just hangs as if I put it in a loop or have a syntax problem. I have to reload the page to clear it up. If I remove the OR logic it works fine but with the '-' problem.

PHP

$query = "SELECT * FROM ".BIZDIRDBTABLE." l LEFT JOIN ".BIZDIRCATTABLE." c ON (c.category_id = l.category_id) WHERE status='1' ";

if(!empty($searchTerms))
{
$query .= $wpdb->prepare("AND (company_name LIKE %s OR REPLACE(company_name,'-',' ') LIKE %s) ORDER BY c.category_id ASC, company_name ASC",'%'.$searchTerms.'%');
}

SQL that works for my testing purposes.

SET @v = 'adams green';
SELECT *
FROM `wp_biz_listings`
WHERE status=1
AND (replace(company_name,'-',' ') like concat('%',@v,'%')
OR company_name like concat('%',@v,'%'))

Thanks in advance for your help.

"You can have anything in life you want if you help enough people get what they want"
Zig Ziggler

 

[jpadie]

try this instead

$_searchTerms = mysql_real_escape_string($searchTerms);
$_searchTerms = str_replace(' ', '( |\-)?', $_searchTerms);
$query .= " AND (company_name regexp '.*{$_searchTerms}.*') ORDER BY c.category_id ASC, company_name ASC");

 

[craigglenn]

thanks jpadis,

I am not sure I fully understand all of that code but here is what I am thinking. This will strip out sp char like the - and whatnot. for every search.

I want to be able to find the record Adams-Green if the user searches by 'adams-green' OR 'adams green'

I am able to accomplish this with straight SQL but falling short with the PHP.

Also I do some data validation at the beginning of the search Function...
function bizdir_directory($searchTerms = "",$city = "",$zip = "") {
global $wpdb;
//Validate input
$searchTerms = strip_tags(trim($searchTerms));
$city = strip_tags(trim($city));
$zip = strip_tags(trim($zip));
$state = strip_tags(trim($state));

I did inherit this code so there are some things I can't explain..

Sorry to be a pain...

"You can have anything in life you want if you help enough people get what they want"
Zig Ziggler

 

[craigglenn]

$_searchTerms = mysql_real_escape_string($searchTerms);
$_searchTerms = str_replace(' ', '( |\-)?', $_searchTerms);
$query .= " AND (company_name regexp '.*{$_searchTerms}.*') ORDER BY c.category_id ASC, company_name ASC");

OK so I did some research and get the first two lines. I am having trouble with "regexp". not finding much help with that online.



"You can have anything in life you want if you help enough people get what they want"
Zig Ziggler

 

[craigglenn]

thanks jpadie,

it works! Thanks again for your help.



"You can have anything in life you want if you help enough people get what they want"
Zig Ziggler

 

[jpadie]

good oh! the regexp tells mysql to search via a regular expression rather than plain text.