I’m familiar with access and manipulate MS SQL Server in Windows Forms or Asp.net Web Application but today, I have to find a way to connect to MS SQL Server by using PHP.
I’m working on an ASP.NET web application (C#) that connects to MS SQL Server database but there is a function that I spend hours to work out but don’t have any solution yet and I may not meet my deadline. Luckily, it becomes possible if I use PHP to solve this issue and my client just want to get his job done no matter the programming language :). His dedicated server also supports PHP, of course.
However, the PHP function need to connect to MS SQL Server to pull data from some tables. Another issue I have to face with. After research around the Internet, I have a PHP script that connects to MS SQL Server and works properly. I would like to save this script and share with everyone.
Connect to MS SQL Server by using PHP
Below is the PHP script just for demonstration purpose. It connects to my local MS SQL Server, select database, add some dump data and display them.
<?php $host = "localhost"; $db_user = "sa"; $db_password = "123456"; $database = "[4rapiddev]"; //connection to the database $dbh = mssql_connect($host, $db_user, $db_password) or die("Couldn't connect to SQL Server on $host"); //select a database to work with mssql_select_db($database, $dbh) or die("Couldn't open database $database"); //insert some data for testing purpose mssql_query("insert into tbl_members(first_name, last_name) values ('Hoan','Huynh')"); mssql_query("insert into tbl_members(first_name, last_name) values ('Rapid','Development')"); $query = "select * from tbl_members"; //execute the SQL query and return records $result = mssql_query($query); $total_rows = mssql_num_rows($result); echo "total_rows: " . $total_rows . "<br>"; echo "<hr>"; //display the results while($row = mssql_fetch_array($result)) { echo $row["first_name"] . " " . $row["last_name"] . "<br>"; } //close the connection mssql_close($dbh); ?> |
Structure of the tbl_members table above as below:
CREATE TABLE [dbo].[tbl_members]( [id] [INT] IDENTITY(1,1) NOT NULL, [first_name] [nvarchar](50) NULL, [last_name] [nvarchar](50) NULL, [created_date] [DATE] NULL, CONSTRAINT [PK_tbl_members] PRIMARY KEY CLUSTERED ( [id] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO ALTER TABLE [dbo].[tbl_members] ADD CONSTRAINT [DF_tbl_members_created_date] DEFAULT (getdate()) FOR [created_date] GO |
