访问子查询的一部分，以便在PHP服务器中构建HTML - accessing parts of sub-queries to build HTML in a PHP server

accessing parts of sub-queries to build HTML in a PHP server

我有一个查询，它返回活动和非活动帖子的计数，以及特定的帖子详细信息。下面是查询

$fetch = mysql_query("SELECT SQL_CALC_FOUND_ROWS * FROM
(SELECT DISTINCT propertyID, streetAddress, city FROM residence.property
WHERE residence.contact.contactEmailAddress1 ='$contactEmailAddress1' AND activePosting = '0') inactposts,
    (SELECT FOUND_ROWS() AS 'inactiveCount') inact,
(SELECT DISTINCT propertyID, streetAddress, city FROM residence.property
INNER JOIN contact ON residence.contact.ContactID = residence.property.ContactID
WHERE residence.contact.contactEmailAddress1 ='$contactEmailAddress1' AND activePosting = '1') actposts,
    (SELECT FOUND_ROWS() AS 'activeCount') act;")
or die('<li class=error>Ooops</li>'.mysql_error());

查询工作在我的网页。在MySQL Workbench中，它返回如下内容:

propID   address        city        inactCount  propID  address     city        actCount
366   110 Main Street   Perinton        1       352    3 Some Place Perinton        3
366   110 Main Street   Perinton        1       353    5 Joe Place  Perinton        3       
366   110 Main Street   Perinton        1       356    111 Main     Perinton        3

对我来说，问题是子查询结果的2部分没有标识符(我可以找到)，所以在PHP服务器构建的HTML中，我不能将列表分为非活动帖子(inactposts)和活动帖子(actposts)。目前HTML 只有返回活动帖子列表。我试过把"inactposts"，临时表的名称，在不同的地方，我只是得到错误消息

PHP正在构建HTML:

if( mysql_num_rows($fetch) ) {
while ($row = mysql_fetch_array($fetch)) {
    $inactiveCount = $row["inactiveCount"];
    $activeCount = $row["activeCount"];
    $storedStreetAddress = $row["streetAddress"];
    $storedCity3 = $row['city3'];
    $error_NumberInactives = "<ul>You have $activeCount active posts and $inactiveCount inactive posts.</ul>";
    $error_List_Actives = "<li>Your $activeCount active posts are:</li>";
    $errorMessages[0] = $error_NumberInactives;
    $errorMessages[1] = $error_List_Actives;
    $errorMessages[] = "<li> $storedStreetAddress, $storedCity3 </li>";
}else{ //error message}

如何从该查询中构建子查询结果的单独列表?还是我需要一个不同的查询?

如果需要单独的行，则需要另一个不带聚合的查询。或者，在一个查询中选择所有帖子(包含详细信息)，然后在php中解析结果，以计算活动和非活动的数量:

SELECT DISTINCT propertyID, streetAddress, city, activePosting FROM residence.property
INNER JOIN contact ON residence.contact.ContactID = residence.property.ContactID
WHERE residence.contact.contactEmailAddress1 ='$contactEmailAddress1'

注。我假设您已经在提供的代码之外清理了输入，否则您需要这样做。