How to fetch data from a MS-SQL Server Database using PHP?

Question

I'm trying to fetch data from a SQL Server database. After debugging, I could figure there's something wrong with the function.

Here's the code:

db.php

class Db{

    public static function getConnection() {
        $server='xxx';
        $database='xxx';
        $user='xxx';
        $password='xxx';
        $dsn="dblib:host=" . $server . ";dbname=" . $database;

        try {
            $conn = new PDO($dsn, $user, $password); 
        } 
        catch (PDOException $e) {
            echo 'SQL SERVER CONNECTION ERROR: ' . $e->getMessage();
        }
        return $conn;
    }
}

functions.php

class Functions {   
    function getAcademicYear() {
        $db = new Db();
        $conn = $db->getConnection();   
        $conn->beginTransaction();
        $sql = "SELECT academicYear FROM AcademicYear ORDER BY academicYearId DESC LIMIT 5";
        $stmt = $conn->prepare($sql);                   
        if ($stmt->execute()) {
            $result = $stmt->fetchAll(PDO::FETCH_ASSOC);
            $conn->commit();
            echo $result;
        } else {
            $conn->rollback();
            echo "false";
        } 
    }

}

The function is returning false. Can you please review it and point out any mistakes?

You say you're getting an error, however, you haven't told us what said error is. — Thom A, Oct 18 '19 at 15:24
The error log is empty. The if statement is returning false so maybe there’s a problem there — AAM, Oct 18 '19 at 15:27
On a different note, there are official PHP drivers for SQL Server by Microsoft: [Microsoft Drivers for PHP for SQL Server](https://learn.microsoft.com/en-us/sql/connect/php/microsoft-php-driver-for-sql-server?view=sql-server-ver15) which you don't appear to be using here — Thom A, Oct 18 '19 at 15:27
That is because you have an antipattern in your code here that I call try/squelch. When your execution fails it returns false. You catch that false and return false. In essence you swallowed the error message that would help you here. Which would be something like "Incorrect syntax near 'LIMIT' because that is not valid t-sql. — Sean Lange, Oct 18 '19 at 15:33
@SeanLange That was it! Removed the limit part and it started working. Thank you so much!! Please post it as an answer — AAM, Oct 18 '19 at 15:53
But that isn't really the answer. You need to get rid of that return false when there is an error. You need to let errors happen and deal with them appropriately. Hiding them is a very bad practice and will drive you insane looking for issues like this one. — Sean Lange, Oct 18 '19 at 15:55
I understand, just removing the part of the code isn’t the solution, but for this question that was the answer. I would appreciate if you could suggest anything further — AAM, Oct 18 '19 at 15:58
The LIMIT keyword is only valid in MySQL, not Microsoft SQL Server. In SQL Server the closest equivalent is TOP (you can look up the syntax). And Sean is right, while you may not have a problem any more in this bit of code, you should still remove the bit he is talking about - that way any other unexpected errors will not be hidden from you. Instead you should should use a try/catch to catch exceptions - that's fine, but then you need to at least log them somewhere instead, so you can debug the issue afterwards. Right now your code doesn't even _try_ to look for the problem. — ADyson, Oct 22 '19 at 13:47
P.S. [this](https://stackoverflow.com/questions/8776344/how-to-view-query-error-in-pdo-php) and [this](https://www.php.net/manual/en/function.set-error-handler.php) will help you on the way to developing better error handling strategies for your database code and for your other code in general. — ADyson, Oct 22 '19 at 13:51

How to fetch data from a MS-SQL Server Database using PHP?

0 Answers0