sql-server-samples/samples/tutorials/php/Ubuntu/SqlServerSample/crud.php at master · microsoft/sql-server-samples · GitHub

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
<?php
$serverName = 'localhost';
$connectionOptions = [
    'Database' => 'SampleDB',
    'Uid' => 'sa',
    'PWD' => 'your_password',
];
// Establishes the connection
$conn = sqlsrv_connect($serverName, $connectionOptions);

// Insert Query
echo('Inserting a new row into table' . PHP_EOL);
$tsql = 'INSERT INTO TestSchema.Employees (Name, Location) VALUES (?,?);';
$params = ['Jake', 'United States'];
$getResults = sqlsrv_query($conn, $tsql, $params);
$rowsAffected = sqlsrv_rows_affected($getResults);
if ($getResults === false || $rowsAffected === false) {
    format_errors(sqlsrv_errors());
    die();
}
echo($rowsAffected . ' row(s) inserted: ' . PHP_EOL);

sqlsrv_free_stmt($getResults);

// Update Query

$userToUpdate = 'Nikita';
$tsql = 'UPDATE TestSchema.Employees SET Location = ? WHERE Name = ?';
$params = ['Sweden', $userToUpdate];
echo('Updating Location for user ' . $userToUpdate . PHP_EOL);

$getResults = sqlsrv_query($conn, $tsql, $params);
$rowsAffected = sqlsrv_rows_affected($getResults);
if ($getResults === false || $rowsAffected === false) {
    format_errors(sqlsrv_errors());
    die();
}
echo($rowsAffected . ' row(s) updated: ' . PHP_EOL);
sqlsrv_free_stmt($getResults);

// Delete Query
$userToDelete = 'Jared';
$tsql = 'DELETE FROM TestSchema.Employees WHERE Name = ?';
$params = [$userToDelete];
$getResults = sqlsrv_query($conn, $tsql, $params);
echo('Deleting user ' . $userToDelete . PHP_EOL);
$rowsAffected = sqlsrv_rows_affected($getResults);
if ($getResults === false || $rowsAffected === false) {
    format_errors(sqlsrv_errors());
    die();
}
echo($rowsAffected . ' row(s) deleted: ' . PHP_EOL);
sqlsrv_free_stmt($getResults);

// Read Query
$tsql = 'SELECT Id, Name, Location FROM TestSchema.Employees;';
$getResults = sqlsrv_query($conn, $tsql);
echo('Reading data from table' . PHP_EOL);
if ($getResults === false) {
    format_errors(sqlsrv_errors());
    die();
}
while ($row = sqlsrv_fetch_array($getResults, SQLSRV_FETCH_ASSOC)) {
    echo($row['Id'] . ' ' . $row['Name'] . ' ' . $row['Location'] . PHP_EOL);
}
sqlsrv_free_stmt($getResults);

function format_errors($errors)
{
    /* Display errors. */
    echo 'Error information: ';

    foreach ($errors as $error) {
        echo 'SQLSTATE: ' . $error['SQLSTATE'] . '';
        echo 'Code: ' . $error['code'] . '';
        echo 'Message: ' . $error['message'] . '';
    }
}