AND and LIKE together

Am trying to write a query that will use AND and LIKE together…I want the query to return the rows where email address and the keywords enter in search text box match…if the keywords
only match and the email address is not match, it return nothing(no match found) and also if the email address match and the keywords enter in the text box do not match any word, it should return nothing(no match found). It should only return value when the email address and keywords match. Pls kindly help me out…

here is my query below and it only return all rows that match the keywords enter in the search text box only

SELECT biz_name, biz_title, biz_description,
biz_logo, bizemail_add FROM business WHERE bizemail_add =:bizemail_add AND
business_name LIKE :business_name 
OR business_title LIKE :business_title 
OR business_description LIKE :business_description

Thanks in Advance.

You need to add parenthesis around the OR conditions.

SELECT biz_name
     , biz_title
     , biz_description
     , biz_logo
     , bizemail_add 
  FROM business 
 WHERE bizemail_add =:bizemail_add 
   AND (business_name LIKE :business_name OR 
	business_title LIKE :business_title OR 
	business_description LIKE :business_description)
1 Like

i love your sql style, man :wink:

similar to leading commas, i also use leading connectors –

FROM business WHERE bizemail_add =:bizemail_add AND ( business_name LIKE :business_name OR business_title LIKE :business_title OR business_description LIKE :business_description )

You should. I picked it up from you :smiley:

I usually use leading connectors as well, but find putting them in the back in cases like this are easier to read and follow…

Thanks so much @DaveMaxwell and @r937 …the search work fine now. :smiley:

@DaveMaxwell and @r937

Pls I need help on this query result…when some text like car dealer enter into the search text box, the query will return value and when car dealers enter no result it return…pls I want it to return value when similar text is entered.

Help me with the code below…

$bquery = "(SELECT business_name
     ,business_title
     ,business_description
     ,company_logo
     ,email_add
     FROM business 
 WHERE email_add =:email 
   AND ( business_name LIKE :business_name 
      OR business_title LIKE :business_title 
      OR business_description LIKE :business_description
       ))";

$s = $pdo->prepare($bquery);
$placeholders[':email'] =  $LocEmail['biz_emailadd'];
$placeholders[':business_name'] = '%' . $keyword . '%';
$placeholders[':business_title'] = '%' . $keyword . '%';
$placeholders[':business_description'] = '%' . $keyword . '%';
$s->execute($placeholders);

Thanks

Yeah, it will do that because if there is no value in the keyword variable, then all values will be returned (a ‘%%’ will search every record and return every record since there’s nothing there to filter against.)

Easiest way to handle it would be to add a check of keyword, and if it’s blank, fill it with something completely obscure and made up that could never be in the text being searched (random keys like ‘wsofghaew;ogruhaeol’).

I hope you have got your answer till now. But in case you need help, please let me know. You ca use AND, LIKE in same query but with different styles. Just let me know if you need more light on it.

Regards,
Narender

Post edited by TechnoBear to remove fake signature

I know this is getting away from the query and into PHP territory, but I prefer to “assemble” the query.

In pseudo-code, something (remotely) like

$query = "the beginning stuff";
if (there is a var) {
$query .= " AND something = " . the var;
}
etc etc.
run($query);

Hi,
Everything is correct except use parenthesis between AND and OR clauses.
Like
SELECT biz_name, biz_title, biz_description,
biz_logo, bizemail_add FROM business WHERE (bizemail_add =:bizemail_add) AND
(business_name LIKE :business_name
OR business_title LIKE :business_title
OR business_description LIKE :business_description )

@ankush93 I need more light on it

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.