PHP PDO sqlsrv executing multiple SELECT COUNT

Question

I'm at the very beginning of my PDO learning curve and soaking up all I can learn. Please can you advise if the code below is the best approach. I want to find if a user has an entry in more than one SQL Server table

try {
    $conn = new PDO("sqlsrv:Server=$pdoServer;Database=$pdoDatabase;", $pdoUID, $pdoPWD);
    $conn->setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_WARNING );  
} catch (PDOException $e) {
    die(error_log(print_r("Could not connect to SQL Server".$e->getMessage(), TRUE), 0));
}

if (isValidUsername($username)) {
    $res_sites = $res_PR = $res_TA = array();

    try {
        $stmt_PR = $conn->prepare("SELECT Count(*) AS PRcounter FROM tbl1 WHERE usr_username=:username");
        $stmt_TA = $conn->prepare("SELECT Count(*) AS TAcounter FROM tbl2 WHERE usr_username=:username");

        $stmt_PR->bindParam(':username',$username,PDO::PARAM_STR);
        $stmt_TA->bindParam(':username',$username,PDO::PARAM_STR);

        $stmt_PR->execute();
        $stmt_TA->execute();

        $res_PR = $stmt_PR->fetch();
        $res_TA = $stmt_TA->fetch();

        $stmt_sites = $conn2->query("SELECT site_ID,site_name FROM tbl3 WHERE isDeleted IS NULL ORDER BY site_name ASC");
        while ($row = $stmt_sites->fetch(PDO::FETCH_ASSOC)){
            $res_sites[$row['site_ID']]['site_name']=$row['site_name'];
        }
    } catch (PDOException $e) {
        echo "Error occurred. Error message: ". $e->getMessage();
    }

    /* do more stuff... */
}

As expected, the output returned is

$res_PR: array(2) {["PRcounter"]=>string(1) "0" [0]=>string(1) "0"}
$res_TA: array(2) {["TAcounter"]=>string(1) "0" [0]=>string(1) "0"}
$res_sites: array(211) {[0]=>array(3) {["site_ID"]=>string(1) "1" ["site_name"]=>...}

This is one of several sections of code I'm working on. I will be pulling a row back from 7 tables when the page loads. Is the above fine, or can the multiple statements be prepared and then executed in a single execute() statement?

Many thanks.

Answer 1

can the multiple statements be prepared and then executed in a single execute() statement?

No.

Even if it was possible, it wouldn't solve none of your problems, because they are irrelevant to PDO, and belongs to general database knowledge, such as table structure and optimization. Say, if you put all your counters in one table, you'll surely be able to get them in one query. And if you create an index for username, it will speed things up.