显示联合选择语句的结果时出错 - trouble displaying results of union select statement

trouble displaying results of union select statement

以下查询有效，但由于某些原因，第一个select语句是唯一显示的URL。会显示其他表中的项目，但是它们的URL是错误的。

$sql = "(SELECT postsID as postsID, postsSubject AS postsSubject, postsTimestamp AS timestamp
                FROM   posts
                WHERE  postsCategory = '$id')
            UNION 
                (SELECT eventID as eventID, eventTitle as eventTitle, eventsTimestamp as timestamp
                FROM   events
                WHERE  eventLocation = '$id')
            ORDER BY timestamp DESC";

事件和帖子表中的信息都正确显示，但结果似乎仅来自帖子表。

例如，假设我有以下信息

postsID |  postsSubject |  postsTimestamp
  1            post             123
eventID |  eventTitle   | eventsTimestamp
  2           event            456

我有以下显示我的结果

   while($row = mysql_fetch_assoc($result)){
    ?>
        <tr><td><? echo '<a href="viewevent.php?eventID=' . $row['eventID'] . '">' . $row['eventTitle'] . '</a>' ; ?></td>
        <tr><td><? echo '<a href="viewpost.php?postID=' . $row['postsID'] . '">' . $row['postsSubject'] . '</a>' ; ?></td>
    <?  
        if(preg_match('/[0-9]/',$row['timestamp'])){        
            list($yyyy,$dd,$mm) = explode('-',$row['timestamp']);
                    $newDate = $dd."-".$mm."-".$yyyy;
            }
        ?>
            <td><center><? echo $newDate; ?></center></td></tr>
        <?
        }
        echo '</table>';
}

输出似乎是正确的

post   123
event  456

然而，这两个结果都链接到以下（分别）

viewpost.php?id = 1
viewpost.php?id = 2  //this should be viewevent.php

sql如下：

$sql = "(SELECT postsID as ids, postsSubject AS description, postsTimestamp AS timestamp,'p' as status
                FROM   posts
                WHERE  postsCategory = '$id')
            UNION 
                (SELECT eventID as ids, eventTitle as description, eventsTimestamp as timestamp, 'e' as status
                FROM   events
                WHERE  eventLocation = '$id')
            ORDER BY timestamp DESC";

检索数据时，

while($row = mysql_fetch_assoc($result)){
     if ($row['status']=="e"){
    ?>
        <tr><td><? echo '<a href="viewevent.php?eventID=' . $row['ids'] . '">' . $row['description'] . '</a>' ; ?></td>
<? }else{?>
        <tr><td><? echo '<a href="viewpost.php?postID=' . $row['ids'] . '">' . $row['description'] . '</a>' ; ?></td>
<? } ?>
    <?  
        if(preg_match('/[0-9]/',$row['timestamp'])){        
            list($yyyy,$dd,$mm) = explode('-',$row['timestamp']);
                    $newDate = $dd."-".$mm."-".$yyyy;
            }
        ?>
            <td><center><? echo $newDate; ?></center></td></tr>
        <?
        }
        echo '</table>';
}

联合/联合使用第一个表中的列，将行聚集在一个表中。因此，结果集有PostsId，但没有EventsId。

也许您想要一个连接，它将把列并排放置。也许您希望将其作为两个单独的查询运行。

以下是示例：

$sql = "select *
        from (SELECT postsID as postsID, postsSubject AS postsSubject, postsTimestamp AS timestamp
                FROM   posts
                WHERE  postsCategory = '$id') p
            cross join
                (SELECT eventID as eventID, eventTitle as eventTitle, eventsTimestamp as timestamp
                FROM   events
                WHERE  eventLocation = '$id') e
            ORDER BY postsTimeStamp DESC"

请注意，这是在生产笛卡尔产品。因此，如果其中一个表中有更多的行，那么您将得到大量的行。