Monday, January 12, 2009

How to connect to MS SQL Server database

Below is the code for connecting to a MSSQL Server database.

<?php
$myServer = "localhost";
$myUser = "your_name";
$myPass = "your_password";
$myDB = "examples";

//connection to the database
$dbhandle = mssql_connect($myServer, $myUser, $myPass)
or die("Couldn't connect to SQL Server on $myServer");

//select a database to work with
$selected = mssql_select_db($myDB, $dbhandle)
or die("Couldn't open database $myDB");

//declare the SQL statement that will query the database
$query = "SELECT id, name, year ";
$query .= "FROM cars ";
$query .= "WHERE name='BMW'";

//execute the SQL query and return records
$result = mssql_query($query);

$numRows = mssql_num_rows($result);
echo "<h1>" . $numRows . " Row" . ($numRows == 1 ? "" : "s") . " Returned </h1>";

//display the results
while($row = mssql_fetch_array($result))
{
echo "<li>" . $row["id"] . $row["name"] . $row["year"] . "</li>";
}
//close the connection
mssql_close($dbhandle);
?>


Connect with a DSN

DSN stands for 'Data Source Name'. It is an easy way to assign useful and easily rememberable names to data sources which may not be limited to databases alone. If you do not know how to set up a system DSN read our tutorial How to set up a system DSN.

In the example below we will show you how to connect with a DSN to a MSSQL Server database called 'examples.mdb' and retrieve all the records from the table 'cars'.

<?php

//connect to a DSN "myDSN"
$conn = odbc_connect('myDSN','','');

if ($conn)
{
//the SQL statement that will query the database
$query = "select * from cars";
//perform the query
$result=odbc_exec($conn, $query);

echo "<table border=\"1\"><tr>";

//print field name
$colName = odbc_num_fields($result);
for ($j=1; $j<= $colName; $j++)
{
echo "<th>";
echo odbc_field_name ($result, $j );
echo "</th>";
}

//fetch tha data from the database
while(odbc_fetch_row($result))
{
echo "<tr>";
for($i=1;$i<=odbc_num_fields($result);$i++)
{
echo "<td>";
echo odbc_result($result,$i);
echo "</td>";
}
echo "</tr>";
}

echo "</td> </tr>";
echo "</table >";

//close the connection
odbc_close ($conn);
}
else echo "odbc not connected";
?>


Connect without a DSN (using a connection string)

Let see a sample script to see how ADODB is used in PHP:

<?php
$myServer = "localhost";
$myUser = "your_name";
$myPass = "your_password";
$myDB = "examples";

//create an instance of the ADO connection object
$conn = new COM ("ADODB.Connection")
or die("Cannot start ADO");

//define connection string, specify database driver
$connStr = "PROVIDER=SQLOLEDB;SERVER=".$myServer.";UID=".$myUser.";PWD=".$myPass.";DATABASE=".$myDB;
$conn->open($connStr); //Open the connection to the database

//declare the SQL statement that will query the database
$query = "SELECT * FROM cars";

//execute the SQL statement and return records
$rs = $conn->execute($query);

$num_columns = $rs->Fields->Count();
echo $num_columns . "<br>";

for ($i=0; $i < $num_columns; $i++) {
$fld[$i] = $rs->Fields($i);
}

echo "<table>";

while (!$rs->EOF) //carry on looping through while there are records
{
echo "<tr>";
for ($i=0; $i < $num_columns; $i++) {
echo "<td>" . $fld[$i]->value . "</td>";
}
echo "</tr>";
$rs->MoveNext(); //move on to the next record
}


echo "</table>";

//close the connection and recordset objects freeing up resources
$rs->Close();
$conn->Close();

$rs = null;
$conn = null;
?>


To create 'examples' database on your MSSQL Server you should run the following script:

CREATE DATABASE examples;
USE examples;
CREATE TABLE cars(
id int UNIQUE NOT NULL,
name varchar(40),
year varchar(50),
PRIMARY KEY(id)
);

INSERT INTO cars VALUES(1,'Mercedes','2000');
INSERT INTO cars VALUES(2,'BMW','2004');
INSERT INTO cars VALUES(3,'Audi','2001');



Tags:

Add To: dzone | digg | del.icio.us | stumbleupon

Comments Blog It
Hide Comments | Add New
Subscription
HTML tags and other related characters will be stripped out of comments.
E-Mail address will not be displayed and will only be used for E-Mail notifications.
Your Name *
E-Mail Address
Comments *


Hi,
This is kind of coding helpful for us .
# Posted by Thiyagu | 1 Jan 2007 23:07:44I'm really benefited.
# Posted by jenilee valdez | 25 Jan 2007 19:48:28I have install sql server 2000 and iis server.
How do i connect into my database?
I use no password and no username or how do i create one in SQL server 2000?
I want to open IE and type: http://my_computers_name/conect_to_the_sql_server_db_and_return_all_the_records_of_tblUsers.php
I know I am asking alot but is for my assignment. If you can help me I will be more then happy man.
Thanks
Yiannis Greece
# Posted by yiannis | 29 Jan 2007 07:06:37***CONNECTION TO MS SERVER2000****

-in order to connect to Ms server 2000,
-firstly
-config IIS and PHP
-secondly
-make sure that iis and php are running! on localhost for testing
then make sure that you use server side script for writing code and save
on the same folder through root--->wwwroot.

Mallinda Ramadhani
satcom network Africa
# Posted by Malinda Ramadhani | 9 Feb 2007 14:13:06Well done, this site really helped me a lot. Thanks
# Posted by Michael Hall | 21 Feb 2007 11:06:14How would you go about encrypting that string? So you don't have to have database connection username/password sitting there in plain text??
# Posted by Landho | 22 Jun 2007 11:43:35This was a great help - thank you :)
# Posted by Tony | 11 Jul 2007 04:14:13I am Thanks to you. It really nice for us.
# Posted by Bhaskar | 19 Jul 2007 21:43:25Connect with a DSN -

This worked fantastically!! Thanks much!!
# Posted by TW | 2 Aug 2007 12:36:46Wow! I had no idea what I was doing wrong. Thank you so much! This really helped me!! God bless you :D
# Posted by azilaka | 6 Aug 2007 02:15:05I'm using Server2Go (Apache + PHP) with SQL Server 2005 Express and it seems i cannot do this the "easy way". I was able to connect using a connection string but when i try to connect directly i get this error:

"Warning: mssql_connect() [function.mssql-connect]: Unable to connect to server: [servername]SQLEXPRESS in [local address]index.php on line 15
Couldn't connect to SQL Server on [servername]SQLEXPRESS"

(line15=$dbhandle = mssql_connect($myServer, $myUser, $myPass))

I opened the SQL Server Configuration Manager and i enabled all the protocols for SQL Express and the ones for the client side but i still cannot connect, should i change something in the TCP/IP section ?

I even forwarded the 1433 port(this is the one that SQL uses by default) in my router and still no luck, i really don't know what else to do, is it possible that this communication depends on some service from the system, that i may have stopped ?

Could anyone make any suggestions ?
# Posted by Keos | 12 Aug 2007 14:18:14this is great help for me.
# Posted by hitesh | 12 Sep 2007 07:19:20Thanks a lot!
It is great help for me@code.

But more question?
I'm unable to connect remot computer with this code.
So, kindli help out.


Thanks.
Haque

# Posted by Haque | 5 Nov 2007 05:27:45Hi,

And thanks for the help, I have another question though.

I am using this connection method
"Connect without a DSN (using a connection string)"

But I wanted to know if there is a way to select the table field names?

I see you are selecting the data itself, but I need the column names as well.

any thoughts?

I have them statically set for now :-(

thanks in advance
Phill
# Posted by Phill Pafford | 13 Nov 2007 11:06:57Hi,

This might come in handy for someone.

I needed to find out the column names for the records I was pulling and found nothing that worked using the mssql php functions.

I am using the "Connect without a DSN (using a connection string)" connection method above (Thanks),

and here is a query that has helped me out:

SELECT sysColumns.name AS ColumnName
FROM sysobjects AS sysObjects, syscolumns AS sysColumns
WHERE sysObjects.name='you_table_name_must_be_in_quotes_here'
AND sysColumns.id=sysObjects.id

Hope this helps someone as it did for me :-)

--Phill Pafford
# Posted by Phill Pafford | 14 Nov 2007 07:54:06I am trying to connect MSSQL 2005 using file DSN.

but getting this error ?

Warning: odbc_connect(): SQL error: [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified, SQL state IM002 in SQLConnect in

Plz any one help me...
# Posted by log | 21 Nov 2007 04:49:55gr8, it's really helped me.
# Posted by rabindra shrestha | 20 May 2008 03:48:59Very nice! thanks!
# Posted by Max | 2 Jun 2008 15:50:55hi

i am trainee and one of my leader assigned the work Php with Mysql.
I have written the code well and all but it giving an error like Cannot connect PHP, ODBC error like this i thk it mght be server doesnot support i thk but i would like to know while dealing with what is the environment we need php, mysql, unix etc like this i need any thg or not plz rply ASAP
# Posted by anusha27 | 3 Jun 2008 01:32:29Your my hero

=)

Thanks a lot
# Posted by Bob_Lerouge | 6 Jun 2008 05:57:25I have install sql server 2000 and iis server.
How do i connect database in sql server 2000 into php?
I donot know theo password & username or how do i create connection with php?
plz help me. I delevo[p the database in sqlserver 2000 already & i have need for connectivity with php.
I know I am asking alot but is for my assignment. If you can help me I will be more then happy man.
Thanks

No comments: