PDO Output to JSON

[Jamiesw]

Hi there, Hoping some one can help. I am new to using PDO and i am trying to get data from my db to json

I can get the data down to my $result so i know that part is working, my issue is with encoding it to json, if i pull a single record then it works if i pull all records then it doesn't, an i am not sure why

$result = $page->read($_GET['pageid']); 
     
	 //check for data
    if ($result) {
        //page array
         $page_arr['pages'] = array();
        while ($row =$result->fetch(PDO::FETCH_ASSOC)) {
            extract($row);
            $page_item = array(
            'id'=> $id,
            'title' => $title,
            'pageid' => $pageid,
            'pagecontent' =>html_entity_decode($pagecontent),
            'mdkeywords' => $mdkeywords,
            'description' =>$description,
            'date_added' =>$date_added,
            'hasAudio' => $hasAudio,
            'excludeSearch' =>$excludeSearch
            );

            //push to data
            array_push($page_arr['pages'], $page_item);
         }
         //print_r($page_arr['pages']); //If i enable this then i can see all the data
         //turn to  json
        echo json_encode($page_arr['pages'], JSON_FORCE_OBJECT);
         //This is where my page is blank.

    }
} else {
    echo json_encode(array('message'=>'nothing here!.'));
}

 

[taquitosensei]

I usually echo at the end of the script instead of in the loop and also send 200 http response. 

$result = $page->read($_GET['pageid']); 
     
	 //check for data
    if ($result) {
        //page array
         $page_arr['pages'] = array();
        while ($row =$result->fetch(PDO::FETCH_ASSOC)) {
            extract($row);
            $page_item = array(
            'id'=> $id,
            'title' => $title,
            'pageid' => $pageid,
            'pagecontent' =>html_entity_decode($pagecontent),
            'mdkeywords' => $mdkeywords,
            'description' =>$description,
            'date_added' =>$date_added,
            'hasAudio' => $hasAudio,
            'excludeSearch' =>$excludeSearch
            );

            //push to data
            array_push($page_arr['pages'], $page_item);
         }
		 http_response_code(200); 
} else {
    $page_arr['message']="Nothing Here!!!"; 
}
echo json_encode($page_arr); 

 

[Jamiesw]

Thanks for replying so quickly,

I am not echoing the json_encode within the loop it is echoing within the if{}, once the while{} is completed.

If i print_r($page_arr['pages']); instead of trying to echo json_encode then i can see all the entries within the array.

I just seems to be the json_encode isnt working, i am using PHP 7.3.6 if that makes a difference?

i tried it at the end and it still made no difference..

[ginerjm]

Since your fetch is already returning an array to you and since you are needlessly creating vars from that array only to build another array with them, why not simply push the $row array onto your final array and save a lot of code?

And why the json? You are just trying to output the rows?  Why not a simple echo with a print_r wrapped in a <pre></pre> pair?

[ginerjm]

And thinking about this some more, why not simply do a FetchAll instead of a loop and use the resulting array as your array?

[Jamiesw]

Hi,

As I mentioned I am new to using PDO and was using an example from brad traversy building a backend api, the code i have used is more or less what he provided in his example including the fetch.

The idea being i can pull the outputted json and use on the frontend using javascript.

[ginerjm]

Well, there is really no "connection" between PDO and json.  They are individual things that do different things.  But now that you tell us that you have some JS code in your page I can see why you want to pass the query results to it using Json.

I would suggest that you use one of my examples to lessen your code and make it easier down the road if you change your table structure

[Jamiesw]

Thanks, where can i find your examples?

[ginerjm]

Not examples per se.  Just the ideas I suggested to use the existing arrays that are available to you instead of making needless variables only to insert them into an array over again.

For ex.

	array_push($page_arr['pages'], $row);
	

This one line can replace all that code you are doing in your loop.

And using fetchall instead of a fetch would replace the whole loop.

[gizmola]

I'm not a big fan of extract or anything that could make a bunch of odd variables when you can just use:  

'pagecontent' => html_entity_decode($row['pagecontent']),

 

With that said, I didn't see anything exceptionally broken in your code.  Probably the issue is that you aren't setting the HTTP Header to indicate you are returning json.  Before your echo:

 

header('Content-Type: application/json');

If something else is broken, you should have a message in your logs.  

 

[Jamiesw]

Hi there,

Thanks for everyones help, the code works properly including reducing it down and not use the extract etc.

The problems looks like the json_encode doesn't like the content within the Database, some of the pagecontent records contain html syntax for forms, i changed my sql to a different table and it worked as expected.

any ideas on getting the json_encode to handle html syntax. (see below of sample data in the pagecontent field).

 [pagecontent] => <p>We just need to ask you a few questions so we can get you the right answer</p><h2>What is your age?</h2>
<form>
<select id="verifyage" name="verifyage" class="dropdown-fields" required>
<option value="0">--Select your age--</option>
<option value="3A1">Under 16</option>
<option value="3A2">16 Or 17</option>
<option value="3A3">18 Or Over</option>
</select>
<input type="submit" name="btnsubmit" id="btnsubmit" class="button" onClick="gotonextpage($('#verifyage option:selected').val());">
<form>
<p> </p>

 

 

[Barand]

This worked for me once I had escaped the single quotes inside your "onclick"

$data = [
    'pagecontent' => '<p>We just need to ask you a few questions so we can get you the right answer</p><h2>What is your age?</h2>
                        <form>
                        <select id="verifyage" name="verifyage" class="dropdown-fields" required>
                        <option value="0">--Select your age--</option>
                        <option value="3A1">Under 16</option>
                        <option value="3A2">16 Or 17</option>
                        <option value="3A3">18 Or Over</option>
                        </select>
                        <input type="submit" name="btnsubmit" id="btnsubmit" class="button" onClick="gotonextpage($(\'#verifyage option:selected\').val());">
                        <form>
                        <p> </p>' 
         ];
                          
$j = json_encode($data);

 

[Jamiesw]

yeah i can use either htmlentities($pagecontent) or htmlspecialchars($pagecontent) and it displays all my data now as json. Now need to work out how to convert it back to html in javascript.

thanks for everyone's help.