[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

PHP with MS SQL

Posted on 2010-09-12
19
Medium Priority
?
819 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 800 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 800 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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
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 800 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 800 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 400 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 400 total points
ID: 33658322
0
 
LVL 111

Assisted Solution

by:Ray Paseur
Ray Paseur earned 800 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 111

Assisted Solution

by:Ray Paseur
Ray Paseur earned 800 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 111

Assisted Solution

by:Ray Paseur
Ray Paseur earned 800 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 111

Assisted Solution

by:Ray Paseur
Ray Paseur earned 800 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

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

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…
Since pre-biblical times, humans have sought ways to keep secrets, and share the secrets selectively.  This article explores the ways PHP can be used to hide and encrypt information.
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…
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…
Suggested Courses
Course of the Month18 days, 7 hours left to enroll

829 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