mysql PHP PDO在单个POST请求上执行2个查询

Question

I'm using a standard apache2 LAMP configuration with mysql and php5, only thing I changed is my character encoding onto UTF8.

I'm making an AJAX call upon clicking an anchor (the article title), and want to run 2 queries. One gets the article, and the other the comments related to the article.

I found this post while browsing around, but so far haven't been able to implement what he's talking about. I get this message: Fatal error: Call to a member function nextRowset() on a non-object in /home/shooshte/Dropbox/PTC_php/db_queries/articles.php on line 45. I actually have no idea if this is the right way to do it.

The problem is that the first SELECT statement returns a single row (one article), while the second SELECT statement returns multiple rows (all the comments on that article), so I can't really join them into one statement.

anyway here's my code (I commented out the irrelevant parts):

<?php
error_reporting(E_ALL);
ini_set('display_errors', 1);

try {
    $hostname = "localhost";
    $username = "topdecka_admin";
    $password = "";

    $db = new PDO("mysql:host=$hostname;dbname=topdecka_PTC;charset=utf8",$username, $password);
    $db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

    /*if (!empty($_POST["searchword"])) {
        $searchword = $_POST["searchword"];
        $query = $db->prepare(
            'SELECT articles.title, articles.posted, articles.extract, authors.name, GROUP_CONCAT(categories.cat_name) AS cat_name
            FROM articles, authors, categories, article_categories 
            WHERE articles.author_id = authors.id
            AND articles.id = article_categories.article_id
            AND article_categories.category_id = categories.id
            AND ((title LIKE :searchword) OR (extract LIKE :searchword) OR (body LIKE :searchword) OR (name LIKE :searchword) OR (cat_name LIKE :searchword))'
            ); //end DB QUERY
        $query->execute(array(":searchword" => "%" . $searchword . "%"));
        $result = $query->fetchAll();
        //turns timestamp into integer
        for($i = 0; $i < count($result); ++$i) {
          $result[$i]['posted'] = strtotime($result[$i]['posted']);
        }
        echo json_encode($result);
        die(); */
    } 
    else if (!empty($_POST["title"])) {
        $title = $_POST["title"];
        $query = $db->prepare(
            "SELECT articles.title, articles.posted, articles.body, authors.name, authors.img, authors.bio, GROUP_CONCAT(categories.cat_name) AS cat_name
            FROM articles INNER JOIN authors ON articles.author_id = authors.id
            INNER JOIN article_categories ON articles.id = article_categories.article_id
            INNER JOIN categories ON article_categories.category_id = categories.id
            WHERE title LIKE :title; SELECT comment.user_id, comment.text, comment.article_id 
            FROM articles RIGHT JOIN comment ON articles.id = comment.article_id
            WHERE title LIKE :title; SELECT comment.user_id, comment.text, comment.article_id FROM articles RIGHT JOIN comment ON articles.id = comment.article_id;"
            ); //end DB QUERY
        $query->execute(array(":title" => $title));
        $result = $query->fetchAll();
        $result->nextRowset();
        $result[0]['posted'] = strtotime($result[0]['posted']);

        echo json_encode($result);
        die();
    }
    /*else {
        $query = $db->prepare(
            'SELECT articles.title, articles.posted, articles.extract, authors.name, GROUP_CONCAT(categories.cat_name) AS cat_name
            FROM articles, authors, categories, article_categories 
            WHERE articles.author_id = authors.id
            AND articles.id = article_categories.article_id
            AND article_categories.category_id = categories.id'
            ); //end DB QUERY
        $query->execute();
        $result = $query->fetchAll();
        //turns timestamp into integer
        for($i = 0; $i < count($result); ++$i) {
          $result[$i]['posted'] = strtotime($result[$i]['posted']);
        }
        echo json_encode($result);
        die();
    } */
} 
catch (PDOException $e) {
    echo "Error!: " . $e->getMessage() . "<br/>";
    die();
}
?>

and here's the jQuery that makes the POST request:

    $(".article-box h1 a").click(function(e) {
        e.preventDefault();
        var title = "title="+ $(this).text();
        $.post(
            "db_queries/articles.php",
            title,
            function(data) {
                console.log(data);
                var response = JSON.parse(data);
                // formats article body
                var articleBody = response[0].body.trim();
                articleBody = articleBody.replace(/(
|
|)/gm,"<br>");
                // formats date
                var posted = new Date((response[0].posted * 1000));
                $("#articles-feed").empty();
                $("#articles-feed").append(
                    '<div class="article-box"><h1>' +response[0].title+
                    '</h1><h3>' + posted.toLocaleDateString()+
                    '</h3><p>' + articleBody +
                    '</p><button>Back to articles</button></div>'
                ); //end article append
                var authorHTML = (
                    '<img id="author-img" src="' + response[0].img +
                    '"><h2 id="author-name">' + response[0].name +
                    '</h2><p>' + response[0].bio +
                    '</p>');
                $("#author").append(authorHTML);
                //back to articles button
                $("#articles-feed button").click(function(e) {
                    e.preventDefault();
                    window.location.assign('articles');
                })
            } //end response function
        ); //end POST request
    }) //end article title click function

Answer 1

There are two problems with what you have now: You are calling nextRowset() on the array that contains your data and you are not getting the data from the second row set.

You need something like:

$result = $query->fetchAll();
// now $result contains your row with data

// get the next row set from the query
$query->nextRowset();
^^^^^^

// get the results of the second row set
$result2 = $query->fetchAll();

// You would have to see how you want to send both result sets...
echo json_encode( array('article' => $result, 'comments' => $result2) );
die();

Note that you would have to adapt your javascript to accommodate for the data structure.