How to modify MSSQL 2008 database in PHP

If you are using PHP to manage a Microsoft SQL Server 2008 database, then you may need to modify something in the database at some point. This might be adding, editing, or deleting data, or modifying the database structure. Whatever your needs are, the combination of PHP and MSSQL 2008 database allows you to accomplish these tasks with ease.

In this article, we will explore how to modify MSSQL 2008 database in PHP. We'll discuss some common techniques and tools that allow you to easily modify your database and ensure that your operations are safe and efficient.

Connecting to the database

Before modifying the MSSQL 2008 database in PHP, you need to make sure you have a connection to the database. The connection can be done using the SQLSRV or PDO SQLSRV driver.

Use the SQLSRV driver to connect to the database:

$serverName = "localhost";    //服务器名称
$databaseName = "MyDB";       //数据库名称
$connectionOptions = array("UID"=>"UserName", "PWD"=>"Password");
$conn = sqlsrv_connect($serverName, $connectionOptions);

Use the PDO SQLSRV driver to connect to the database:

$serverName = "localhost";    //服务器名称
$databaseName = "MyDB";       //数据库名称
$uid = "UserName";  
$pwd = "Password";  
try {  
    $conn = new PDO("sqlsrv:server=$serverName ; Database = $databaseName", $uid, $pwd);  
}  
catch(PDOException $e) {  
    echo $e->getMessage();  
}

Add data

Add data in the MSSQL 2008 database very simple. Just write a SQL INSERT statement and use sqlsrv_query() or the PDO prepare() and execute() functions to insert data into the database.

Use sqlsrv_query() to insert data:

$firstName = "John";
$lastName = "Doe";
$age = 30;
$sql = "INSERT INTO Persons (FirstName, LastName, Age) VALUES ('$firstName', '$lastName', $age)";
$query = sqlsrv_query($conn, $sql);
if ($query === false) {
    die(print_r(sqlsrv_errors(), true));
}

Use PDO prepare() and execute() functions to insert data:

$firstName = "John";
$lastName = "Doe";
$age = 30;
$sql = "INSERT INTO Persons (FirstName, LastName, Age) VALUES (:firstName, :lastName, :age)";
$query = $conn->prepare($sql);
$query->bindParam(':firstName', $firstName);
$query->bindParam(':lastName', $lastName);
$query->bindParam(':age', $age);
$query->execute();

Edit data

To edit For data in MSSQL 2008 database, you need to write SQL UPDATE statements and use sqlsrv_query() or PDO prepare() and execute() functions to update.

Use sqlsrv_query() to update data:

$personID = 1;
$age = 40;
$sql = "UPDATE Persons SET Age = $age WHERE PersonID = $personID";
$query = sqlsrv_query($conn, $sql);
if ($query === false) {
    die(print_r(sqlsrv_errors(), true));
}

Use PDO prepare() and execute() functions to update data:

$personID = 1;
$age = 40;
$sql = "UPDATE Persons SET Age = :age WHERE PersonID = :personID";
$query = $conn->prepare($sql);
$query->bindParam(':age', $age);
$query->bindParam(':personID', $personID);
$query->execute();

Delete data

To delete For data in the MSSQL 2008 database, you need to write a SQL DELETE statement and use the sqlsrv_query() or PDO prepare() and execute() functions to delete it.

Use sqlsrv_query() to delete data:

$personID = 1;
$sql = "DELETE FROM Persons WHERE PersonID = $personID";
$query = sqlsrv_query($conn, $sql);
if ($query === false) {
    die(print_r(sqlsrv_errors(), true));
}

Use PDO prepare() and execute() functions to delete data:

$personID = 1;
$sql = "DELETE FROM Persons WHERE PersonID = :personID";
$query = $conn->prepare($sql);
$query->bindParam(':personID', $personID);
$query->execute();

Modify the database structure

To To modify the structure of an MSSQL 2008 database, you need to use the SQL ALTER statement. The ALTER statement allows you to add, delete, or change tables, columns, indexes, etc. Use sqlsrv_query() or the PDO prepare() and execute() functions to execute ALTER statements.

Use sqlsrv_query() to modify the database structure:

$sql = "ALTER TABLE Persons ADD Email VARCHAR(255)";
$query = sqlsrv_query($conn, $sql);
if ($query === false) {
    die(print_r(sqlsrv_errors(), true));
}

Use PDO prepare() and execute() functions to modify the database structure:

$sql = "ALTER TABLE Persons ADD Email VARCHAR(255)";
$query = $conn->prepare($sql);
$query->execute();

Summary

In Modifying MSSQL 2008 database in PHP is an important task. Connect to the database using the SQLSRV or PDO SQLSRV driver, and then use SQL INSERT, UPDATE, DELETE, and ALTER statements to add, edit, delete, and modify database structures. Make sure your operations are safe and efficient, and always back up your data. Good luck!

The above is the detailed content of How to modify MSSQL 2008 database in PHP. For more information, please follow other related articles on the PHP Chinese website!