echo out from the UNION sql?

Question

<?php 
$sql = mysql_query("
SELECT navn FROM member_film WHERE username = '$pusername'
UNION SELECT meetup FROM member_meetups WHERE byusername = '$pusername'
UNION SELECT title FROM member_tutorials WHERE username = '$pusername'");
$rowit = mysql_fetch_array($sql);
$number = mysql_num_rows($sql);
?>
<? echo $number; ?>
<? echo $rowit["navn"]; ?><br>
<? echo $rowit["meetup"]; ?><br>

So i have this. Now, i have 3 columns where username is username in the database. 2 in member_film, 1 in member_meetups

The echo $number, shows it correct, that i have 3.

Now when i try to echo the "navn", it echo only 1, when there is 2 columns? And when i try to echo "meetup" it says undefined index: meetup, like there's nothing to show.. but there is, mysql_num_rows founded it, else it would have been 2?

I think most of you misunderstood me.. I want to show what the user($pusername) have been posted, in member_film, member_meetups, member_tutorials.. And then if e.g the user have 2 columns with his username, i want to display the "navn" from the same column. Same with title on member_tutorials, and meetup on member_meetups.

An example on how i want it to look like:

Your posts: (which as i want, checks the three table if there's any columns with the user's username in "username" column.)

My new video, its so good (navn, which is from member_film)

My boring meetup with my parents (title, which is from member_meetup)

My great great video!(navn, which is from member_film)

Answer 1

UNION selects, are joining select statements into 1 result set, therefor have the same column names.

Correct SQL:

SELECT navn colName, 'member_film' tableName FROM member_film WHERE username = '$pusername'
UNION SELECT meetup colName, 'member_meetups' tableName FROM member_meetups WHERE byusername = '$pusername'
UNION SELECT title colName, 'member_tutorials' tableName FROM member_tutorials WHERE username = '$pusername'");

and in the php:

<? echo $rowit["colName"]; ?><br>

and to find out what table:

 <? echo $rowit["tableName "]; ?><br>

Answer 2

A union query combines the results of several queries into one resultset. In order for the union to work all subqueries have to return the fields with the same data types for each position of the query or each named field.

So your original query will result in a result set with one field called "navn" (taken from the first sub query of the union). So to make it more readable you could either rename the field using As myName on each of the subqueries (sam munkes' solution).

However, from the way you are accessing the query I believe you want to have one row with all 3 values on it then you should try using this instead:

SELECT member_film, member_meetups.meetup, meber_tutorials.title
FROM member_film, member_meetups, member_tutorials
WHERE member_film.username = '$pusername' AND
      member_film.username = member_meetups.byusername AND
      member_film.username = member_tutorials.username

Edit after the comments:

SELECT CONVERT(varchar(100), navn) AS Comment FROM member_film WHERE username = '$pusername'
UNION
SELECT CONVERT(varchar(100), meetup) AS Comment FROM member_meetups WHERE byusername = '$pusername'
UNION
SELECT CONVERT(varchar(100), title) AS Comment FROM member_tutorials WHERE username = '$pusername'

This should now yield a table having one column named 'comment' and being of varchar(100). You can now access all values via the comment column. If you need to know from which table the information is taken, use Sam's answer. I inserted the convert to ensure that all entries are of the same data type and length, if they are in you DB you can leave this portion out and just use the names. If needed the length needs to be adjusted.

To make it clear, there is only one column with the name "comment" now.