locked
Insert JSON records into SQL Server table using PHP RRS feed

  • Question

  • I hope this is the correct forum for PHP questions related to sql server.

    I would like to pull data for all the cities in my county from the census API into a table in sql server.  I have been working on the code and was successful in having it write a record to the database table for the total number of items in the array returned by the API, unfortunately, they are all blank records.

    The code below is what i was trying to do, and below this code block is a functional sample someone put together where it treats the JSON as a string, whereas I am thinking in the context of discrete records and fields in a table.  I can follow the concept of the working code, but as I am from a SQL server background, I could relate to it better in the latter form.

    Thanks.

    <html> <head></head> <body> <?php $serverName = "server"; $connectionInfo = array( "Database"=>"database", "UID"=>"username", "PWD"=>"password" ); $conn = sqlsrv_connect( $serverName, $connectionInfo ); if( $conn ) { echo "Connection established. <br /> <br />"; }else{ echo "Connection could not be established. <br /> <br />"; die( print_r( sqlsrv_errors(), true)); } $homepage = file_get_contents("https://api.census.gov/data/2020/dec/responserate?get=NAME,DRRALL,CRRINT,RESP_DATE,CRRALL,GEO_ID,DRRINT&for=place:*&in=state:13"); $content = json_decode($homepage, true); $totalrecords = count($content)."<br /> <br />"; $NAME = $content['NAME']; $DRRALL = $content['DRRALL']; $CRRINT = $content['CRRINT']; $RESP_DATE = $content['RESP_DATE']; $CRRALL = $content['CRRALL']; $GEO_ID = $content['GEO_ID']; $DRRINT = $content['DRRINT']; $state = $content['state']; $place = $content['place']; $trunc = "TRUNCATE TABLE edtable.dbo.Demog_CensusResponse_Test"; $stmt2 = sqlsrv_query( $conn, $trunc ); //foreach ($content as $contentvalue) { for ($x = 1; $x <= $totalrecords; $x++) { //Create SQL query $sql = "INSERT INTO edtables.dbo.Demog_CensusResponse_Test(NAME, DRRALL, CRRINT, RESP_DATE, CRRALL, GEO_ID, DRRINT, state, place) VALUES('$NAME', '$DRRALL', '$CRRINT', '$RESP_DATE', '$CRRALL', '$GEO_ID', '$DRRINT', '$state', '$place')"; //WHERE $place LIKE '42425'"; //Execute SQL query $stmt = sqlsrv_query( $conn, $sql ); if( $stmt === false ){ echo "Something went wrong with the query, check error below <br /> <br />"; die( print_r( sqlsrv_errors(), true)); } else { echo "Statement executed <br /> <br />"; } } //} sqlsrv_free_stmt( $stmt ); sqlsrv_close( $conn ); ?> </body> </html>

    <html> <head></head> <body> <?php $serverName = "server"; $connectionInfo = array( "Database"=>"database", "UID"=>"username", "PWD"=>"password" ); $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)); } //Remove all previous data from the table $deleteRecords_query = "TRUNCATE TABLE edtables.dbo.Demog_CensusResponse_GACities_2020"; $stmt = sqlsrv_query( $conn, $deleteRecords_query); if( $stmt === false ){ die( print_r( sqlsrv_errors(), true)); } $homepage = file_get_contents("https://api.census.gov/data/2020/dec/responserate?get=NAME,DRRALL,CRRINT,RESP_DATE,CRRALL,GEO_ID,DRRINT&for=place:*&in=state:13"); $content = json_decode($homepage, true); //Table is created on for view purposes. echo "<table border='1'"; $j = 1; $insert_string = ""; foreach ($content as $character) { echo "<TR>"; $fieldcount = count($character); for ($x = 0; $x < $fieldcount; $x++) { //$j>1 - We are excluding first iteration because it contains field names // In this for loop, $insert_string keeps on concatenating all the values in the fields in a string with quotes and comma in between if($j > 1){$insert_string .= "'".$character[$x]."',";} echo "<td>$character[$x]</td>"; } echo "</TR>"; // rtrim removes the last comma and gives us a clean string of values in quotes which we can plug in insert statement. $insert_string = rtrim($insert_string, ","); $j = $j +1; if($insert_string <> ""){ $sql = "INSERT INTO edtables.dbo.Demog_CensusResponse_GACities_2020 (NAME, DRRALL, CRRINT, RESP_DATE, CRRALL, GEO_ID, DRRINT, state, place) VALUES( $insert_string )"; $stmt = sqlsrv_query( $conn, $sql); if( $stmt === false ){ die( print_r( sqlsrv_errors(), true)); } } //Below we are setting the $insert_string back to nothing $insert_string = ""; } echo "</table>"; ?> </body> </html>




    • Edited by olivernm Tuesday, May 5, 2020 7:09 PM
    Friday, April 3, 2020 8:00 PM

Answers

  • Second code block works, but I would like to get the first one to function as well.
    • Marked as answer by olivernm Tuesday, May 5, 2020 7:13 PM
    Tuesday, May 5, 2020 7:13 PM