How to use UNION in this case (MySQL)

Question

I have two tables in a DB (table_1 and table_2), Each of them has a mutual column called Name.

I am currently using the following code to import some data (Name,status) only from table_1:

/* database section start */
    $mysqli = new mysqli("z","z","z","z");

    if (mysqli_connect_errno()) {
        printf("Connect failed: %s\n", mysqli_connect_error());
        exit();
    }
/* database section end */

// Choose Relevant items, and turn them into an array
$item_array = array(
'item1', 
'item2',
'item3'
);

//implode items, turn into string
$item_implode = join("','", $item_array);

//declare an overall array for result
$product = array();

$result = $mysqli->query("SELECT Name, Status as status from table_1 where Name IN ('$item_implode') ORDER BY FIELD (Name, '$item_implode');");

while($row = $result->fetch_assoc()) {

    $product_name = $row['Name'];
    // find all keys in $item_array which value is the products
        $keys = array_keys($item_array, $product_name);
    foreach ($keys as $key) {

        // add values for these keys
        $product[$key + 1]["Name"] = $row['Name'];
        $product[$key + 1]["status"] = $row['status'];//

    }
}

What should I add to this code if I want to import data from table_2 as well (e.g., columns named color, date_added)?

My objective is to have these keys:

$product[$x]["Name"]
$product[$x]["status"]
$product[$x]["color"]
$product[$x]["date_added"]

EDIT:

That long thread doesn't answer my question. The code isn't similar to the code I'm using, and I want to know how to apply the UNION exactly in this concrete case.

I've tried using:

$result = $mysqli->query

("(SELECT Name, status as status from table_1 ) 

UNION

(SELECT Name, color as color from table_2 )

where Name IN ('$item_implode') ORDER BY FIELD (Name, '$item_implode');");


while($row = $result->fetch_assoc()) {

But I get fatal error on the last line.

EDIT2:

Still getting an error:

$result = $mysqli->query

("(SELECT Name, status as statu table_1 where Name IN ('$item_implode') ORDER BY FIELD (Name, '$item_implode') ) 
UNION (SELECT color from table_2  where Name IN ('$item_implode') ORDER BY FIELD (Name, '$item_implode') );");

while($row = $result->fetch_assoc()) {

Use `UNION` (http://dev.mysql.com/doc/refman/5.7/en/union.html) — rokas, Jul 13 '16 at 08:44

score 1 · Answer 1 · answered Jul 13 '16 at 08:45

1

$result = $mysqli->query("SELECT table_1.Name, table_1.Status,table_2.color,table_2.date_added from table_1 inner join table_2 on table_1.Name=table_2.Name where table_1.Name IN ('$item_implode') ORDER BY FIELD (Name, '$item_implode');");

answered Jul 13 '16 at 08:45

Mebin Varghese

58
1
10

score 1 · Answer 2 · answered Jul 13 '16 at 08:46

1

(SELECT Name, Status as status FROM table_1 t1)
UNION
(SELECT color, date_added FROM table_2 t2)

answered Jul 13 '16 at 08:46

rokas

1,521
9
16

Please see my edited question, I didn't understand how to apply that UNION. – rockyraw Jul 13 '16 at 09:18
use where/order etc. clauses inside select: ```(SELECT Name, Status as status FROM table_1 t1 where ... order by ...) UNION (SELECT color, date_added FROM table_2 t2 where ... order by ...)``` – rokas Jul 13 '16 at 09:19
Still getting an error, see my second edit – rockyraw Jul 13 '16 at 10:45

score 0 · Answer 3 · answered Jul 13 '16 at 08:47

0

Change your query, and try to use: INNER JOIN table_2

I can`t make the query for you, because I don't have your data...

answered Jul 13 '16 at 08:47

Mecanik

1,539
1
20
50

score 0 · Answer 4 · answered Jul 13 '16 at 08:51

0

Build your Query with a join.

$query="SELECT t1.Name, t1.Status as status, t2.color, t2.date_added) from "
  ."table_1 t1 "
  ."LEFT JOIN table_2 t2 ON t2.Name "
  ."where t1.Name IN ('$item_implode') "
  ."AND t2.Name = t1.Name "
  ."ORDER BY FIELD (t1.Name, '$item_implode');";

answered Jul 13 '16 at 08:51

Martin S.

256
1
10

I prefer a solution that doesn't change the whole structure of my code, if possible – rockyraw Jul 13 '16 at 08:58

How to use UNION in this case (MySQL)

4 Answers4