Php pdo mssql query example

Skip to main content

This browser is no longer supported.

Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.

PDO::query

• Article
• 09/09/2022
• 2 minutes to read

In this article

Executes an SQL query and returns a result set as a PDOStatement object.

Syntax

  
PDOStatement PDO::query ($statement[, $fetch_style);  

Parameters

$statement: The SQL statement you want to execute.

$fetch_style: The optional instructions on how to perform the query. See the Remarks section for more details. $fetch_style in PDO::query can be overridden with $fetch_style in PDO::fetch.

Return Value

If the call succeeds, PDO::query returns a PDOStatement object. If the call fails, PDO::query throws a PDOException object or returns false, depending on the setting of PDO::ATTR_ERRMODE.

Exceptions

PDOException.

Remarks

A query executed with PDO::query can execute either a prepared statement or directly, depending on the setting of PDO::SQLSRV_ATTR_DIRECT_QUERY. For more information, see Direct Statement Execution and Prepared Statement Execution in the PDO_SQLSRV Driver.

PDO::SQLSRV_ATTR_QUERY_TIMEOUT also affects the behavior of PDO::exec; for more information, see PDO::setAttribute.

You can specify the following options for $fetch_style.

Call PDOStatement::closeCursor to release database resources associated with the PDOStatement object before calling PDO::query again.

You can close a PDOStatement object by setting it to null.

If all the data in a result set is not fetched, the next PDO::query call will not fail.

Support for PDO was added in version 2.0 of the Microsoft Drivers for PHP for SQL Server.

Query example

This example shows several queries.

setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION );  
$conn->setAttribute( PDO::SQLSRV_ATTR_QUERY_TIMEOUT, 1 );  
  
$query = 'select * from Person.ContactType';  
  
// simple query  
$stmt = $conn->query( $query );  
while ( $row = $stmt->fetch( PDO::FETCH_ASSOC ) ){  
   print_r( $row['Name'] ."\n" );  
}  
  
echo "\n........ query for a column ............\n";  
  
// query for one column  
$stmt = $conn->query( $query, PDO::FETCH_COLUMN, 1 );  
while ( $row = $stmt->fetch() ){  
   echo "$row\n";  
}  
  
echo "\n........ query with a new class ............\n";  
$query = 'select * from HumanResources.Department order by GroupName';  
// query with a class  
class cc {  
   function __construct( $arg ) {  
      echo "$arg";  
   }  
  
   function __toString() {  
      return $this->DepartmentID . "; " . $this->Name . "; " . $this->GroupName;  
   }  
}  
  
$stmt = $conn->query( $query, PDO::FETCH_CLASS, 'cc', array( "arg1 " ));  
  
while ( $row = $stmt->fetch() ){  
   echo "$row\n";  
}  
  
echo "\n........ query into an existing class ............\n";  
$c_obj = new cc( '' );  
$stmt = $conn->query( $query, PDO::FETCH_INTO, $c_obj );  
while ( $stmt->fetch() ){  
   echo "$c_obj\n";  
}  
  
$stmt = null;  
?>  

Sql_variant example

This code sample shows how to create a table of sql_variant types and fetch the inserted data.

setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION );  

try {
    $tableName = 'testTable';
    $query = "CREATE TABLE $tableName ([c1_int] sql_variant, [c2_varchar] sql_variant)";

    $stmt = $conn->query($query);
    unset($stmt);

    $query = "INSERT INTO [$tableName] (c1_int, c2_varchar) VALUES (1, 'test_data')";
    $stmt = $conn->query($query);
    unset($stmt);

    $query = "SELECT * FROM $tableName";
    $stmt = $conn->query($query);

    $result = $stmt->fetch(PDO::FETCH_ASSOC);
    print_r($result);
    
    unset($stmt);
    unset($conn);
} catch (Exception $e) {
    echo $e->getMessage();
}
?>

The expected output would be:

Array
(
    [c1_int] => 1
    [c2_varchar] => test_data
)

See Also

PDO Class

PDO

How do I run a PDO query?

What is PDO in PHP with example?

How fetch data from database in PHP and display PDO?

What function do you use to run a query using a PDO object?