ajax error when returning multiple rows of data

Question

I am trying to return multiple rows of data from my database through an AJAX call. It successfully returns one row, but with more than one row I get an error even though it successfully retrieves the data. I have narrowed it down to a server side issues which most likely lies in my PHP. Here is the PHP function I am using:

    public function get_case_studies($conn) {

    $v_sql_str = <<< END_SQL
        SELECT 
            name,
            title,
            content,
            location
        FROM case_studies
        WHERE is_active_flag = 1 AND is_deleted_flag = 0  
    END_SQL;

    try {
        $sth = $conn->prepare($v_sql_str);
        $sth->execute();
        }
        catch (PDOException $e) {
            $v_errormsg=$e->getMessage();
            return <<< END_HD
            {   
                "status":"FAIL" ,
                "error": "db_exception: Could not select case studies from database",
                "errorno": "1003" ,
                "errortype":  "SYS",
                "usermsg": "SQL=$v_sql_str, Error = $v_errormsg"
            }
    END_HD;
    }

    $v_json = "\"data\" : [";
    $n=0;
    $v_QT="\"";         
    while ($result = $sth->fetch(PDO::FETCH_OBJ)) {

        if ($n > 0) { $v_json = $v_json . ",";};
        $v_flags = "";

        $v_json = $v_json . "{ "
        . $v_QT .   "name" .                    $v_QT . ":" .       $v_QT . $result->name .                                 $v_QT . "," 
        . $v_QT .   "title" .                   $v_QT . ":" .       $v_QT . $result->title .                                $v_QT . ","
        . $v_QT .   "content" .                 $v_QT . ":" .       $v_QT . base64_decode($result->content) .               $v_QT . ","
        . $v_QT .   "location" .                $v_QT . ":" .       $v_QT . $result->location .                             $v_QT 
        . "}" ;
        $n++;
    }
    $v_json = $v_json . "] ";

    $sth->closeCursor();

    $v_json1 = "{ \"status\" : \"OK\", " . $v_json . " }";
    return $v_json1;

}

Here is my AJAX Call:

            $.ajax({
            type: "POST",
            url: "ajax.php",
            data: { module: "case_studies.php", action: "get_case_studies" }, 
            success: function(json_data) {
                ui_case_studies.case_list = json_data.data;
                alert(JSON.stringify(json_data.data));
            },
            error: function (a, b, c) {
                console.log(JSON.stringify(a));
                alert("Something went wrong while retreiving the case studies, please contact your database administrator.");
                //alert(JSON.stringify(a));
                //alert(b);
                //alert(c);
            }
        });

Log your JSON object and check if it's valid. Probably the problem is there. Why not using json_encode, by the way? — enenen
– enenen, Commented Feb 27, 2013 at 9:14
I'm still learning and pulling from old projects so that I why I am not using json_encode at the moment, I have already logged the JSON and run it through jsonlint and it came back as valid JSON, could you possibly help with an example of how I could use json_encode here? Thanks! Edit: All of the data is being returned correctly, and I get no error when only one row of data comes back, however even with the correctly returned data I get an error through the $.ajax call — JLeslie
– JLeslie, Commented Feb 27, 2013 at 9:16

enenen · Accepted Answer · 2013-02-27 10:01:00Z

1

It's better to use json_encode when creating your JSON. Also be sure whether you are parsing correctly the returned data in your Ajax' onSuccess part.

PHP part:

$result = array(); 
$i = 0;

while ($result = $sth->fetch(PDO::FETCH_OBJ)) {
    $result[$i]['name'] = $result->name;
    $result[$i]['title'] = $result->title;
    $result[$i]['content'] = base64_decode($result->content);
    $result[$i]['location'] = $result->location;
    $i++;
}

return json_encode($result);

JS part:

new Ajax.Request(url,
{
    method: 'post',
    onSuccess: function(transport) 
    {
        var response = transport.responseText || false;

        if (response !== false) {
            var result = JSON.parse(response);

            alert(result[1]['name']); // etc.
        }
    }
});

Edit:

You doesn't provide additional information. There is an error is not a helpful answer. So, here is some stuff which may help you when debugging.

PHP response format:

[
  {
    "name": "name0",
    "title": "title0",
    "content": "content0",
    "location": "location0"
  },
  {
    "name": "name1",
    "title": "title1",
    "content": "content1",
    "location": "location1"
  },
  {
    "name": "name2",
    "title": "title2",
    "content": "content2",
    "location": "location2"
  }
]

Parsing:

var response = '[{"name":"name0","title":"title0","content":"content0","location":"location0"},{"name":"name1","title":"title1","content":"content1","location":"location1"},{"name":"name2","title":"title2","content":"content2","location":"location2"}]';
var result = JSON.parse(response);

alert(result[1]['name']);

edited Feb 27, 2013 at 10:01

answered Feb 27, 2013 at 9:29

enenen

1,9772 gold badges17 silver badges34 bronze badges

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

4 Comments

JLeslie Over a year ago

I had the same issues after changing it over to json_encode, I have added my ajax call to the initial post.

enenen Over a year ago

I edited my answer. Check if the PHP script is returning the correct result. Check if the same Json is received correctly from the JavaScript.

JLeslie Over a year ago

I figured it out, I feel stupid. The error was with one of the rows of content data. I had copied my clients verbage from a word document and it had a blank line that was brought across causing the parse error... Thank you for all of your help!

enenen Over a year ago

You're welcome. It's mandatory to validate the user input. Never trust it!

Collectives™ on Stack Overflow

ajax error when returning multiple rows of data

1 Answer 1

PHP part:

JS part:

Edit:

PHP response format:

Parsing:

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

PHP part:

JS part:

Edit:

PHP response format:

Parsing:

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related