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
– Panagiotis Kanavos, Commented Feb 6, 2017 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
– Panagiotis Kanavos, Commented Feb 6, 2017 at 13:29

Shubham Srivastava · Accepted Answer · 2017-02-06 13:29:06Z

1

Hope this will help you

SELECT name, surname FROM emp FOR JSON AUTO

Result:

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

edited Feb 6, 2017 at 13:29

answered Feb 6, 2017 at 13:27

Shubham Srivastava

1,3232 gold badges16 silver badges29 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Panagiotis Kanavos Over a year ago

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

Usman Ali Over a year ago

Hi thanks for your effort. but if i need to check my response is converted in json or not. Then how i check this.

Shubham Srivastava Over a year ago

stackoverflow.com/questions/9804777/… you can visit here and ready about this.

Collectives™ on Stack Overflow

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

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related