php - Combine Multiple MySQL Queries on the Same Table Into One -

how make multiple queries on same table, selecting different columns?

if helps @ all... of queries have common column in select part of sql statement. select id, followed specific.

so every query needs id , either of following: post_name, post_title, or post_excerpt.

also if helps simplify things. i'm trying search broad matches , exact matches in these columns.

so in example, looking for: "floor finish", "floor", or "finish" in following columns: post_name, post_title, , post_excerpt. within same table.

i've attempted accomplish union.

here queries:

array (     [broad] => array         (             [floor] => array                 (                     [slugs] => select `id`, `post_name` tps_3_posts `post_status` = 'publish' , `post_name` '%floor%'                     [titles] => select `id`, `post_title` tps_3_posts `post_status` = 'publish' , `post_title` '%floor%'                     [excerpts] => select `id`, `post_excerpt` tps_3_posts `post_status` = 'publish' , `post_excerpt` '%floor%'                 )              [finish] => array                 (                     [slugs] => select `id`, `post_name` tps_3_posts `post_status` = 'publish' , `post_name` '%finish%'                     [titles] => select `id`, `post_title` tps_3_posts `post_status` = 'publish' , `post_title` '%finish%'                     [excerpts] => select `id`, `post_excerpt` tps_3_posts `post_status` = 'publish' , `post_excerpt` '%finish%'                 )          )      [exact] => array         (             [slugs] => select `id`, `post_name` tps_3_posts `post_status` = 'publish' , `post_name` '%floor-finish%'             [titles] => select `id`, `post_title` tps_3_posts `post_status` = 'publish' , `post_title` '%floor finish%'             [excerpts] => select `id`, `post_excerpt` tps_3_posts `post_status` = 'publish' , `post_excerpt` '%floor finish%'         )      [combined] => ( select `id`, `post_name` tps_3_posts `post_status` = 'publish' , `post_name` '%floor-finish%' ) union ( select `id`, `post_name` tps_3_posts `post_status` = 'publish' , `post_name` '%floor%' ) union ( select `id`, `post_name` tps_3_posts `post_status` = 'publish' , `post_name` '%finish%' ) union ( select `id`, `post_title` tps_3_posts `post_status` = 'publish' , `post_title` '%floor finish%' ) union ( select `id`, `post_title` tps_3_posts `post_status` = 'publish' , `post_title` '%floor%' ) union ( select `id`, `post_title` tps_3_posts `post_status` = 'publish' , `post_title` '%finish%' ) union ( select `id`, `post_excerpt` tps_3_posts `post_status` = 'publish' , `post_excerpt` '%floor finish%' ) union ( select `id`, `post_excerpt` tps_3_posts `post_status` = 'publish' , `post_excerpt` '%floor%' ) union ( select `id`, `post_excerpt` tps_3_posts `post_status` = 'publish' , `post_excerpt` '%finish%' ) ) 

however, above result interesting. appear correct results except key of each result value (which supposed name of column) remains same. it's post_name though value assigned might post_title or post_excerpt.

so every result has id , post_name. keys wrong values appear accurate.

i tried this:

array         (             [broad] => array                 (                     [floor] => select `id`, `post_name`, `post_title`, `post_excerpt` tps_3_posts `post_status` = 'publish' , ( `post_name` '%floor%' or `post_title` '%floor%' or `post_excerpt` '%floor' )                     [finish] => select `id`, `post_name`, `post_title`, `post_excerpt` tps_3_posts `post_status` = 'publish' , ( `post_name` '%finish%' or `post_title` '%finish%' or `post_excerpt` '%finish%' )                 )              [exact] => select `id`, `post_name`, `post_title`, `post_excerpt` tps_3_posts `post_status` = 'publish' , ( `post_name` '%floor-finish%' or `post_title` '%floor finish%' or `post_excerpt` '%floor finish%' )             [combined] => select `id`, `post_name`, `post_title`, `post_excerpt` tps_3_posts `post_status` = 'publish' , ( `post_name` '%floor-finish%' or `post_title` '%floor finish%' or `post_excerpt` '%floor finish%' ) union (select `id`, `post_name`, `post_title`, `post_excerpt` tps_3_posts `post_status` = 'publish' , ( `post_name` '%floor%' or `post_title` '%floor%' or `post_excerpt` '%floor%' )) union (select `id`, `post_name`, `post_title`, `post_excerpt` tps_3_posts `post_status` = 'publish' , ( `post_name` '%finish%' or `post_title` '%finish%' or `post_excerpt` '%finish%' ))         )  ) 

this more along lines of i'm trying accomplish. each result have id, post_excerpt, post_slug, , post_title. if there's no match, display key empty value, or don't display key.

the problem second attempt it's requiring match in 1 of 3 desired columns. if matched in post_excerpt , no else, still pull values post_title , post_name. making results inaccurate.

i've read several appear similar questions don't have real solid clear answers... or ... questions/answers more geared multiple queries on separate tables.

any guidance or advice on combining multiple mysql queries on same table?

by way... using "combined" in both examples final query send database.

so 1 more time... if there's no match in column, display key null or omit key results entirely.

you're getting wrong "key" because of union statement in query. have different key names, compatible column types between 3 different columns unioned queries. rather throw error, database engine picking column name first query , using of them:

id | post_name # <= column name in first query 1  | "my post"  union  id | post_title # <= column name different, type compatible, union succeeds 1  | "my post title"  union  id | post_excerpt # <= ditto 1  | "my post excerpt" 

would result in:

id | post_name # <= column name first query 1  | "my post" 1  | "my post title" 1  | "my post excerpt" 

which experiencing.

instead, like:

id | post_name | post_title | post_excerpt 1  | "my post" | null       | null        # <= deliberately select nulls these columns in query  union  id | post_name | post_title      | post_excerpt 1  | null      | "my post title" | null  union  id | post_name | post_title | post_excerpt 1  | null      | null       | "my post excerpt" 

which give results like:

id | post_name | post_title      | post_excerpt 1  | "my post" | null            | null 1  | null      | "my post title" | null 1  | null      | null            | "my post excerpt" 

with table, basic version of might like:

select id, post_name, null post_title, null post_excerpt tps_3_posts  union  select id, null post_name, post_title, null post_excerpt tps_3_posts  union  select id, null post_name, null post_title, post_excerpt tps_3_posts 

which might more usable trying do. here's sqlfiddle if want see in action.