How to connect MS SQL database server with PHP?

[Shane]

Hello All,

In order to connect MS SQL server with front end as PHP one need to use below mentioned sample connection code:

<?php
$myServer = "localhost";
$myUser = "database_username";
$myPass = "User_password";
$myDB = "Database_Name";

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

$selected = mssql_select_db($myDB, $dbhandle)
or die("Couldn't open database $myDB");

$query = "SELECT id, name, year ";
$query .= "FROM cars ";
$query .= "WHERE name='BMW'";

$result = mssql_query($query);

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

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

That’s it!

Thanks,

Shane G.
AccuWebHosting.Com

[Shane]

Re: How to connect MS SQL database server with PHP? - With DSN Connection

Sample 2: With DSN Connection

<?php

$conn = odbc_connect('myDSN','','');

if ($conn)
{
$query = "select * from cars";
$result=odbc_exec($conn, $query);

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

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

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";
?>

Thanks,

Shane G.
AccuWebHosting.Com

[Shane]

Re: How to connect MS SQL database server with PHP? Without DNS

Sample 3: DSN Less Connection String

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

$conn = new COM ("ADODB.Connection")
or die("Cannot start ADO");

$connStr = "PROVIDER=SQLOLEDB;SERVER=".$myServer.";UID=".$myU ser.";PWD=".$myPass.";DATABASE=".$myDB;
$conn->open($connStr); //Open the connection to the database

$query = "SELECT * FROM cars";

$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)
{
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;
?>

Thanks,

Shane G.
AccuWebHosting.Com

[Chris85]

Re: How to connect MS SQL database server with PHP?

I also need help in same problem.and this thread help me.thanks to all.