How to combine two MySQL columns to one column?

Question

I want to convert this MySQL table:

title1 | title2 |type  
-------+--------+----
qwe1   | qwe2   | 3  
asd1   | asd2   | 7

to this table in PHP

title | type  
------+-----
asd1  | 7  
asd2  | 7  
qwe1  | 3  
qwe1  | 3

But I don't know how to order the PHP table by the first column correctly.

(This is the code I am currently using)

$sql = "SELECT * FROM table ORDER BY title1, title2";   
$pager = new PS_Pagination( $dbh, $sql, 3, 4, null );//pagination class
$rs = $pager->paginate(); 

while ($row = $rs->fetch(PDO::FETCH_ASSOC)){
    echo "<tr>";
    echo "<td>{$row['title1']}</td>";
    echo "<td>{$row['type']}</td>";
    echo "</tr>";

    echo "<tr>";
    echo "<td>{$row['title2']}</td>";
    echo "<td>{$row['type']}</td>";
    echo "</tr>";
}       

echo "</table>";

Answer 1

In SQL itself you can do it using UNION. And use ORDER BY for the whole query!

SELECT `title1` AS `title`, `type`
FROM `table`
UNION
SELECT `title2` AS `title`, `type`
FROM `table`
ORDER BY `title` ASC

Output

+-------+------+
| TITLE | TYPE |
+-------+------+
|  asd1 |    7 |
|  asd2 |    7 |
|  qwe1 |    3 |
|  qwe2 |    3 |
+-------+------+

Fiddle: http://sqlfiddle.com/#!2/ff9cf/1

Answer 2

try:

SELECT 'title1' AS 'title', 'type' from 'table'
UNION
SELECT 'title2' AS 'title', 'type' from 'table'
ORDER BY 'title'

Hopefully I got the syntax right.

Essentially you want to do two queries and merge the result, which is what UNION does.

EDIT (see other answer for difference between UNION and UNION ALL).

Answer 3

Use As to rename columns to gt the same structure before using Union

SELECT title1 as title, type
FROM YourTable
UNION ALL
SELECT title2 as title, type
FROM YourTable

Answer 4

You need union

i.e.

select * from (select title1 as title, type
from table
union
select title2 as title, type
from table) order by title

Answer 5

I think you need to use UNION:

SELECT title1, type
FROM YourTable
UNION ALL
SELECT title2, type
FROM YourTable

If you don't want to have duplicates, you can remove use UNION instead of UNION ALL.

--EDIT

As has been suggested, if your intentions were to ORDER these results as well, just add an ORDER BY clause and alias your column:

SELECT title1 title, type
FROM YourTable
UNION ALL
SELECT title2 title, type
FROM YourTable
ORDER BY title

Good luck.