Build a SQL Server and PHP Environment


By:   |   Updated: 2014-12-26   |   Comments (1)   |   Related: More > Application Development

Problem

You are an experienced SQL Server DBA and you wish to use PHP to provide some front end web pages to help with monitoring and management.  How do you setup PHP and SQL Server?  What are the steps? What needs to be installed?  Can you provide any checklists?

Solution

Here are some tips on building a development environment for PHP and SQL Server. Using my checklist will help you cover each point.

Step 1: Reboot Server/PC/Laptop

Install all Windows Updates and reboot your server. Backup anything important just in case.

Step 2: Install SQL Server

Get SQL Server up and running on your machine. In my case I used my basic desktop that already had SQL Server 2012 Express running.

Here are SQL Server installation related tips:

Here are SQL Server Express Edition related tips:

Step 3: Install PHP

If you are installing PHP on a Windows desktop system use this guide: Microsoft Web Platform Installer.

"The Microsoft Web Platform Installer (Web PI) is a free tool that makes getting the latest components of the Microsoft Web Platform, including Internet Information Services (IIS), SQL Server Express, .NET Framework and Visual Web Developer easy. The Web PI also makes it easy to install and run the most popular free web applications for blogging, content management and more with the built-in Windows Web Application Gallery."

 Install PHP

Free Download

The installer works well. Click on the green "Free Download" button. Then run the wpilauncher.exe application that was downloaded. Click on the Applications button and then search for PHP. Add the PHP version most relevant to your system. Note that you can install the Microsoft Drivers for PHP at this point, but I explain the installation more later in this tip. As a DBA I choose NOT to install SQL Server via this Web Platform Installer.

Then run the wpilauncher.exe application that was downloaded.

Note the installation folder that PHP uses.

Accept agreement.

Note the installation folder that PHP uses.

A few applications to install.

Install the SQL Server drivers for PHP.

Install the SQL Server drivers for PHP.

Confirm they installed.

Confirm the applications installed.

At this point reboot your computer and ensure all is working ok.

At this point reboot your computer and ensure all is working correctly.

Step 4: Internet Information Services (IIS)

The next step is getting Internet Information Services (IIS) running. First see if the application is running. Start a browser and type in localhost as the URL and see if a basic page is displayed. If not, then you need to start IIS. In Windows click on the Start button and the Run option in Windows then enter "inetmgr" as documented in this Microsoft web site. Click on the start link as shown below to get IIS up and running. If your operating system does not support this command then search Microsoft TechNet.

Click on Start

To test if IIS is running start a browser. Then enter "localhost" as the URL and refresh the browser. If this error message is displayed then IIS is NOT running.

IIS not running

A blank browser will mean IIS is running, but no local file was found to display.

IIS not running

Step 5: Configure IIS

Next you need to configure IIS. First we need to turn on FastCGI. 'FastCGI reduces overhead associated with interfacing the web server and CGI programs'.

Click on Start -> Run and type optionalfeatures.exe and click OK. Then expand Internet Information Services -> World Wide Web Services -> Application Development Features -> enable CGI -> click OK and wait until the installation is complete.

IIS not running

Next we need to tell PHP that we have enabled FastCGI. We do that in the php.ini configuration file. When you installed PHP you should have noted the installation folder. Mine is in C:\Program Files\PHP\v5.3\php.ini

Backup *.ini files like this prior to editing them. Simply copy the file and place it in a backup folder first then edit the original file with your favorite editor.

Search for the section titled [WebPIChanges] and edit or add these settings.

fastcgi.impersonate = 1
fastcgi.logging = 0
cgi.fix_pathinfo=1
cgi.force_redirect = 0

Next we set the PHP handler mapping. What this does is tell the browser to run the PHP.exe command when it encounters a file with an extension of *.php.

Open the dialog box by clicking on Start in Windows then click on Run and type inetmgr and click OK. In the manager double click on Handler Mappings and then click on Add Managed Handler.

PHP Handler Mapping Dialog Box
PHP Handler Mapping Dialog Box

Add the details and click OK.

PHP Handler Mapping Dialog Box

Restart IIS or even your server to ensure all is OK.

Click on Start

Your Handler Mappings should show *.php allocated.

Click on Start

Your FastCGI Settings should show the path to php-cgi.exe. Reboot your machine again.

If you get stuck use this resource - FastCGI Configuration.

*** NOTE *** - For Mac users install or enable Apache and PHP on a MacBook

I decided to use PHP as the front end because I learned PHP at University and PHP could run on my Mac and Windows systems. I choose to configure my Mac after I had PHP running on my Windows machine. If you have a Mac with OS X try this resource for enabling Apache.

Mac OS X comes bundled with PHP. Here is a guide for enabling PHP on OS X.

Note that the Mac will not actually connect to the SQL Server directly. The Mac can use the PHP pages on your chosen server. The PHP pages on the machine running SQL Server use the SQL Server PHP drivers to access SQL Server. You can develop pages on the Mac, but copy them to the Windows machine when they need to connect to SQL Server. You may ask why then have PHP on the Mac? The answer is that I can develop pages remotely.

Step 6: Confirm PHP runs on each machine

The usual method to confirm PHP is installed and available to you is to "run" a simple command. The command is echo phpinfo(); Follow these steps to do that:

Step 1

Find your "DOCUMENT_ROOT".
The "DOCUMENT_ROOT" is the folder that holds the files that the web service can see.
Note that the "DOCUMENT_ROOT" folder is actually an IIS setting.
So start inetmgr again and right click on "Default Web Site" and choose explore.
Windows Explorer will open up at your "DOCUMENT_ROOT".

Document Root

Step 2

Save this snip of PHP into a file called phpinfo.php

<?php

echo phpinfo();

?>

Step 3

Place that file into your "DOCUMENT_ROOT" folder.
On my machine that was C:\inetpub\wwwroot by default. This is the PHP code in that snip of PHP.

 
Confirm PHP runs on each machine

The echo means display and the phpinfo() function returns all the data associated with PHP and your machine. Note: It is a PHP file and needs to be located in or below the "DOCUMENT_ROOT" on my system that is C:\inetpub\wwwroot\Index.php

IIS and PHP work together to process PHP commands and display the result in your computer browser. For example the result of the command phpinfo() should show something like this:

It is a PHP file and needs to be located in or below the "DOCUMENT_ROOT".

And so on.... as the result is a lot of data.

Step 7: Configure PHP for the SQL Server drivers

Now we need to tell PHP, via a configuration file, about those SQL Server drivers.
Locate your php.ini file by looking in the phpinfo output page.
Mine is located in C:\Program Files\PHP\v5.3\php.ini
The PHP.ini file is the main PHP configuration file. Be careful with it.
Copy that file into a backup folder to back it up before editing it.
Edit the file using your favorite editor.
Then add these two driver names to it. Under the [ExtensionList] section.

[ExtensionList]
extension=php_pdo_sqlsrv.dll
extension=php_sqlsrv.dll

If you get stuck try this blog on SQL Server drivers.

Reboot your machine.

Step 8: Create a SQL Server Login and User for Database Access

Don't use the sa login to access SQL Server from your PHP pages. (Did I need to say that?) Create a specific login and user name. In terms of naming conventions, if you are you just getting reporting data from SQL Server then try a login name like "Report_Reader". Or if you plan to read and write consider a login name such as "Data_User_RW". Make your life easy by using names that are intuitive. Here is some T-SQL below to create a login called "Author_RW". 

USE [master]
GO

CREATE LOGIN [Author_RW] WITH PASSWORD=N'MakeOneUp', 
DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english], 
CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO

I generally recommend using the master database as the default database for your logins rather than the user defined database just in case you drop your database or it is inaccessible. On start-up SQL Server should have master available and so your login should be able to access the SQL Server instance.

Grant that user read and write access to your database or whatever permissions are needed. In my example, I will add data reader permissions to the msdb database to allow the user to read database backup information.

CREATE DATABASE [Creativity]
GO
USE [Creativity]
GO
CREATE USER [Author_RW] FOR LOGIN [Author_RW] WITH DEFAULT_SCHEMA=[dbo]
GO
ALTER ROLE [db_datareader] ADD MEMBER [Author_RW]
GO
ALTER ROLE [db_datawriter] ADD MEMBER [Author_RW]
GO


-- Add data reader ONLY over msdb so we can see backup records
USE [msdb]
GO
CREATE USER [Author_RW] FOR LOGIN [Author_RW]
GO
USE [msdb]
GO
ALTER ROLE [db_datareader] ADD MEMBER [Author_RW]
GO

Tip: Immediately test that login to access your SQL Server instance via SQL Server Management Studio. Enter the Server Name, for Authentication choose 'SQL Server Authentication' and enter the user name as well as the password.

Immediately test that account by logging into your SQL Server system using that account and password using the SQL Server instance

Do not proceed until that login is working as expected.

Step 9: Create a PHP page to test the SQL Server connection

Here is a basic connection method for PHP to SQL Server.
Find your "DOCUMENT_ROOT" as per step 6.

Create a file called Index.php and save that "DOCUMENT_ROOT" folder.

To connect PHP to SQL Server we need these settings:

echo '<html>';
$Instance = "PC\Instance1";
$ConnectionInfo = array( "Database"=>"Creativity", "UID"=>"Author_RW", "PWD"=>"MakeOneUp");

At this point if you do not know PHP then try some tutorials or find a good book at your local library.

Now we use those settings to connect using the command sqlsrv_connect. If the connection fails we will see the errors.

$conn = sqlsrv_connect( $Instance, $ConnectionInfo);
if( $conn ) {
        echo "Connection established to $Instance.";
}else{
 echo "Connection could not be established.";
 print_r($ConnectionInfo);
     die( print_r( sqlsrv_errors(), true));
}

For the initial connection let us check the last backup dates. Note I convert the dates as it is simpler to display the dates in PHP that way. I found fields as normal SQL Server DATETIME format did not display by default using PHP. We will use sqlsrv_query to query the database. When using PHP the period "." is the concatenate command.

$SQL_String = "SELECT 
  MAX(sys.sysDataBases.Name) as DatabaseName,
  Coalesce(max(CONVERT(VARCHAR(28),msdb.dbo.BackUpset.BackUp_Finish_Date,120))
               ,'No backup taken') as LastBackupCompleted,
  Coalesce(max(convert(varchar(50),CEILING(msdb.dbo.BackUpset.BackUp_Size/1000/1000)))
               ,'No backup taken') as MBBackedUp
FROM     
  sys.sysDataBases
  LEFT OUTER JOIN msdb.dbo.BackUpset
    ON msdb.dbo.BackUpset.DataBase_Name = sys.sysDataBases.Name
GROUP BY sys.sysDataBases.Name
ORDER BY 1";

$result = sqlsrv_query( $conn , $SQL_String) or die ( "sqlsrv_query failed"  );

echo '<table align="center">';
echo '<th>Database</th><th>Last Backup Date (120 format)</th><th>Size MB</th>';
$output = '';
while ( $row = sqlsrv_fetch_array($result, SQLSRV_FETCH_ASSOC))
        {
  $output .= '<tr>'. 
   '<td>' . $row['DatabaseName'] . '</td>' .
   '<td><center>' . $row['LastBackupCompleted'] . '</center></td>' .
   '<td>' . $row['MBBackedUp']  . '</td>' .
   '</tr>';
 }
echo $output;
echo '</table>';
echo '</br>';
echo '</html>';

Hopefully you can see we have extracted an array out of SQL Server. Then we use the PHP while loop to display the results.

Save all that PHP code above into your "Index.php" file, here is a sample file - click here.  Unzip and place that file in the "DOCUMENT_ROOT" as per step 6.

Step 10: Confirm your first PHP page has produced results

Start your browser up and type in localhost/Index.php as the URL.

Confirm your first PHP page has produced results

Congratulations. If you are this far along you are doing well.

If you have no results examine your php file and make sure you have the semi colon at the end of each line in PHP or the whole page fails to load!

Then check the SQL Server logs and see if there is an error there.

Check the SQL Server logs and see if there is an error there.

Tips

  • PHP single line comments are //
  • PHP multi line comment begin with a /* and end with a */ Use these to debug portions of your code.
Next Steps
  • Design the folder structure for your projects under the document root:
    • Use CommonCode as the main folder for common code, header, footer, etc.
    • Use Configuration as the folder for XML configuration settings or your database connection settings.
    • Create a Backup folder under Configuration for copies of things like php.ini
    • Use CSS as the folder for CSS files.
    • Date display is tricky in PHP and T-SQL. Try a CAST or CONVERT as shown in tip 1145.
    • Revisit my checklist for SQL Server and PHP.


Last Updated: 2014-12-26


get scripts

next tip button



About the author
MSSQLTips author Graham Okely Graham Okely is a contract SQL Server DBA and has been working with database systems since 1984 and has been specializing in SQL Server since 2007.

View all my tips




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name
*Email
Email me updates

Signup for our newsletter

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.





Monday, October 08, 2018 - 11:47:09 AM - EntsieV Back To Top

 Thank you for the information.

I noticed the code below as you hve it the tutorial above has an error

<?php
echo phpinfo():
?>

Note : The second line should be terminated rather with ; (semicolon) and not : (colon).

The code should look like below

<?php
echo phpinfo();
?>

Thank you.



download

























get free sql tips

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



Learn more about SQL Server tools