Solved

PHP with MS SQL

Posted on 2010-09-12
19
813 Views
Last Modified: 2013-12-13
Hello All,

How can we connect PHP with MS - SQL?

Thanks beforehand.
0
Comment
Question by:Mohit Vijay
  • 9
  • 4
  • 4
  • +1
19 Comments
 
LVL 12

Assisted Solution

by:jet-black
jet-black earned 200 total points
ID: 33656079
Hi,

If you have Windows based hosting, it will be simple because mssql module is already installed on Windows based hosting.
However it is very hard to find Unix based hosting that the mssql module is installed. However I can suggest one company which install this module if you contact and ask them to install it:
www.pacifichost.com


Connecting to mssql database is very similar to connecting to mysql databases.
Here is the all functions of mssql module:
http://php.net/manual/en/book.mssql.php





<?php
define('DATABASE_HOST_NAME', 'ip_address'); //if your database port is different than the default one, you should replace ip_address with ip_address:port
define('DATABASE_USER_NAME', 'sa');
define('DATABASE_USER_PASSWORD', 'password');

$connection = mssql_connect(DATABASE_HOST_NAME, DATABASE_USER_NAME, DATABASE_USER_PASSWORD);

if(!$connection)
{
    die('could not connected');
}
else
{
    echo "connected";
}


//test
$sql_Recordset1 = "SELECT * FROM database";
$recordset1 = mssql_query($sql_Recordset1);

while ($row_Recordset1 = mssql_fetch_assoc($recordset1))
{
    echo $row_Recordset1['id'];
}
?>

Open in new window

0
 
LVL 8

Author Comment

by:Mohit Vijay
ID: 33656087
Yes We are using Windows based hosting. Actually we are using our own server.

Its Windows Server 2008
Installed IIS
using PHP on IIS
SQL Express 2008 available
We are using Moodle.

any specific to any of these?
0
 
LVL 12

Assisted Solution

by:jet-black
jet-black earned 200 total points
ID: 33656100
check php configuration <?php phpinfo(); ?> and confirm that the mssql module is listed there.
it should run without any problems.

0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 8

Author Comment

by:Mohit Vijay
ID: 33656101
We are using FreeTDS version 8.0 and added its extension.
0
 
LVL 12

Assisted Solution

by:jet-black
jet-black earned 200 total points
ID: 33656108
ok, so everything seems to be ok.
0
 
LVL 8

Author Comment

by:Mohit Vijay
ID: 33656115
No, I am unable to connect with server. always connection failed message is appearing :(, something is missing.
0
 
LVL 12

Assisted Solution

by:jet-black
jet-black earned 200 total points
ID: 33656121
Did you tried to connect using the instance name?


<?php
// Server in the this format: <computer>\<instance name> or 
// <server>,<port> when using a non default port number
$server = 'KALLESPC\SQLEXPRESS';

// Connect to MSSQL
$link = mssql_connect($server, 'sa', 'phpfi');

if (!$link) {
    die('Something went wrong while connecting to MSSQL');
}
?>

Open in new window

0
 
LVL 8

Accepted Solution

by:
Mohit Vijay earned 0 total points
ID: 33656126
Question, Is it required to use sa account?
0
 
LVL 8

Author Comment

by:Mohit Vijay
ID: 33656131
I created a small testing app. in vb.net,

when I use

Server=WEBSRV01\SQLEXPRESS;Database=Devdb;User ID=Test;Password=Hello123;Trusted_Connection=False;

connect worked fine with vb.net application.

and when I tried to use

Server=WEBSRV01\SQLEXPRESS,1433;Network Library=DBMSSOCN;Initial Catalog=devdb;User ID=Test;Password=Hello123;

get a error, Login failed for user "Test"

0
 
LVL 13

Assisted Solution

by:p_nuts
p_nuts earned 100 total points
ID: 33658317
the connection string is different

mssql_connect('server','user',password');

mssql_select_db('dbname);

0
 
LVL 13

Assisted Solution

by:p_nuts
p_nuts earned 100 total points
ID: 33658322
0
 
LVL 110

Assisted Solution

by:Ray Paseur
Ray Paseur earned 200 total points
ID: 33662311
This function returns FALSE on error:
http://www.php.net/manual/en/function.mssql-connect.php

In MySQL we have some error handling functions.  Maybe there is some analog for those in MSSQL?
http://www.php.net/manual/en/function.mssql-get-last-message.php

You might find some diagnostic information in that function.
0
 
LVL 8

Author Comment

by:Mohit Vijay
ID: 33662443
Thanks Ray_Paseur,

I was thinking about such error information. Let me try it today, will let you know if I need something else. :)

BTW I am using connection string as

mssql_connect('server','user',password') in Test.php
0
 
LVL 110

Assisted Solution

by:Ray Paseur
Ray Paseur earned 200 total points
ID: 33662486
From the way I read the online manual, that looks correct.  Will be interesting to see if you're getting FALSE from the connect command.
0
 
LVL 8

Author Comment

by:Mohit Vijay
ID: 33662497
How can I know either I am using thread Safe or non-thread safe php version?
0
 
LVL 8

Author Comment

by:Mohit Vijay
ID: 33665576
I got false while connect, so its mean there is some error while connecting. but problem is I am not getting error information, like what type of error occurred :(
0
 
LVL 110

Assisted Solution

by:Ray Paseur
Ray Paseur earned 200 total points
ID: 33666635
Please post the code that failed, thanks.  You have verified the passwords (case-sensitive), right?
0
 
LVL 8

Author Comment

by:Mohit Vijay
ID: 33668691
yes, password, username, servername everything has been checked several times..

failed code is similar to

mssql_connect('server','user',password')
0
 
LVL 110

Assisted Solution

by:Ray Paseur
Ray Paseur earned 200 total points
ID: 33689673
That does not tell us much, since we cannot see the other code, or how the return is tested.  If you want to post the code, please use the code snippet and post enough of the code that we can see what is really going on.  Also, please have a look at the user notes on this page:
http://php.net/manual/en/function.mssql-connect.php
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Developers of all skill levels should learn to use current best practices when developing websites. However many developers, new and old, fall into the trap of using deprecated features because this is what so many tutorials and books tell them to u…
3 proven steps to speed up Magento powered sites. The article focus is on optimizing time to first byte (TTFB), full page caching and configuring server for optimal performance.
Learn how to match and substitute tagged data using PHP regular expressions. Demonstrated on Windows 7, but also applies to other operating systems. Demonstrated technique applies to PHP (all versions) and Firefox, but very similar techniques will w…
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …

740 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question