How To: phpMySQL 3.1.4 on Windows and MS SQL

[Grunt]

I spent a few long days trying to get phpmyfaq working on Linux, which got me familiar with how the application works. However, since I'm coming from a Windows background and the environments I work in are all Windows-centric,I set out to figure out how to get phpmyfaq running on top of IIS and MS SQL. While the process isn't difficult once you know the secret sauce, there's a couple gotchas that I ran into along the way.

Architecture Overview
Application Server

• Windows Server 2019
• IIS Role
• PHP 8.0
• phpMyFAQ

Database Server

• Windows Server 2019
• SQL Server 2019

Required Software

• Microsoft Web Platform Installer - https://www.microsoft.com/web/downloads/platform.aspx
• Microsoft ODBC 18 Driver - https://docs.microsoft.com/en-us/sql/co ... rver-ver16
• Microsoft sqlsrv extension - https://docs.microsoft.com/en-us/sql/co ... rver-ver16
• Microsoft VC Runtime 2019 - https://aka.ms/vs/16/release/vc_redist.x64.exe
• PHP 8.0
• PHP Manager (Optional, recommended) - https://github.com/phpmanager/phpmanager/releases
  
• SQL Server Management Studio (Optional) - https://docs.microsoft.com/en-us/sql/ss ... rver-ver16

IIS Setup
Note:During setup of IIS, ensure you select CGI from under application development.

Web Platform Installer
If you haven't already installed the Web Platform Installer (WPI)

1. Open IIS and select Get New Web Platform Components.
2. Select Install this extension to download the installer
3. Run the installer.

You can now access WPI from the Start Menu, or from within IIS under Feature Management.

Microsoft Visual C++ Runtime 2019
Install the VC Runtime prior to attempting to install anything from WPI.

PHP 8.0
The easiest way to install PHP on IIS is through the use of WPI. WPI will automatically configure some settings in IIS to get php working.

1. Open WPI
2. In the top right corner search bar, enter PHP 8.0 and hit enter.
3. Locate PHP 8.0 (x64) and select Add
4. Select Install.

Note: Do not try to install multiple options at the same time as it may cause installation of PHP to fail.


PHP Manager (Optional)
PHP Manager integrates with IIS to perform some basic, but critical functions for PHP. I highly recommend using it.

1. Download the latest release of PHP Manager.
2. After installing PHP Manager, open IIS.
3. Select the server in the connections pane.
4. In the main pane, select PHP Manager.

PHP Manager will display a yellow warning box if you're missing the proper vc runtime or if you php.ini is not optimally configured. Ensure that you resolve any of these issues before continuing.

Enabling Required Extensions
PHP comes with most extensions required enabled by default. However, there are a couple required extensions that are not enabled by default.

1. Open IIS
2. Select the website that will host your files
3. Open PHP Manager
4. At the bottom, select Enable or disabl an extension
5. Right-click php_fileinfo.dll and select Enable.
6. Do the same for php_gd.dll.
7. If you plan to integrate with directory services (Active Directory, OpenLDAP, etc...), you'll also need to enable php_ldap.dll.

Verifying PHP Functionality
Let's ensure PHP is working properly with IIS up to this point.

1. Navigate to C:\inetpub\wwwroot
2. Create a file called phpinfo.php.
3. Edit the file using notepad and enter the following:
   

   Code: Select all

   <?php
   phpinfo();
   ?>

4. Save the file.
5. Open a browser on the server.
6. Go to localhost/phpinfo.php.

If you've done everything properly, you should get a page displaying information about PHP and the loaded extensions.

Microsoft ODBC Connector
If you haven't already, install the Microsoft ODBC driver linked in the Required Software section above.

Microsoft SQL Configuration
I recommend installing SQL Server Management Studio SSMS to setup a user and an empty database for phpMyFAQ.

1. Log into SSMS
2. Right-click on Security, highlight New, then Login...
3. Enter a login name
4. Select SQL Server authentication and enter a password.
   • I would recommend disabling the three boxes related to password management for the user.
5. Click OK to complete user setup.
6. Right-click on Databases and select New Database...
7. Enter phpmyfaq for the name.
8. Click the three dots next to the Owner box and enter the username you just created.
9. Click the Check Names box to ensure the user is properly detected and then click OK
10. Click OK to complete database setup.
11. Right-click on the server name in the connections pane and select Properties
12. Select Connections on the left side and verify the box is checked to allow remote connections to the server.

phpMyFAQ
Installation

1. Delete any files in C:\inetpub\wwwroot.
2. Unzip phpMyFAQ to C:\inetpub\wwwroot so that index.php is in the wwwroot folder.
3. In File Explorer, goto C:\Inetpub
4. Right-Click the wwwroot folder and select Properties
5. On the Security tab, select Edit
6. Select Users and then check the box under the Allow column for Full Control.
7. Click Ok to apply the new permissions then OK to close the properties dialog.

Configuration
phpMyFAQ 3.1.4: If you are testing in a non-production environment, you must edit the sqlsrv.php file to allow self-signed certificates from SQL.

1. Open File Explorer and go to phpMyFAQ/src/phpMyFAQ/Database
2. Open Sqlsrv.php using notepad
3. Go to line 101, which should read 'CharacterSet' => 'UTF-8',
4. Hit enter and then enter 'TrustServerCertificate' => true

Database Setup
Server: MS SQL Server
Host/Socket: IP Address of your SQL server
Port: 1433
User: Name of the user you created in SSMS
Password: Password of the user you created in SSMS
Database: Name of the database you created in SSMS

LDAP Setup
Host: IP Address of your LDAP server.
Port: LDAP port to use
User DN: Username in the syntax: DOMAIN\Username
Password: Domain user password
Base DN: Base OU to search for users.

Elasticsearch Setup
Server(s): IP Address:Port of your Elasticsearch Instance
Index Name: Desired name of your index. phpMyFAQ will create it for you.

Admin User Setup
These settings should be self-explanatory. Once complete, select Click to install phpMyFAQ 3.1.4 at the bottom to kick off creation of the database tables and basic setup.

With any luck, you'll get a page that tells you setup was a success with a link that will take you to your new homepage.


3.1.4 Specific Configuration Modifications
There are a couple database queries that need to be modified to properly work with MS SQL. Later versions should have these issues resolved.
Category Query

1. Open File Explorer
2. Navigate to phpmyfaq\src\phpMyFAQ
3. Copy Category.php
4. Rename the copy to Category.php.bak
5. Open Category.php
6. Go to line 255
7. Change it to:

   Code: Select all

   fc.name, fc.id ASC',

8. Save the file.

Note: This will break category sorting options and hardcode it to sort categories by name only.

FAQ Query

1. Open File Explorer
2. Navigate to phpmyfaq\src\phpMyFAQ
3. Copy faq.php
4. Rename the copy to faq.php.bak
5. Open faq.php
6. Go to line 2393
7. Change it to:

   Code: Select all

   fd.id,fd.lang,fcr.category_id,fv.visits,thema

8. Save the file.

Attachment Management Query

1. Open File Explorer
2. Navigate to phpmyfaq\src\phpMyFAQ\Attachment
3. Copy AttachmentCollection.php
4. Rename the copy to AttachmentCollection.php.bak
5. Open AttachmentCollection.php
6. Go to line 73
7. Change it to:

   Code: Select all

   fa.id,fd.thema,record_id,record_lang,filename,filesize,mime_type',

8. Save the file.

[Thorsten]

Awesome, thank you!

I'll fix the issues for v3.1.5

bye
Thorsten

[Grunt]

Ya, I believe you've handled all of the query fixes already in a bug report I made on Github. Just pointing them out since this is the version we are working with at the moment.