PDO PHP select distinct query not working for mssql

Question

I've got a website that is pulling data from my MSSQL Server. I am using functions to build tables for reports. Here's what I've got:

function BeginTable($rowCount,$headings,$searchValue,$ReportName,$OneButton,$NewSearch)
{
    try{
    $StateSelectSQL = "select distinct State from pmdb.MaterialTracking where State is not null";
    var_dump($StateSelectSQL);echo " What!<br>";

    $getSelect = $conn->query($StateSelectSQL);
    var_dump($getSelect);echo " When!<br>";

    $StateSelectNames = $getSelect->fetchALL(PDO::FETCH_ASSOC);
    var_dump($StateSelectNames);echo " Where!<br>";
    }
    catch(Exception $e)
    {
        echo "Something went wrong";
        die(print_r($e->getMessage()));
    }

I tried this too:

    try{
        $StateSelectSQL = "select distinct State from pmdb.MaterialTracking where State is not null";
        var_dump($StateSelectSQL);echo " What!<br>";

         $getSelect = $conn->prepare($StateSelectSQL);
         $getSelect->execute();

        //$getSelect = $conn->query($StateSelectSQL);
        //var_dump($getSelect);echo " When!<br>";

        $StateSelectNames = $getSelect->fetchALL(PDO::FETCH_ASSOC);
        var_dump($StateSelectNames);echo " Where!<br>";
    }
    catch(Exception $e)
    {
        echo "Something went wrong<br>";
        die( print_r( $e->getMessage()));
    }

The second and third var_dump's never show anything and the rest of the code (not shown here) doesn't get run. If I comment out the $getSelect and $StateSelectNames lines (with the var_dump's under them) then everything else works.

Here is my DBConn.php file that is included above the Function:

    $conn = new PDO("sqlsrv:server=$servername;database=$dbname", $username,$password);
    //set the PDO error mode to exception
    $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    $conn->setAttribute(PDO::SQLSRV_ATTR_QUERY_TIMEOUT, 10);

What is wrong with the line $getSelect = $conn->query($StateSelectSQL); I can't figure it out. I tried using it later in my foreach like this:

foreach($conn->query($StateSelectSQL) as $StateName)

But that doesn't work either. It again stops at this line and doesn't go any further. The only thing I can think of is that my SQL is messed up, but when I run it in SSMS it works fine!

What's going on?

score 1 · Answer 1 · edited May 23 '17 at 12:14

Try preparing and executing your SQL before using fetchAll. Also consider enabling exception mode if you haven't already and wrapping your statement in a try catch - this should flag any issues (e.g. your applications database user not having permission to access the schema, or syntax error etc)

Exceptions:

See this stack overflow post for info about how to enable

And for your code:

try { 
    $sql = "
    SELECT DISTINCT State
      FROM pmdb.MaterialTracking
     WHERE State IS NOT NULL
    ";

    $sth = $conn->prepare($sql);
    $sth->execute();

    $rowset = $sth->fetchAll(PDO::FETCH_ASSOC);
    print_r($rowset);

} catch PDOException($err) {
    echo "Something went wrong".
    echo $err;
}

I added the try catch around it (see above), but still only gets to the first `var_dump` then nothing after that! It doesn't throw an error. — Mike, Jul 21 '16 at 17:27

score 0 · Accepted Answer · answered Jul 21 '16 at 19:45

I have figured it out after pulling my hair out all day! I had to include my DBConn.php inside the function. After this it worked. I don't know why that mattered since it is included at the beginning of the file. If there is anyone who can explain why that is i'd be grateful!

It now looks like this:

function BeginTable($rowCount,$headings,$searchValue,$ReportName,$OneButton,$NewSearch)
{
    try{
        include("DBConn.php");
        $SelectSQL = "select distinct State from pmdb.MaterialTracking where State is not null order by State";            

        $getSelect = $conn->prepare($SelectSQL);

        $getSelect->execute();

        $StateSelectNames = $getSelect->fetchALL(PDO::FETCH_ASSOC);

        }
        catch(Exception $e)
        {
            echo "Something went wrong<br>";
            die( print_r( $e->getMessage()));
        }

PDO PHP select distinct query not working for mssql

2 Answers2