CODEWITHSUNDEEP
phpmysql
thinkofacard
thinkofacard

Reputation: 501

using union on two tables but renaming the field

I have two tables that have an id field with the same name. I didn't think I'd ever need to mix the two but there's one page where I need to. I can't join the tables because they both have completely separate data and no fields in common.

I can union them but the ID field is the same name and many identical numbers (which do not relate). I can't change the name in the tables but I need the field names to be different when put into a variable (using PHP).

I tried something like this:

SELECT date, id as id1
FROM football

UNION
SELECT date, id as id2
FROM basketball
ORDER BY date

But that just gives me one field (id1). I need the result to be in such a way that I can do this:

foreach ($rows as $row) {
   if (!empty($row['id1'])) {
      $id = $row['id1'];
      $sport = "football";
   } else {
      $id = $row['id2'];
      $sport = "basketball";
   }
   echo "my number is $id and I play $sport";
}

Upvotes: 1

Views: 1169

Answers (1)

AbraCadaver
AbraCadaver

Reputation: 79024

From MySQL Union Syntax

The column names from the first SELECT statement are used as the column names for the results returned.

You could assign sport in your query:

SELECT date, id, 'football' as sport
FROM football    
UNION
SELECT date, id, 'basketball' as sport
FROM basketball
ORDER BY date

Upvotes: 2

Related Questions