从多维数组创建JSON输出

Question

I populate an array with the results of three SQL queries. Then I create a temporary table, insert all this data into it then query the table to be able to pass the result as a json array to my Java app (Android).

So.

$i=0;
try {
    $stmt = $conn->prepare("SELECT APPID FROM COMMENTROOM WHERE BADGEID=? GROUP BY APPID");
    $stmt->execute(array($badgeID));
        while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
            $row2[$i][0] = $row['APPID'];
            $i++;
        }
    } catch(PDOException $e) {
        echo 'ERROR: ' . $e->getMessage();
        $server_dir = $_SERVER['HTTP_HOST'] . rtrim(dirname($_SERVER['PHP_SELF']), '/\\');
        header('Location: http://' . $server_dir);
        exit();
}

for ($i=0; $i<count($row2); $i++)
{
    try {
        $stmt = $conn->prepare("SELECT APPNAME, MARKETLINK, FILENAME, USERID FROM TABLE_ADS WHERE ID=?");
        $stmt->execute(array($row2[$i][0]));
            while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
                $row2[$i][1] = $row['APPNAME'];
                $row2[$i][2] = $row['MARKETLINK'];
                $row2[$i][3] = $row['FILENAME'];
                $row2[$i][4] = $row['USERID'];
            }
        } catch(PDOException $e) {
            echo 'ERROR: ' . $e->getMessage();
            $server_dir = $_SERVER['HTTP_HOST'] . rtrim(dirname($_SERVER['PHP_SELF']), '/\\');
            header('Location: http://' . $server_dir);
        exit();
    }
}

for ($i=0; $i<count($row2); $i++)
{
    try {
        $stmt = $conn->prepare("SELECT BADGEID FROM REG_USERS WHERE ID=?");
        $stmt->execute(array($row2[$i][4]));
            while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
                $row2[$i][5] = $row['BADGEID'];
            }
        } catch(PDOException $e) {
            echo 'ERROR: ' . $e->getMessage();
            $server_dir = $_SERVER['HTTP_HOST'] . rtrim(dirname($_SERVER['PHP_SELF']), '/\\');
            header('Location: http://' . $server_dir);
            exit();
    }
}

Then finally after I inserted the data from the $row2 array into the temporary table I do

try {
    $stmt = $conn->prepare("SELECT * FROM TEMP_COMMENTLIST");
    $stmt->execute();
        while ($row = $stmt->fetchAll(PDO::FETCH_ASSOC)) {
            $output[] = $row;
        }
    } catch(PDOException $e) {
        echo 'ERROR: ' . $e->getMessage();
        $server_dir = $_SERVER['HTTP_HOST'] . rtrim(dirname($_SERVER['PHP_SELF']), '/\\');
        header('Location: http://' . $server_dir);
        exit();
    }
print(json_encode($output));

The output looks like

[[{"APPID":"0000000021","APPNAME":"Enhanced Email","MARKETLINK":"https:\/\/play.google.com\/store\/apps\/details?id=com.qs.enhancedemail","FILENAME":"00000000089_2013-10-23 13:26:38_Enhanced Email.png","USERID":"00000000089","BADGEID":"2626511"},{"APPID":"0000000037","APPNAME":"Mobile....

However I think there must be a better solution than messing with this temporary table thing. Is a there a way to create the SAME output from the $row2 array without any more sql queries?

SOLUTION: I mixed hotzu's and SoaperGEM's solution:

  $result = array();
try{
    $stmt = $conn->prepare("SELECT c.APPID, t.APPNAME, t.MARKETLINK, t.FILENAME, t.USERID, c.BADGEID FROM COMMENTROOM c
    INNER JOIN TABLE_ADS t ON t.ID = c.APPID 
    INNER JOIN REG_USERS r ON r.ID = t.USERID 
    WHERE c.BADGEID = ? 
    GROUP BY c.APPID");
    $stmt->execute(array($badgeID));
    while ($row = $stmt->fetch(PDO::FETCH_OBJ)) {
        $result[] = $row;
    }
} catch(PDOException $e) {
    echo 'ERROR: ' . $e->getMessage();
    $server_dir = $_SERVER['HTTP_HOST'] . rtrim(dirname($_SERVER['PHP_SELF']), '/\\');
    header('Location: http://' . $server_dir);
    exit();
}


echo "[".json_encode($result)."]";
exit();

Answer 1

EDITED

Try this:

$result = array();
try{
    $stmt = $conn->prepare("SELECT c.APPID, t.APPNAME, t.MARKETLINK, t.FILENAME, t.USERID, r.BADGEID FROM COMMENTROOM c
    LEFT JOIN TABLE_ADS t ON t.ID = c.APPID 
    LEFT JOIN REG_USERS r ON r.ID = t.USERID 
    WHERE c.APPID = ? 
    GROUP BY c.APPID";
    $stmt->execute(array($badgeID));
    while ($row = $stmt->fetch(PDO::FETCH_OBJ)) {
        $result[] = $row;
    }
} catch(PDOException $e) {
    echo 'ERROR: ' . $e->getMessage();
}

echo json_encode($result);