Skip to content
Advertisement

pdo connect convert to MSsql and fetch data

I had such code, which works fine and I need to get same result then I convert my database connection

$dbh = new PDO('mysql:host=localhost;dbname=DB', 'root', '');
    $dbh->setAttribute(PDO::MYSQL_ATTR_USE_BUFFERED_QUERY, 1);
    $stmt = $dbh->prepare('SELECT * FROM `Details` WHERE `ID`="2";');
    $stmt->execute();
    $result = $stmt->fetchAll();
     $json=json_encode($result);
    echo $json;

And now I converting this code to:

$con = mssql_connect($myServer, $myUser, $myPass) or die("Could not connect to database: ".mssql_get_last_message()); 
if($con){
// echo "connected";
}
// Select a database:
mssql_select_db('DB') 
    or die('Could not select a database.');

$SQL = "SELECT * FROM Details WHERE ID='2'";

// Execute query:
$result = mssql_query($SQL) 

How to get THE SAME RESULT AS IN MY FIRST CODE WITH PDO Connection ?

 $result = $stmt->fetchAll();
     $json=json_encode($result);
    echo $json;

At first PDO example I get echo $json such data structure:

[{"ID":null,"0":null,"TimeID":"2","1":"2","MyToDate":null,"2":null,"MyDayOfW":"0","3":"0","WtTimeFrom":"01:30:00","4":"01:30:00","WtTimeTo":"03:00:00","5":"03:00:00","EntryDate":"2018:05:17 05:44:53.906","6":"2018:05:17 05:44:53.906"},{"ID":null,"0":null,"TimeID":"2","1":"2","MyToDate":null,"2":null,"MyDayOfW":"1","3":"1","WtTimeFrom":"06:00:00","4":"06:00:00","WtTimeTo":"06:30:00","5":"06:30:00","EntryDate":"2018:05:17 05:44:53.906","6":"2018:05:17 05:44:53.906"},{"ID":null,"0":null,"TimeID":"2","1":"2","MyToDate":null,"2":null,"MyDayOfW":"1","3":"1","WtTimeFrom":"07:00:00","4":"07:00:00","WtTimeTo":"07:30:00","5":"07:30:00","EntryDate":"2018:05:17 05:44:53.906","6":"2018:05:17 05:44:53.906"},{"ID":null,"0":null,"TimeID":"2","1":"2","MyToDate":null,"2":null,"MyDayOfW":"1","3":"1","WtTimeFrom":"07:30:00","4":"07:30:00","WtTimeTo":"08:00:00","5":"08:00:00","EntryDate":"2018:05:17 05:44:53.906","6":"2018:05:17 05:44:53.906"},{"ID":null,"0":null,"TimeID":"2","1":"2","MyToDate":null,"2":null,"MyDayOfW":"1","3":"1","WtTimeFrom":"08:00:00","4":"08:00:00","WtTimeTo":"08:30:00","5":"08:30:00","EntryDate":"2018:05:17 05:44:53.906","6":"2018:05:17 05:44:53.906"},{"ID":null,"0":null,"TimeID":"2","1":"2","MyToDate":null,"2":null,"MyDayOfW":"2","3":"2","WtTimeFrom":"03:00:00","4":"03:00:00","WtTimeTo":"05:00:00","5":"05:00:00","EntryDate":"2018:05:17 05:44:53.906","6":"2018:05:17 05:44:53.906"},{"ID":null,"0":null,"TimeID":"2","1":"2","MyToDate":null,"2":null,"MyDayOfW":"2","3":"2","WtTimeFrom":"07:30:00","4":"07:30:00","WtTimeTo":"12:30:00","5":"12:30:00","EntryDate":"2018:05:17 05:44:53.906","6":"2018:05:17 05:44:53.906"},{"ID":null,"0":null,"TimeID":"2","1":"2","MyToDate":null,"2":null,"MyDayOfW":"3","3":"3","WtTimeFrom":"03:00:00","4":"03:00:00","WtTimeTo":"04:30:00","5":"04:30:00","EntryDate":"2018:05:17 05:44:53.906","6":"2018:05:17 05:44:53.906"},{"ID":null,"0":null,"TimeID":"2","1":"2","MyToDate":null,"2":null,"MyDayOfW":"3","3":"3","WtTimeFrom":"07:30:00","4":"07:30:00","WtTimeTo":"11:30:00","5":"11:30:00","EntryDate":"2018:05:17 05:44:53.906","6":"2018:05:17 05:44:53.906"},{"ID":null,"0":null,"TimeID":"2","1":"2","MyToDate":null,"2":null,"MyDayOfW":"3","3":"3","WtTimeFrom":"12:00:00","4":"12:00:00","WtTimeTo":"12:30:00","5":"12:30:00","EntryDate":"2018:05:17 05:44:53.906","6":"2018:05:17 05:44:53.906"},{"ID":null,"0":null,"TimeID":"2","1":"2","MyToDate":null,"2":null,"MyDayOfW":"3","3":"3","WtTimeFrom":"13:00:00","4":"13:00:00","WtTimeTo":"13:30:00","5":"13:30:00","EntryDate":"2018:05:17 05:44:53.906","6":"2018:05:17 05:44:53.906"},{"ID":null,"0":null,"TimeID":"2","1":"2","MyToDate":null,"2":null,"MyDayOfW":"3","3":"3","WtTimeFrom":"14:30:00","4":"14:30:00","WtTimeTo":"15:00:00","5":"15:00:00","EntryDate":"2018:05:17 05:44:53.906","6":"2018:05:17 05:44:53.906"},{"ID":null,"0":null,"TimeID":"2","1":"2","MyToDate":null,"2":null,"MyDayOfW":"4","3":"4","WtTimeFrom":"07:00:00","4":"07:00:00","WtTimeTo":"09:30:00","5":"09:30:00","EntryDate":"2018:05:17 05:44:53.906","6":"2018:05:17 05:44:53.906"},{"ID":null,"0":null,"TimeID":"2","1":"2","MyToDate":null,"2":null,"MyDayOfW":"4","3":"4","WtTimeFrom":"03:00:00","4":"03:00:00","WtTimeTo":"03:30:00","5":"03:30:00","EntryDate":"2018:05:17 05:44:53.906","6":"2018:05:17 05:44:53.906"}]

Advertisement

Answer

Working example:

<?php
// Settings
$server = '127.0.0.1instance,port';
$user = 'user';
$password = 'password';
$db = 'DB';

// Connection
$con = mssql_connect($server, $user, $password);
if (!$con) {
    mssql_close($con);
    echo 'Error: '.mssql_get_last_message();
    exit;
}

// Query
$sql = "SELECT * FROM Details WHERE ID='2'";
if (!mssql_select_db($db, $con)) {
    mssql_close($con);
    echo 'Error: '.mssql_get_last_message();
    exit;
};
$result = mssql_query($sql);
if ($result === false) {
    mssql_close($con);
    echo 'Error: '.mssql_get_last_message();
    exit;
}

// Result
$json = array();
do {
    while ($row = mssql_fetch_assoc($result)) {
        $json[] = $row;
    };
} while (mssql_next_result($result));       

// JSON
mssql_close($con);
$json = json_encode($json);
echo $json;
?>

Notes:

Example is tested with MS SQL Server 2012 and PHP 5.2.10.

User contributions licensed under: CC BY-SA
10 People found this is helpful
Advertisement