Creating PHP CRUD Apps with SQL Server on your Server or in the Azure Cloud

  • Article

Microsoft SQL Server 2008 Express Do you know PHP and have data in a Microsoft SQL Server database? Well we have an application wizard that will make your lives a little easier. The project, open source and hosted on CodePlex, will help you build a simple CRUD (Create, Read, Update, Delete) application that works against Microsoft SQL Server, SQL Azure and Windows Azure Storage. The application is installable on Windows and supports data navigation, paging, sorting and UI customization using simple CSS.  crudwizard_arch_sm

Here’s what you will need, a working PHP web server, a connection to the internet, a SQL Server 2005 or higher. You can also use the free version. “SQL Server Express” that is available for download and installable as part of the Web Platform Installer. As an added bonus you can also work against your Windows Azure Storage or SQL Azure database.   Windows Azure tokens are available by registering for Windows Azure Services and redeemable at http://windows.azure.com

To begin download the wizard, open the.zip and install on your PC. There is a handy deployment guide that helps you get started. You will simply need to set up a database account with a username and password. Install the SQL Server 2005 Native Client DLL and the  SQL 2005 PHP Driver 1.1 which will give you a .dll for the appropriate version on PHP you are running (5.2 or 5.3, thread-safe and non). Copy the appropriate .dll to your PHP extension directory (e.g. C:\php\ext) and add a reference to your PHP.ini file (e.g. C:\php\php.ini) to call the .dll, (e.g. extension=php_sqlsrv_xx_yyy.dll)

You will then want to use “SQL Server Management Studio” (ssms.exe) from the Start Menu (All Programs or Programs) or from C:\Program Files\Microsoft SQL Server\100\Tools\Binn\VSShell\Common7\IDE to create a new user and a database. The tutorials listed below are a quick way to get started

Following the steps above I created a login to my SQL Server, Created a database “sqlcruddemo” and a new table called “customer” and associated a database user “demo” to it.  In the table I then created 3 columns. ID, set as a non-null integer and a “Primary Key” plus Firstname and Lastname both as variable character strings of length 50.  This is how it looks like from the design tool.

sqlmgmtstudio

Once you have the database set up you can run the file PhpSiteGenerator.exe either from the “PHP SQL Crud Application Builder” entry in your Start menu or from the Installation folder (typically C:\Program Files\PHP SQL Crud Application Builder). The splash page shows up as below. You will want to enter in the name of the database user you created (e.g. demo) and it’s password. Then click the button “Find Database” where it will bring down entries for the “Database” and “Table” drop down menus. I have selected the entries I created before (database “sqlcruddemo” and table “customer”) and the checkboxes for the columns (ID, Firstname, Lastname)

Hit the “Next” button to get to the second screen where you can set CSS styles for the table of the CRUD application. I decided to change the “.tblHeaderCell” tag, which sets the table headers, with a blue background-color and bold fonts. The CSS in the generated file, “style.css” and the form looks like  … PHPGenerator1

PHPGeneratorCSSoutput

PHPGenerator2 

Hitting “Generate Site” will yield the result in the following form, “index.php”, which I have populated entries using the “Create New” button which calls the “create.php” file also displayed in the browser belowCRUD

 

 

 

 

 

 

 

 

 

 

 

You can use these generated helper php files and forms as the building blocks for the database driven application that you would like to write against SQL Server. As you can see most of the PHP code you need to manipulate data from SQL Server has been taken care for you.

Take a look!

Jas Sandhu
Technical Evangelist, Interop Vendor Alliance Manager, Interoperability Strategy Team
Twitter@jassand, FriendFeed@jassand