fetch rows from two different table on MySQL without any relation between both tables

Question

There is two tables there is nor relation between both tables,

structure: table_one

+---------+--------+-----------+
| name    | age    |insertTEMP |
+---------+--------+-----------+
| cat     | 12     |02-02-2014 |
| dog     | 13     |03-04-2014 |
+---------+--------+-----------+

structure: table_two

+---------+--------+---------------+
| book    | pages  | insertTEMP    |
+---------+--------+---------------+
| book1   | 34     | 05-02-2014    |
| book2   | 54     | 04-03-2014    |
+---------+--------+---------------+

now the idea is here i want to list records from both table so i keep bellow function to list them.

public function recentALL() {
   $sql = "select * from table_one, table_two ORDER BY insertTEMP";
    $q = $this -> conn -> prepare($sql);
    $q -> execute();
    return $row = $q -> fetchAll();
 }

and i fetch it in the view,

        $data2 = $functions->recentAll();
            foreach ($data2 as $data3){
                  echo $data3['name']  . $data3['book'] . '<br />';

           }

how i can make it to show like:

+---------+--------+
| cat     | NULL   |
| null    | book1  |
| null    | book2  |
| dog     | null   |
+---------+--------+

this based on the insertTEMP

Sidenote: You do realize that `insert` is a MySQL reserved word http://dev.mysql.com/doc/refman/5.5/en/reserved-words.html — Funk Forty Niner, Nov 11 '14 at 04:14
Pretty sure you can use UNION http://www.mysqltutorial.org/sql-union-mysql.aspx — Funk Forty Niner, Nov 11 '14 at 04:23
Try a query like -> `SELECT name, NULL as book, insertTEMP FROM table_one UNION SELECT NULL as name, book, insertTEMP FROM table_tow ORDER BY insertTEMP` — Sean, Nov 11 '14 at 04:25
What do you mean by "based on insertTEMP"? How does `insertTEMP` affect your results? — Mark Miller, Nov 11 '14 at 04:29
i mean i will sort it ORDER BY insertTEMP, here when i use union it works but when i add order by too it' gives error — SAR, Nov 11 '14 at 04:50
This is duplicate of http://stackoverflow.com/questions/12890071/select-from-multiple-tables-mysql. — EternalHour, Nov 11 '14 at 04:56

score 0 · Answer 1 · answered Nov 11 '14 at 06:20

Use the following query

Select * from (
    Select name  as Name, null as Book,insertTEMP  from table_one
    UNION ALL
    select null as Name, book as Book,insertTEMP  from table_two
) as tmp
Order by tmp.insertTEMP asc

Change the column names in your code as per query

fetch rows from two different table on MySQL without any relation between both tables

1 Answers1