load json query from ms sql server in php

[emon]

help load print json data with rows and columns in ms sql server in php 

thank your for response..

[emon]

for google chart thank you.. in advance..

Noong Huwebes, Agosto 15 2013 15:34:17 UTC+8, si emon ay sumulat:

[asgallant]

Here's a basic PHP script to get you started (assuming you are querying MS SQL Server locally from a Windows-based PHP environment; there is a small change you have to make to use a non-windows based environment):

<?php
// $databaseName is the database to access
// $username is the user name to log in with
// $password is the user name's password for logging in
try {
    $db = new PDO("mssql:dbname=$databaseName", $username, $password);
}
catch (PDOException $e) {
    echo $e->getMessage();
}
$db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

$queryStr = "SELECT foo, bar, baz FROM myTable WHERE cad = :cadValue";
$params['cadValue'] = $_GET['cad'];

$query = $db->prepare($queryStr);
$query->execute($params);
$results = $query->fetchAll(PDO::FETCH_ASSOC);

$table = array();

$table['cols'] = array(
    // each element in this array is 1 column in the DataTable
    // you must specify a type for each column, the label is optional
    array('label' => 'foo', 'type' => 'string'),
    array('label' => 'bar', 'type' => 'number'),
    array('label' => 'baz', 'type' => 'number')
);

$rows = array();
foreach ($results as $row) {
    $temp = array();

    $temp[] = array('v' => $row['foo']);
    // you may have to typecast values extraced from the database if they come out as strings, eg:
    // typecast as floating point number:
    $temp[] = array('v' => (float) $row['bar']);
    // typecast as integer number:
    $temp[] = array('v' => (int) $row['baz']);

    $rows[] = array('c' => $temp);
}

// gracefully exit the database
$results = null;
$query = null;
$db = null;

$table['rows'] = $rows;

// set up header and output json (first two prevent IE from caching queries)
header('Cache-Control: no-cache, must-revalidate');
header('Expires: Mon, 26 Jul 1997 05:00:00 GMT');
header('Content-type: application/json');
echo json_encode($table);
?>

That script is set up to be used as an AJAX-based data source rather than populating the javascript directly, but it can be adapted for the latter case if you want.

[emon]

thank you in response i will try it...

Noong Biyernes, Agosto 16 2013 00:08:03 UTC+8, si asgallant ay sumulat:

[emon]

how i can connect it from localhost..??? thank you

Noong Biyernes, Agosto 16 2013 00:08:03 UTC+8, si asgallant ay sumulat:

Here's a basic PHP script to get you started (assuming you are querying MS SQL Server locally from a Windows-based PHP environment; there is a small change you have to make to use a non-windows based environment):

[emon]

Noong Huwebes, Agosto 15 2013 15:34:17 UTC+8, si emon ay sumulat:

help load print json data with rows and columns in ms sql server in php 

thank your for response..

[emon]

or from Local Area network or WAN? thank you

Noong Biyernes, Agosto 16 2013 16:26:24 UTC+8, si emon ay sumulat:

[asgallant]

If you are connecting from a Windows host, if the "mssql" driver isn't working, try the "sqlsrv" driver:

$db = new PDO("sqlsrv:dbname=$databaseName", $username, $password);

You can connect to a non-local host by using the "host" parameter of the connection string:

$db = new PDO("sqlsrv:host=host_name:port;dbname=$databaseName", $username, $password);

or

$db = new PDO("sqlsrv:host=host_ip_address:port;dbname=$databaseName", $username, $password);

If you are connecting from Linux, you have to compile the SQL server driver manually: http://blog.acjacinto.com/2011/11/compiling-php-with-mssql-servers-native.html, but the remote connection parameters should stay the same.