Does UNION ALL return an array?

Question

I have a sql statement that is a union of several queries.

"SELECT a, b, c, 'd' as type FROM x WHERE id = $id UNION ALL
 SELECT a, b, c, 'e' as type FROM y WHERE id = $id UNION ALL
 SELECT a, b, c, 'f' as type FROM z WHERE id = $id ORDER BY a DESC LIMIT 10";

I try to iterate through the results and get 'a' value for each row but can't make it to work.

for($i =  0 ;  $i <  mysql_num_rows($result) ; $i++){
    $row = mysql_fetch_assoc($result);
    echo($row['a']);
}

I know it is a silly question but I got stuck. Any ideas please? Thanks!

Answer 1

Did you copy/paste your code or retype it?

$row = mysql_fetch_assoc($guery);

Come to think of it, you should be calling mysql_fetch_assoc on a result handle, not a query.

Answer 2

Solved. I changed the query to this and it worked:

"SELECT a as A, b as B, c as C, 'd' as type FROM x WHERE id = $id UNION ALL
 SELECT a as A, b as B, c as C, 'e' as type FROM y WHERE id = $id UNION ALL
 SELECT a as A, b as B, c as C, 'f' as type FROM z WHERE id = $id ORDER BY a DESC LIMIT 10";

Answer 3

Bad practice, Should never call a function in a for loop this will cause performance issues.

for($i =  0 ;  $i <  mysql_num_rows($result) ; $i++){
    $row = mysql_fetch_assoc($result);
    echo($row['a']);
}

Also do you want to fetch the associate array in the loop? this will execute every time as well?

Good Practice

$countRows = mysql_num_rows($result);

for($i=0; $i<$countRows; $i++){
    $row = mysql_fetch_assoc($result);
    echo "Row: ".$row['a']."<br />";
}

or

$row = mysql_fetch_assoc($result);

foreach($row as $key=>$value) {
   echo "Key: ".$key." Value: ".$value."<br />";
}

or

while($row = mysql_fetch_assoc($result))
{
   echo "Row: ".$row['a'] . "<br />";
}

Answer 4

I am going to answer the actual question ("Does UNION ALL return an array?").

No, union [all] doesn't return arrays, it is used to concatenate resultsets. If it is used in the union all form, it makes sure, that all the duplicate rows in the resulting resultset are preserved.

That's all there is to it.