Jump to content
Jamiesw

PDO Output to JSON

Recommended Posts

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!.'));
}

 

Share this post


Link to post
Share on other sites

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); 

 

Share this post


Link to post
Share on other sites

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..

Share this post


Link to post
Share on other sites

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?

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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.  

 

Share this post


Link to post
Share on other sites

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>

 

 

Share this post


Link to post
Share on other sites

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);

 

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.


×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.