What are some best practices for handling database connections and queries in PHP when working with MS-SQL databases?

When working with MS-SQL databases in PHP, it is important to properly handle database connections and queries to ensure efficiency and security. One best practice is to use parameterized queries to prevent SQL injection attacks. Additionally, it is recommended to establish a separate database connection function to avoid repetitive code and to properly close connections after use to free up resources. 

// Establishing a database connection function
function connectToDatabase() {
    $serverName = "your_server_name";
    $connectionOptions = array(
        "Database" => "your_database_name",
        "Uid" => "your_username",
        "PWD" => "your_password"
    );
    $conn = sqlsrv_connect($serverName, $connectionOptions);
    
    if (!$conn) {
        die(print_r(sqlsrv_errors(), true));
    }
    
    return $conn;
}

// Example of using parameterized query
$conn = connectToDatabase();
$sql = "SELECT * FROM your_table WHERE id = ?";
$params = array(1);
$stmt = sqlsrv_query($conn, $sql, $params);

if ($stmt === false) {
    die(print_r(sqlsrv_errors(), true));
}

while ($row = sqlsrv_fetch_array($stmt, SQLSRV_FETCH_ASSOC)) {
    echo $row['column_name'] . "<br />";
}

sqlsrv_free_stmt($stmt);
sqlsrv_close($conn);

Keywords

PHP MS-SQL database connections queries error handling

Related Questions