Solved

PHP with MS SQL

Posted on 2010-09-12
19
808 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
Comment Utility
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
Comment Utility
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
Comment Utility
check php configuration <?php phpinfo(); ?> and confirm that the mssql module is listed there.
it should run without any problems.

0
 
LVL 8

Author Comment

by:Mohit Vijay
Comment Utility
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
Comment Utility
ok, so everything seems to be ok.
0
 
LVL 8

Author Comment

by:Mohit Vijay
Comment Utility
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
Comment Utility
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
Comment Utility
Question, Is it required to use sa account?
0
 
LVL 8

Author Comment

by:Mohit Vijay
Comment Utility
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
Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

 
LVL 13

Assisted Solution

by:p_nuts
p_nuts earned 100 total points
Comment Utility
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
Comment Utility
0
 
LVL 108

Assisted Solution

by:Ray Paseur
Ray Paseur earned 200 total points
Comment Utility
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
Comment Utility
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 108

Assisted Solution

by:Ray Paseur
Ray Paseur earned 200 total points
Comment Utility
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
Comment Utility
How can I know either I am using thread Safe or non-thread safe php version?
0
 
LVL 8

Author Comment

by:Mohit Vijay
Comment Utility
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 108

Assisted Solution

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

Author Comment

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

failed code is similar to

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

Assisted Solution

by:Ray Paseur
Ray Paseur earned 200 total points
Comment Utility
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

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

Suggested Solutions

Part of the Global Positioning System A geocode (https://developers.google.com/maps/documentation/geocoding/) is the major subset of a GPS coordinate (http://en.wikipedia.org/wiki/Global_Positioning_System), the other parts being the altitude and t…
Nothing in an HTTP request can be trusted, including HTTP headers and form data.  A form token is a tool that can be used to guard against request forgeries (CSRF).  This article shows an improved approach to form tokens, making it more difficult to…
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…
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.

762 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

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now