Select data from SQL table and convert it in json formate?

Question

I am new to use SQL Server. I try to get data from a SQL Server table and convert it to JSON format and show it in HTML table. I try to do this two much. But still can't have success. If anyone know how to do this please help me. Any of your help effort is appreciated.

Here is my T-SQL query.

function Crawled_sites_total(){
//$sql = "SELECT * FROM crawl_site";
$sql = "SELECT  
    CSCH.CrawlSiteID, CSCH.CrawlHistoryID, CSCH.CountAtStart, 
    CSCH.CountAtEnd, CSCH.RecodsFound, CSAR.CrawlSite, 
    CSAR.AverageRecords as Standered, 
    CAST((((CONVERT(FLOAT, CSCH.RecodsFound) - CONVERT(FLOAT, CSAR.AverageRecords)) * 2 * 100) / (CONVERT(FLOAT, CSAR.AverageRecords) + CONVERT(FLOAT, CSCH.RecodsFound))) AS DECIMAL(18, 2)) as Tolerance 
FROM 
    csCrawlSiteCrawledHistory as CSCH 
INNER JOIN 
    csCrawledHistory as CH on CSCH.CrawlHistoryID = CH.CrawlHistoryID 
INNER JOIN 
    csCrawlSiteAverageRecords as CSAR on csch.CrawlSiteID = CSAR.CrawlSiteId 
WHERE 
    CH.CrawlHistoryID = (SELECT TOP 1 C.CrawlHistoryID 
                         FROM csCrawledHistory C 
                         WHERE C.EndTime IS NOT NULL 
                         ORDER BY C.EndTime DESC) ";
//$result = $GLOBALS['conn']->query($sql);
while($row = mssql_fetch_assoc($sql)){

    $row = array(
        // data from theme
        //'id'      => $row['id'],
        'CrawlSiteID'       => $row['CrawlSiteID'],
        'CrawlHistoryID'    => $row['CrawlHistoryID'],
        'CountAtStart'  => $row['CountAtStart'],
        'CountAtEnd'    => $row['CountAtEnd'],
        'RecodsFound'   => $row['RecodsFound'],
        'CrawlSite' => $row['CrawlSite'],
        'Standered' => $row['Standered'],
        'Tolerance' => $row['Tolerance'],
    );
    $data[] = preg_replace('/[\x00-\x1F\x80-\xFF]/', '', $row);
}  
echo  json_encode($data);

}

How *did* you try to do it and what was the problem? Where is your code? SQL Server 2016 is the only version that supports JSON. Earlier versions have no support for Json, you'll have to generate the string on the client side, eg using Json.NET. Which one are you asking about? The query doesn't help at all — Panagiotis Kanavos, Feb 06 '17 at 13:27
Posting a bigger query helps even less. *Are* you using SQL Server 2016? Then why no JQuery keyword? If not, post the code you used to generate Json. — Panagiotis Kanavos, Feb 06 '17 at 13:29

Shubham Srivastava · Answer 1 · 2017-02-06T13:29:06.887

1

Hope this will help you

SELECT name, surname FROM emp FOR JSON AUTO

Result:

[{
    "name": "shubham"
}, {
    "name": "shubham",
    "surname": "xaio"
}]

edited Feb 06 '17 at 13:29

answered Feb 06 '17 at 13:27

Shubham Srivastava

1,190
14
28

First, that only works with SQL Server 2016. Second, the OP just changed the query. It's quite a bit more complicated but still no hint of an attempt to generate JSon – Panagiotis Kanavos Feb 06 '17 at 13:28
Hi thanks for your effort. but if i need to check my response is converted in json or not. Then how i check this. – Usman Ali Feb 06 '17 at 13:44
http://stackoverflow.com/questions/9804777/how-to-test-if-a-string-is-json-or-not you can visit here and ready about this. – Shubham Srivastava Feb 06 '17 at 13:56

Select data from SQL table and convert it in json formate?

1 Answers1