Geeks With Blogs

News


Timmy Kokke's Blog

↑ Grab this Headline Animator

Timmy Kokke at Blogged

Timmy Kokke …just sorting my bubbles…

Intro

For the blog engine I’m writing for the WinPHP contest I needed a place to store data. Because windows specific features need to be used, Microsoft SQL Server is the logic choice. Getting data from the database isn’t as easy as making that choice. The extension that comes with PHP worked well with SQL Server 2005, but failed when running on a Windows Server 2008 machine with SQL Server 2008. Here’s a step-by-step tutorial on how to get it to work on SQL Server 2008.

 

Installation

First, download and install the Microsoft SQL Server 2005 Native Client.

Than download and install the SQL Server Driver for PHP Version 1.0 into the /Ext folder of your php installation.

Edit the php.ini file and add extension=php_sqlsrv.dll.

Restart IIS to reload the php.ini file.

To test if it works create a .php file at a location accessible from you browser file and add <?php phpinfo(); ?> to it. Open the location and check if sqlsrv appears in the list.

 

Getting Data

The hard part is done now. To get data from you SQL server a connection has to be made first. A call to sqlsrv_connect( string $serverName, array $connectionInfo ) with the right credentials will do just that.

For example:

$connectionInfo = array('Database'=>'YourServerInstance', 
                        'UID'=>'UserName',
                        'PWD'=>'AComplicatedPassword');
$connection = sqlsrv_connect('DatabaseOfChoice',
                             $connectionInfo); 

 

Getting some data from the database is almost as easy as just using SQL. A call to sqlsrv_query( resource $conn, string $tsql, array $params). This example returns all values from the aTable table where theID column equals 5:

$result = sqlsrv_query( $connection, 
                       'select * from aTable where theID = (?) ' , 
                       array( 5 ));
 

The data can be extracted from the result set by calling sqlsrv_fetch_array( resource $stmt). The function returns an array of a row, which by default can be accessed associative or numeric. Here’s an example.

while($row = sqlsrv_fetch_array($result))
{
    echo($row['ID'] . ', '.
         $row['Title'] . ', '.
         $row['Name']);
}

 

More Code

Here’s an entire class as a start to use in your own projects. It uses a singleton pattern so the database connection doesn’t have to be made a every call to the database. Only a call like db::query(‘select * from table’); to query the database. If a connection has to be made first, the class will take care of that.

 

class db 
{    
    private static $instance = null; 
    private $connection; 
 
    public static function getInstance() 
    { 
        if(self::$instance == null) 
        { 
            self::$instance = new self; 
        }    
        return self::$instance;          
    } 
 
    private function __construct() 
    {               
        $connectionInfo = array('Database'=>'YourServerInstance', 
                                'UID'=>'UserName',
                                'PWD'=>'AComplicatedPassword'); 
       
        $this->connection = sqlsrv_connect("DatabaseOfChoice", 
                                           $connectionInfo); 
 
        if ($this->connection === false) { 
            echo '<h2>Unable to connect to database</h2><br/>'; 
            die (print_r(sqlsrv_errors(), true)); 
        };               
    } 
      
    public static function query($query, $params=null) 
    { 
        $db = self::GetInstance(); 
        $result = sqlsrv_query( $db->connection, $query, $params); 
        if (!$result ) 
        {     
             echo 'Error in statement execution.\n'; 
             die( print_r( sqlsrv_errors(), true)); 
        } 
        return $result;          
    } 
} 

 

What’s Next?

I don’t think it’s a good idea to hard code the connection information in the .php file. A far better way is to store these settings in an .xml file and load that from the db-class. Also, the class above can easily be extended with other functions from the SQL server for PHP API.

Posted on Friday, May 8, 2009 3:55 PM WinPHP , Microsoft SQL , PHP | Back to top


Comments on this post: How to get data from Microsoft SQL Server using PHP

# re: How do i get arabic data from Microsoft SQL Server using PHP
Requesting Gravatar...
thanks in advance for helping
Left by tony on Oct 10, 2009 11:44 AM

# re: How to get data from Microsoft SQL Server using PHP
Requesting Gravatar...
Now that is what I call an excellent guide, thanks...
Left by Bonus Doxxbet on Feb 03, 2010 9:30 PM

# re: How to get data from Microsoft SQL Server using PHP
Requesting Gravatar...
How do i connect to MS SQL server through PHP on Unix OS. I tried using sqlsrv_connect ($servername, $connectionInfo). It gives a FATAL ErrorL call to undefined function sqlsrv_connect()
Left by Ash on Sep 01, 2010 8:10 PM

# re: How to get data from Microsoft SQL Server using PHP
Requesting Gravatar...
Hey, nice guide, but i'm not so far that i can use it, i still have a problem to first write data in the mssql tabel...i've got a package from 1and1.com but it does not work, could you please help me? :)
Left by Kevin on Aug 01, 2012 11:34 AM

# re: How to get data from Microsoft SQL Server using PHP
Requesting Gravatar...
I have tried this method but still my data could not load onto my dropdownlist
I keep getting these errors"
PHP Warning: sqlsrv_query() expects parameter 1 to be resource, boolean given in C: on line 36
PHP Warning: sqlsrv_fetch_array() expects parameter 1 to be resource, boolean given in C: on line44"



Here is my script please help!
<?php
// turn on php error reporting
ini_set('display_errors',1);
error_reporting(E_ALL);
//Php Code to connect to postgresqldatabase
$serverName = "SQLEXPRESS\dbo";
$uid = "sa";
$pwd = "Casa2009";
$databaseName = "dbo";

$connectionInfo = array("UID"=>$uid,
"PWD"=>$pwd,
"Database"=>$databaseName);

/* Connect using SQL Server Authentication. */
$conn = sqlsrv_connect( $serverName, $connectionInfo);
if(!$conn) {
echo "Connection established.<br />";
}else{
echo "Connection could not be established.<br />";
die( print_r(sqlsrv_errors(), true));
}

$stmt = sqlsrv_query($conn, 'select * from apptally where theID = (?)', array(5));
while($row = sqlsrv_fetch_array( $stmt))
{
echo($row['ID'] . ', '.
$row['PIPENO'] . ', '.
$row['HEATNO']);
}

sqlsrv_free_stmt( $stmt);
?>
</select>
</form>
</body>
</html>
Left by Shad on Nov 20, 2012 12:43 AM

# sqlsrv_fetch_array() expects parameter 1 to be resource when between dates
Requesting Gravatar...
good mormimg, excuse my poor english, get this error when in php :
$fecha1='01/11/2012';
$fecha2='03/11/2012';
<?php

$sql = "SELECT convert(varchar(14),fecha,101) as fecha2, odontologo,nombrepac,laboratorio from ordenes where fecha2 between '$fecha1' and '$fecha2' ";
Left by juan arminio on Dec 16, 2012 11:54 AM

# re: How to get data from Microsoft SQL Server using PHP
Requesting Gravatar...
All are good. Great post about the Microsoft SQL Server using PHP. But you can get more code also video tutorials. Click this link.

Details
Left by Mh Shamim on May 01, 2014 9:55 PM

# How to fetch data from Microsoft SQL Server using PHP
Requesting Gravatar...
<?php

require_once 'db.php';
$serverName = "HP";
$connectionInfo = array( "Database"=>"sepsus");
$conn = sqlsrv_connect( $serverName, $connectionInfo );
if( $conn === false ) {
die( print_r( sqlsrv_errors(), true));
}

$number = $_POST['imei No'];

$sql ="SELECT name,ps,[imei no],Telephone FROM sepsus.DBO.imei_number WHERE [imei No] ='$number'";

$stmt = sqlsrv_query( $conn, $sql );
if( $stmt === false) {
die( print_r( sqlsrv_errors(), true) );
}

while( $row = sqlsrv_fetch_array( $stmt, SQLSRV_FETCH_ASSOC) ) {
echo($row['Name'] . ', '.

$row['ps'] . ', '.

$row['imei no'] . ', '.

$row['telephone']);

}

sqlsrv_free_stmt( $stmt);
?>
Left by sunil on Jun 10, 2015 1:59 PM

# How to fetch data from Microsoft SQL Server using PHP
Requesting Gravatar...
<?php

require_once 'db.php';
$serverName = "HP";
$connectionInfo = array( "Database"=>"sepsus");
$conn = sqlsrv_connect( $serverName, $connectionInfo );
if( $conn === false ) {
die( print_r( sqlsrv_errors(), true));
}

$number = $_POST['imei No'];

$sql ="SELECT name,ps,[imei no],Telephone FROM sepsus.DBO.imei_number WHERE [imei No] ='$number'";

$stmt = sqlsrv_query( $conn, $sql );
if( $stmt === false) {
die( print_r( sqlsrv_errors(), true) );
}

while( $row = sqlsrv_fetch_array( $stmt, SQLSRV_FETCH_ASSOC) ) {
echo($row['Name'] . ', '.

$row['ps'] . ', '.

$row['imei no'] . ', '.

$row['telephone']);

}

sqlsrv_free_stmt( $stmt);
?>


im getting an error as

Notice: Undefined index: imei No in C:\xampp\htdocs\search\php\search.php on line 11

please help me out and mail the solution to my mailid, and it would be very greatful to me...!
Left by sunil on Jun 10, 2015 2:07 PM

# re: How to get data from Microsoft SQL Server using PHP
Requesting Gravatar...
how to setup php for mssql server 2008
Left by chandan on Oct 17, 2016 1:25 PM

Your comment:
 (will show your gravatar)


Copyright © Timmy Kokke | Powered by: GeeksWithBlogs.net