Mysql – Query larger than table – MySQL and PHP

I am working on a db with only a few records, say 5 only. The DB has several columns; records being each row and a field being each column in the row/record. What's the correct Query?

db: movies
table: main

mysql> select Title, Genre, Genre2, Genre3, Actor1, Actor2, Actor3 from main;
+-------------------+-----------+-----------+---------+-----------------+-----------------------+----------------------+
| Title             | Genre     | Genre2    | Genre3  | Actor1          | Actor2                | Actor3               |
+-------------------+-----------+-----------+---------+-----------------+-----------------------+----------------------+
| Avatar            | Action    | Adventure | Fantasy | Sam Worthington | Zoe Saldana           | Sigourney Weaver     |
| Oblivion          | Action    | Adventure | Mystery | Tom Cruise      | Morgan Freeman        | Andrea Riseborough   |
| The Big Short     | Biography | Comedy    | Drama   | Christian Bale  | Steve Carell          | Ryan Gosling         |
| The Great Escape  | Adventure | Drama     | History | Steve McQueen   | James Garner          | Richard Attenborough |
| The Green Mile    | Crime     | Drama     | Fantasy | Tom Hanks       | Michael Clarke Duncan | David Morse          |
+-------------------+-----------+-----------+---------+-----------------+-----------------------+----------------------+
5 rows in set (0.00 sec)

mysql>

Problem:
I have discovered (I believe) that a QUERY (eg UNION), although it combines cannot exceed the total number of records. Is this right? What I want to do is query the db and have the result be larger than the actual original table. In other words I want to ask for All Genre + All Genre2 + All Genre 3, DISTINCT; to act as a list of all possible genres in a db of 5 movies. As a movie is advertised it has up to 3 genres as shown in my examples.

Example QUERY : PHP to QUERY movies.main

$queryTable = "(SELECT Genre FROM $table_name_long)";
$queryTable .= " UNION ";
$queryTable .= "(SELECT Genre2 FROM $table_name_long)";
$queryTable .= " UNION ";
$queryTable .= "(SELECT Genre3 FROM $table_name_long);";

or simply

(SELECT Genre FROM movies.main)
UNION
(SELECT Genre2 FROM movies.main)
UNION
(SELECT Genre3 FROM movies.main);

Actual Query Result: 5 records (I suspect 5 is max result due to table size?)

Action
Biography
Adventure
Crime
Comedy

Desired Query Result: 9 records or fields combined from Genre + Genre2 + Genre3

Action
Adventure
Biography
Comedy
Crime
Drama
Fantasy
History
Mystery

Here is the table: movies.main

mysql> show create table main;
+-------+------------------------------------------------------------
---------------------------------------------------------------------
| Table | Create Table

+-------+------------------------------------------------------------
---------------------------------------------------------------------
| main  | CREATE TABLE `main` (
  `Title` varchar(100) COLLATE latin1_general_ci NOT NULL,
  `RunTime` varchar(20) COLLATE latin1_general_ci NOT NULL,
  `Genre` varchar(150) COLLATE latin1_general_ci NOT NULL,
  `Genre2` varchar(150) COLLATE latin1_general_ci NOT NULL,
  `Genre3` varchar(150) COLLATE latin1_general_ci NOT NULL,
  `Actor1` varchar(150) COLLATE latin1_general_ci NOT NULL,
  `Actor2` varchar(150) COLLATE latin1_general_ci NOT NULL,
  `Actor3` varchar(150) COLLATE latin1_general_ci NOT NULL,
  `YearMovieMade` varchar(4) COLLATE latin1_general_ci NOT NULL,
  `Director` varchar(150) COLLATE latin1_general_ci NOT NULL,
  `Description` text COLLATE latin1_general_ci,
  `ImageLink` varchar(200) COLLATE latin1_general_ci NOT NULL,
  `Link` varchar(200) COLLATE latin1_general_ci NOT NULL,
  PRIMARY KEY (`Title`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci |
+-------+------------------------------------------------------------
---------------------------------------------------------------------
1 row in set (0.00 sec)

mysql> describe main;
+---------------+--------------+------+-----+---------+-------+
| Field         | Type         | Null | Key | Default | Extra |
+---------------+--------------+------+-----+---------+-------+
| Title         | varchar(100) | NO   | PRI | NULL    |       |
| RunTime       | varchar(20)  | NO   |     | NULL    |       |
| Genre         | varchar(150) | NO   |     | NULL    |       |
| Genre2        | varchar(150) | NO   |     | NULL    |       |
| Genre3        | varchar(150) | NO   |     | NULL    |       |
| Actor1        | varchar(150) | NO   |     | NULL    |       |
| Actor2        | varchar(150) | NO   |     | NULL    |       |
| Actor3        | varchar(150) | NO   |     | NULL    |       |
| YearMovieMade | varchar(4)   | NO   |     | NULL    |       |
| Director      | varchar(150) | NO   |     | NULL    |       |
| Description   | text         | YES  |     | NULL    |       |
| ImageLink     | varchar(200) | NO   |     | NULL    |       |
| Link          | varchar(200) | NO   |     | NULL    |       |
+---------------+--------------+------+-----+---------+-------+
13 rows in set (0.02 sec)

EDIT: Partial resolution – seems SQL gives desired result if done on the mysql prompt. Something strange with PHP then? Or is it me…?

mysql> (SELECT Genre FROM movies.main)
    -> UNION
    -> (SELECT Genre2 FROM movies.main)
    -> UNION
    -> (SELECT Genre3 FROM movies.main);
+-----------+
| Genre     |
+-----------+
| Action    |
| Biography |
| Adventure |
| Crime     |
| Comedy    |
| Drama     |
| Fantasy   |
| Mystery   |
| History   |
+-----------+
9 rows in set (0.00 sec)

Final thoughts about this puzzle: Since the query works in the mysql prompt as expected, but doesn't work in PHP, it gives rise to this: Is the result a temporary table then? Because I query the table and then ask for the… I just figured it out folks! Mid-sentence as I am typing now.

I was using a (counter*) to write my results in the PHP code! The "counter" was set to count the records; thus the limited results. So, it's me. But my code is awesome and perfect! Isn't it? So it reminds me why once again it isn't always a good idea to use a counter, but rather a "foreach" type of loop, if available.

Thanks to: @ypercubeᵀᴹ whom convinced me to look at the basic functionality of mysql before anything else. RESOLVED.

@ypercubeᵀᴹ go ahead and copy/paste my final query above that shows the query works and I'll accept the solution.