Returning multiple SELECT queries in single array

Question

I am writing angularjs application that relies on data returned from MySQL database.

Angularjs code makes a HTTP request and logs the returned data to the console:

$http({method: 'POST', url: 'php/return_something.php', headers: {'Content-Type': 'application/json'}})
    .success(function(data, status, headers, config) {
    //The API call to the back-end was successful (i.e. a valid session)
        console.log(data);
    })
    .error(function(data, status, headers, config) {
        console.log("Error.");
    });

return_something.php

try
{
    $conn = new PDO("mysql:host=".DB_HOST."; dbname=".DB_DATABASE, DB_USER, DB_PASSWORD);
    // set the PDO error mode to exception
    $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    $sql = $conn->prepare("SELECT count(id_table_x) from table_x; SELECT count(id_table_y) from table_y;");
    $sql->execute();

    // set the resulting array to associative
    $results = $sql->fetchAll(PDO::FETCH_ASSOC);
    echo json_encode($results);
}

catch(PDOException $e)
{
    echo $sql . "<br />" . $e->getMessage();
}

$conn = null;

To the console an array with one element gets logged.

[Object]

Inside this object there is a result of the first query ("SELECT count(id_table_x) from table_x").

What should I change that I would see the result of the second query ("SELECT count(id_table_y) from table_y") as the second element of this array? Any help appreciated.

Answer 1

You could write two queries, which is also more readable (imho):

$sql = $conn->prepare("SELECT count(id_table_x) from table_x;");
$sql->execute();

$sql2 = $conn->prepare("SELECT count(id_table_y) from table_y;");
$sql2->execute();

$results[] = $sql->fetchAll(PDO::FETCH_ASSOC);
$results[] = $sql2->fetchAll(PDO::FETCH_ASSOC);

EDIT: $results[] = is a shortcut for $results.push(). Before, you have to define $results an an array:

$results = array();