I like having a local development environment when I’m working on new or existing projects. This means that if I blow something up, it’s just on my local machine and not on either the staging server or (God forbid) the live server. It’s an added level of sanity for me (I can revert back to the code that worked on the staging/live server before my changes blew it up). Also, I don’t have to keep uploading changes to the staging server to make sure things are working properly. I just have to save the changes and refresh the page in my browser.
Most of our client projects are php/MySQL projects. I am running WampServer on my computer for my local development. WampServer allows you to run a development web server on your (Windows) computer, with Apache, php, and MySQL. You can download and install multiple versions of each. This helps you mimic the live web server environment. There are other solutions, such as XAMPP, but WampServer has been working well for me.
We have an upcoming client project that requires the website be on a Windows server with php and MSSQL. We do have some other projects that run php on a Windows server, so that wasn’t my concern. My concern was the MSSQL part of the equation. How in the world am I going to develop a website locally using MSSQL? Well, I was in luck. Microsoft has a FREE version of their SQL Server available for download, SQL Server 2008 R2 Express. They have several different versions based on what your needs are. I went with the Database with Management Tools version because I don’t really have the need for reporting services since I’m just developing locally.
I did run into a snag while setting things up. I couldn’t get php and the SQL server to talk to each other. Eventually I figured things out (after uninstalling and then reinstalling the SQL server). Here is how you can do it too.
Install a Web Server
If you don’t already have something like WampServer or XAMPP installed, go download one and install it. Then make sure things are working properly before you even start with the SQL server side of things. As I already said, I have WampServer installed, so I was good to go for this part.
Install SQL Server
The installation process for SQL Server is pretty straightforward. One thing that I had to make sure of was that I set the Security Mode to Mixed Mode Authentication. Other than that, I pretty much followed the article How to: Install SQL Server 2008 R2 (Setup).
Create and Configure Database
It was at this point that I ran into my first snag. I opened Microsoft SQL Server Management Studio, which was installed during the SQL server install, but no database servers were showing up. It turns out that I needed to register the server instance that was created when SQL Server was installed. Here is how you do that.
- Go to View » Registered Servers in the menu to open the Registered Servers window (in the left sidebar). (screenshot)
- Expand the Local Server Groups folder. If your SQL server instance doesn’t show up, you need to register it.
- Right click on the Local Server Groups folder and select Tasks » Register Local Servers. (screenshot)
If you get an error that says “No local servers of type ‘Database Engine’ were found“, you will need to select the New Server Registration option in the right click menu from step #3 above (this is what I had to do). This will open up an interface where you can register your server.
Please refer to the screenshot to the left (click it to enlarge it).
For the Server name, enter the name of your computer and the name of the instance you created when you installed the SQL server.
For example, if your computer is named mycomp and your instance is named SQLEXPRESS, enter mycompSQLEXPRESS for the Server name.
Then select either Windows Authentication or SQL Server Authentication based on how you want to set things up. You should be able to leave the defaults for everything else, unless you want to customize things.
After doing this, my database server instance showed up in the Objects Explorer. At this point I created a database. Right click on the Databases folder in the Object Explorer and select New Database. This will open an interface where you can create the database. You should only have to enter a name for your database. I left the default values for all other options.
I then created a login. Open the Security folder (the one under the server, not the one under the database that you just created) and the Logins folder under that. (screenshot) This will open an interface where you can create the login. Please refer to the screenshot to the below (click it to enlarge it).
Enter the name of the login and select SQL Server authentication. This login will end up being associated with the user that is used by the php code to connect to the database, so it needs a password set up for it. Selecting this security mode allows for that to be done. Once SQL Server authentication is selected, more options become available. I checked Enforce password policy and unchecked the other 2 options. For the Default database, select the database that you created for your application.
Now switch over to the User Mapping tab. You need to map this login to a user on your database (this will save you a step).Please refer to the following screenshot (click it to enlarge it).
Select the database you created earlier. A user will be created with the same name as the login. Then click the button in the Default Schema column and select the db_datareader and db_datawriter objects (I selected these since the website will be selecting/inserting/updating/deleting data, but I must admit, I’m not sure if it really did anything for me).
You should be able to leave the default values for everything else on all of the other tabs. Click the OK button to create the login.
Here is another snag that got me. You must grant the new user permission for the actions that will be performed. If you do not do this, you won’t be able to access the database from your php script. The code for that is:
GRANT SELECT, INSERT, UPDATE, DELETE TO user_name GO
Now your SQL Server should be configured properly for use with php. But you’re not done yet, or at least I wasn’t. Since I am using php version 5.2.9 and SQL Server 2008, I needed an updated version of the MSSQL driver for php, ntwdblib.dll. The version I had was [file] 2000.2.8.0 [product] 7.00.839. I had to download an updated version, [file] 2000.80.194.0 [product] 8.00.194.
The last thing that I needed to do to get MSSQL and php to play together was enable the php_mssql extension in the php configuration settings. You have 2 options for this, if you are using WampServer:
- Modify the php.ini file
- Select the php_mssql extension in the WampServer menu (screenshot)
If you are modifying the php.ini file, remove the semi-colon (;) from the beginning of the following line to uncomment and enable it.
After making that change, you will need to restart the server. Then you should be ready to go. MSSQL and php should be playing together happily. You can connect to the SQL server by using the following php function calls.
<!--?php <br ?--> $link = mssql_connect('mycompSQLEXPRESS', 'user', 'password');
die(“Unable to connect to SQL Server. “.mssql_get_last_message());
if(mssql_select_db(‘database_name’, $link) === false)
die(“Unable to select database. “.mssql_get_last_message());
I will be using CodeIgniter on this project with the SQL server, but that’s a post for another day. Hopefully this helps you get going on your own MSSQL/php project.