Importing and Querying a MSSQL database in SQL Server locally with PHP and XAMPP on Windows 10

Prerequesit installs

1. Established a new htdocs directory and host in Xampp and added a new host in local host config
2. Installed SSMS 
3. Installed SQL Server 2019 config manager 

Installing PHP Extensions

Reference Tutorial

1. Downloaded and added PDO extension for PHP on Windows ( Click Download Microsoft Drivers for PHP for SQL Server (Windows))
   In my case this meant extracting and moving the .dll to

   C:\xampp\php\ext

   I used php_pdo_sqlsrv_73_nts_x64.dll, 73 being the PHP version (found in phpinfo()) x64 being the architechture

2. edit the php.ini in

   C:\xampp\php\php.ini

   , checked this was the only ini config loaded
   

3. Restart Apache
4. Checked SQLSRV was present in phpinfo()
   

Importing MSSQL DB .bak

1. Run SSMS as administrator
2. In object explorer choose Connect > Database Engine, choose your local server probably COMPUTERNAME\SQLEXPRESS and in Authentication choose Windows Authentication
3. Follow instructions on how to import the .bak
4. You might start running into permissions issues here preventing you from importing, this is because the Windows Auth user might not have the correct access rights.

Getting Acess to DB

Reference Tutorial

1. Run SQL Server 2019 config manager  as Administrator
2. In SQL Server Services right click your SQL Server instance and click Properties in Startup Parameters add “-m” and click Add, Apply, Ok,
3. Right click the SQL Server instance again and restart it
   
4. Restart SSMS (run as administrator) and connect as your Windows user again
5. Now you are in single admin mode and can do more!
6. In Object Explorer open folders Security > Logins
7. Right Click your own user and in Server Roles, go wild choose what you like
8. This gives you permission to create additional logins. Create a new login, using SQL Server Authentication, set the Server Roles etc as appropriate
   Reference Tutorial
9. Now Repeat step 2 except remove the -m
10. Restart everything, Services, SSMS etc

Connecting with PHP PDO and writing first query

Using PHP:sqlsrv_connect threw errors

Call to undefined function sqlsrv_connect()

So I used PDO Object instead  Reference Tutorial

<?php
$db = new PDO("sqlsrv:Server=COMPUTERNAME\SQLEXPRESS;Database=DBNAME", "USERNAME", "PASSWORD");
$db->setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION );
$db->setAttribute( PDO::SQLSRV_ATTR_QUERY_TIMEOUT, 1 );
?>

and begin with the first query “list all table names in the database”

$query = "SELECT * FROM SYSOBJECTS WHERE xtype = 'U'";
$test = $db->query( $query );
while ( $row = $test->fetch( PDO::FETCH_ASSOC ) ){
print_r( $row['name'] ."\n" );
}