Setting Up a Database for a PHP Application on IIS

Set Up a SQL Server Database from Microsoft SQL Server Management Studio Express

SQL Server is a database built and supported by Microsoft.   Many PHP applications support SQL Server as their back end data repository. These instructions assume that you already have SQL Server installed and running.  For this walkthrough, we used SQL Server® 2005. You can also use MySQL, SQL Server® Express or others.

Before starting the install, create a database on your database server. Also create a user and grant this user ownership permission to the database. If you are using SQL Server 2005, then open Microsoft SQL Server Management Studio and click "New Query" button. Enter the following script into the query window (this script creates the database and user).

USE [master]
GO

CREATE DATABASE my_DB
GO

CREATE LOGIN [my_DBuser]
WITH PASSWORD=N'pass@word1',
     DEFAULT_DATABASE=[my_DB],
     CHECK_EXPIRATION=OFF,
     CHECK_POLICY=OFF
GO

USE [my_DB]
GO

CREATE USER [my_DBuser] FOR LOGIN [my_DBuser] WITH DEFAULT_SCHEMA=[dbo]

In addition, grant the newly created user the db_owner permissions to my_DB database:

 

Setup a MySQL Database from the Command Line

MySQL is a popular database supported by most PHP applications as their back end data repository. These instructions assume that you already have MySQL installed and running. 

Before starting the installation procedure for your PHP application, create a database on your server. Also create a database user and grant this user ownership permission to the database. First, log on to your MySQL server with a command similar to the following (you must log on with an account with admin privileges; in our case 'root'):

c:\> mysql  -u root -p

You then must provide the password to your root or admin account on the MySQL server.

Next, create a database for the your application at the MySQL prompt after logging in:

mysql>CREATE DATABASE my_DB;

To create the database, download the MySql GUI-Tools from: http://dev.mysql.com/downloads/gui-tools/5.0.html. Then you can create the database using a simple “create database cslh” command using the MySQL Query Browser.  

Next, create the account that will be used with your PHP application to access this particular database:

mysql> GRANT ALL PRIVILEGES ON my_DB.* TO 'my_DBuser'@'%' IDENTIFIED BY 'my_DBpass';

This command creates the account, sets the password, and sets the privileges all in one line:

• GRANT ALL PRIVILEGES ON my_DB.* - This statement says to allow all privileges for the account but only for the 'my_DB' database and all of its tables.
• TO 'my_DBuser'@'%' - This statement specifies the account name and which machines can login with this account. In this case, 'my_DBuser' is the name of the account that will be created and '%' means that you can login to the account from anywhere. If '%' was replaced with localhost or a specific IP address you could only login to the database from that location.
• IDENTIFIED BY 'my_DBpass'; - This statement sets the password for the account to 'my_DBpass' and ends the entire command with a semi-colon.

Note: Refer to the MySQL documentation for more complete information on managing user accounts and privileges.

Related Content

Comments

1. Submitted on May 25 2009 by
   mikemalter Where is the rest of the article? How do you get PHP to use SQL Server?
2. Submitted on Jul 22 2009 by
   tnuis Jezus christ,
   
   I have been trying for days to make a database but there is no step by step posts that can pull me through. I have installed SQL Server 2005 on my 2003 server but there is no such thing as "Microsoft SQL Server Management Studio" on my computer. The only thing I can choose from in the microsoft sql server 2005 directory on "START" is " configuration tools" which gives me these three options:
   
   -SQL server configuration manager
   -SQL server error and usage reporting
   and
   -SQL server surface area configuration
   
   And there is nowhere in any of these were I can make any Query's or Database's. There is not even anything mentioned about these two topics in the help tab's.
   
   I must be doeing something terrible wrong here. Feel kind a stupid.
   
   Can anybody please help me?
3. Submitted on Jul 31 2009 by
   jahmekyat Hi tnuis,
   Do a search for the Microsoft SQL Server Management Studio and download and install. You need this seperate component to manage your SQL Server instance. Once you get that installed, follow the instructions at the top of the page. It should be fairly easy after that.