...

View Full Version : SQL UNION clause



ooiyh
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

GJay
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).



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum