How to search three table for result

aosworks · September 21, 2015, 12:33pm

Am new to this type of sql query…how to write the SQL query to search three table for result. Am trying to implement a search feature where the keyword/text enter by the user can search three different table for result and if the keyword/text entered by the user do not match any text in this three tables no match found should be return.

I want to search like…

table_1

SELECT * FROM business WHERE 
    business_name LIKE :business_name 
    OR business_title LIKE :business_title 
    OR business_description LIKE :business_description

table_2

SELECT * FROM products WHERE 
    product_name LIKE :product_name
    OR product_description LIKE :product_description

table_3

 SELECT * FROM personal WHERE first_name LIKE :first_name 
    OR last_name LIKE :last_name
    OR personal_description LIKE :personal_description

If the keyword/text entered by the user match any text in one of the table or all three tables the whole result should be return as the result…

Thanks

r937 · September 21, 2015, 1:44pm

[quote=“aosworks, post:1, topic:202359, full:true”]…the whole result should be return as the result…
[/quote]presumably “the whole result” is why you’re using the dreaded, evil “select star”

your solution: three separate queries

you’re welcome

aosworks · September 21, 2015, 2:02pm

am showing three SELECT separate queries here to show how I want it to be perform…what am expecting is to put the query in single select or something…all I want is how I can search the three tables…kindly help…

DaveMaxwell · September 21, 2015, 2:03pm

Do three distinct queries. Sometimes the simplest approach is the best one.

aosworks · September 21, 2015, 2:05pm

@r937 I want to search table business,personal and the product table…just with one query…

aosworks · September 21, 2015, 2:12pm

@Davemaxwell how can I go about that?

I want to search the three tables business, personal and product by using LIKE for the column I specify

DaveMaxwell · September 21, 2015, 2:18pm

Did you run the queries? It looks like that’s what you’re doing?

If you’re talking about getting them into one result set (which in your case I think would be a BAD idea since they are totally different logical units), you’re going to have to use UNIONS. Now to do that, you need to have a consistent group of fields returned

So, for a quick and dirty from your original question…

SELECT 'BUSINESS' AS resultType
     , business_name AS resultName
     , business_title AS resultTitlee
     , business_description AS resultDescription
  FROM business 
 WHERE business_name LIKE :business_name 
    OR business_title LIKE :business_title 
    OR business_description LIKE :business_description
UNION
SELECT 'PRODUCT' AS resultType
     , product_name AS resultName
     , '' AS resultTitle
     , product_description AS resultDescription 
  FROM products 
 WHERE product_name LIKE :product_name
    OR product_description LIKE :product_description
UNION
SELECT 'PERSONAL' AS resultType
     , first_name + ' ' + last_name AS resultName
     , '' AS resultTitle
     , personal_description AS resultDescription 
  FROM personal 
 WHERE first_name LIKE :first_name 
    OR last_name LIKE :last_name
    OR personal_description LIKE :personal_description

aosworks · September 21, 2015, 3:39pm

Thanks for your time…@DaveMaxwell…do I need to specify all the name of the column I want it to search and return?

I run the query just like this and am getting error message that says Undefined index:

$query = " SELECT 'BUSINESS' AS resultType
     , business_name AS resultName
     , business_title AS resultTitlee
     , business_description AS resultDescription
  FROM business 
 WHERE business_name LIKE :business_name 
    OR business_title LIKE :business_title 
    OR business_description LIKE :business_description
UNION
SELECT 'PRODUCT' AS resultType
     , product_name AS resultName
     , '' AS resultTitle
     , product_description AS resultDescription 
  FROM products 
 WHERE product_name LIKE :product_name
    OR product_description LIKE :product_description
UNION
SELECT 'PERSONAL' AS resultType
     , first_name + ' ' + last_name AS resultName
     , '' AS resultTitle
     , personal_description AS resultDescription 
  FROM personal 
 WHERE first_name LIKE :first_name   ";

in business table I want it to select the column

b_user_id, business_name, business_title, business_description,
company_logo, email_add

In personal I want it to select

product_id, product_name, product_image_name, product_price, product_description,
company_id, company_name, added_datetime

and in product I want it to select

p_user_id, first_name, pic_pro_name, last_name, company_email, personal_description

any help…

DaveMaxwell · September 21, 2015, 3:48pm

Are you sure the column names are right? That is the only thing that I can think of that would cause that undefined index, so check the selected columns AND the column names in the where clauses.

You need to have the same number of columns (with the same names) to do a UNION. If you look at my example, the column names are all renamed using the AS keyword. I DID notice a typo there (there’s a resultTitle in the first query, where the rest are correctly named resultTitle)

If you want completely diverse column names, than it would probably be better to follow Rudy (and my) earlier advice to run the queries separately.

r937 · September 21, 2015, 4:15pm

[quote=“aosworks, post:8, topic:202359, full:true”]in business table I want it to select the column

b_user_id, business_name, business_title, business_description,
company_logo, email_add

In personal I want it to select

product_id, product_name, product_image_name, product_price, product_description,
company_id, company_name, added_datetime

and in product I want it to select

p_user_id, first_name, pic_pro_name, last_name, company_email, personal_description

any help…
[/quote]you have already received the help you needed and asked for

two solutions:

use three separate queries
kludge together a UNION query with all the columns you need – but make sure that the three subselects return the same number and types of columns

my advice: get a lot of practice writing UNION queries, or else use solution 1 and move on

aosworks · September 21, 2015, 6:04pm

thanks for your time @DaveMaxwell @r937 … I think am getting the Undefined index: error because my loop array is empty…

$query = " SELECT 'BUSINESS' AS resultType
     , business_name AS resultName
     , business_title AS resultTitle
     , business_description AS resultDescription
  FROM business 
 WHERE business_name LIKE :business_name 
    OR business_title LIKE :business_title 
    OR business_description LIKE :business_description
UNION
SELECT 'PRODUCT' AS resultType
     , product_name AS resultName
     , '' AS resultTitle
     , product_description AS resultDescription 
  FROM products 
 WHERE product_name LIKE :product_name
    OR product_description LIKE :product_description
UNION
SELECT 'PERSONAL' AS resultType
     , first_name + ' ' + last_name AS resultName
     , '' AS resultTitle
     , personal_description AS resultDescription 
  FROM personal 
 WHERE first_name LIKE :first_name   ";

$s = $pdo->prepare($query);
$placeholders[':business_name'] = '%' . $keyword . '%';
$placeholders[':business_title'] = '%' . $keyword . '%';
$placeholders[':business_description'] = '%' . $keyword . '%';
$placeholders[':first_name'] = '%' . $keyword . '%';
$placeholders[':product_name'] = '%' . $keyword . '%';
$placeholders[':product_description'] = '%' . $keyword . '%';
$s->execute($placeholders);

foreach ($s as $row){
 $searchRow[] = array( 
'first_name' =>$row['first_name'], 
'business_name' =>$row['business_name'], 
'business_title' =>$row['business_title'], 
'business_description' =>$row['business_description'],
'product_name' =>$row['product_name'], 
'product_description' =>$row['product_description'] ); }

please kindly help on how I can go about it…am getting list of Undefined index: error for each column name. the column names are all right…I guess $searchRow is empty…

DaveMaxwell · September 21, 2015, 6:20pm

No…you don’t have those column indexes in your $searchRow resultset. You’ll have columns resultType, resultName, resultTitle and resultDescription. That’s what the AS keyword does - it changes the column name returned in the result set. Its also why I added the resultType column - it allows you to know how to handle each row of data.

That’s part of the reason I suggested the three queries (and I’m sure why Rudy did as well). Unless you need to return the results in one recordset (i.e. you’re displaying it in ONE grid), then there isn’t a benefit to doing the unions…

aosworks · September 21, 2015, 7:23pm

Thanks so much @DaveMaxwell…it working fine now… I hope to see you again for any future help…am still new to so many things…Thanks once again…and @r937 thanks for your time.

system · December 22, 2015, 2:24am

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