View Full Version : SQL UNION clause

07-05-2007, 12:37 PM
Anything wrong with this code?

$sql = "SELECT * FROM decipleInfo WHERE decipleName='".$q."' UNION SELECT * FROM deciplezonedetail WHERE decipleName='".$q."'";

$result = mysql_query($sql);

while($row = mysql_fetch_array($result))
echo "<tr>";
echo "<td>" . $row['decipleInfo.Name'] . "</td>";
echo "<td>" . $row['decipleInfo.userid'] . "</td>";
echo "<td>" . $row['deciplezonedetail.email'] . "</td>";
echo "<td>" . $row['deciplezonedetail.zoneName'] . "</td>";
echo "</tr>";
echo "</table>";

I got this Error..

Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in C:\Inetpub\wwwroot\cyyamhub\library\getPageInfo.php on line 27

07-05-2007, 02:08 PM
A union between 2 tables requires them to have the same number of columns, and to generally be similar (I'm not sure what mysql does if you try to union a text-column with, for example, a timestamp).

But regardless, from your code, it doesn't look like you actually want to be doing a union, but a JOIN instead.

Without the structure of your tables I'm guessing, but

SELECT i.*, z.* FROM discipleinfo i JOIN disciplezonedetail z ON (i.disciple_id=z.disciple_id) WHERE i.disciplename=\''.mysql_real_escape_string.'\'';

should point you in the right direction.

As far as the output goes, table prefixes don't get used as array indices, so $row['name'] rather than $row['i.name'] (with the aliased tables in my query).