How to select table with MSSQL database using PHP
by admin | Apr
29, 2021 | Continue
<?php
$serverName =
"(local)";
$connectionInfo = array(
"Database"=>"hotels");
$conn = sqlsrv_connect( $serverName, $connectionInfo );
if( $conn === false )
{
die( print_r( sqlsrv_errors(), true));
}
$sql = "SELECT Name, Place FROM Hotel";
$stmt = sqlsrv_query( $conn, $sql );
if( $stmt === false)
{
die( print_r( sqlsrv_errors(), true) );
}
while( $row = sqlsrv_fetch_array( $stmt, SQLSRV_FETCH_NUMERIC) )
{
echo $row[0].", ".$row[1]."
";
}
sqlsrv_free_stmt( $stmt);
?>
In this article, we will learn different methods that are used to update the data in a table with the data of other tables. The update from select query structure is the main technique for performing these updates.
An UPDATE query is used to change an existing row or rows in the database. UPDATE queries can change all tables’ rows, or we can limit the update statement affects for certain rows with the help of the WHERE clause. Mostly, we use constant values to change the data, such as the following structures.
The full update statement is used to change the whole table data with the same value.
The conditional update statement is used to change the data that satisfies the WHERE condition.
Preparing the sample data
With the help of the following query, we will create Persons and AddressList tables and populate them with some synthetic data. These two tables have a relationship through the PersonId column, meaning that, in these two tables, the PersonId column value represents the same person.
CREATE TABLE
dbo.Persons(
PersonId
INT
PRIMARY
KEY IDENTITY(1,
1)
NOT NULL,
PersonName
VARCHAR(100)
NULL,
PersonLastName
VARCHAR(100)
NULL,
PersonPostCode
VARCHAR(100)
NULL,
PersonCityName
VARCHAR(100)
NULL)
GO
CREATE TABLE AddressList(
[AddressId]
[int] PRIMARY
KEY IDENTITY(1,1)
NOT NULL,
[PersonId]
[int]
NULL,
[PostCode]
[varchar](100)
NULL,
[City]
[varchar](100)
NULL)
GO
INSERT INTO
Persons
(PersonName,
PersonLastName )
VALUES
(N'Salvador',
N'Williams'),
(N'Lawrence',
N'Brown'),
( N'Gilbert',
N'Jones'),
( N'Ernest',
N'Smith'),
( N'Jorge',
N'Johnson')
GO
INSERT INTO
AddressList
(PersonId,
PostCode,
City)
VALUES
(1,
N'07145',
N'Philadelphia'),
(2,
N'68443',
N'New York'),
(3,
N'50675',
N'Phoenix'),
(4,
N'96573',
N'Chicago')
SELECT *
FROM Persons
SELECT *
FROM AddressList
UPDATE from SELECT: Join Method
In this method, the table to be updated will be joined with the reference (secondary) table that contains new row values. So that, we can access the matched data of the reference table based on the specified join type. Lastly, the columns to be updated can be matched with referenced columns and the update process changes these column values.
In the following example, we will update the PersonCityName and PersonPostCode columns data with the City and PostCode columns data of the AdressList table.
