doumen5491 2015-06-14 09:24
浏览 57
已采纳

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

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
  • 写回答

1条回答 默认 最新

  • douguang9014 2015-06-14 09:41
    关注

    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.

    本回答被题主选为最佳回答 , 对您是否有帮助呢?
    评论

报告相同问题?

悬赏问题

  • ¥20 找驱动QTR2774-SU-IBM版本驱动
  • ¥15 NeRF的代码复现以及人工智能入门
  • ¥15 centos如何做一个可以让ikuai连接的openvpn
  • ¥15 Protege 中的Entities下面的界面删除了如何复原
  • ¥15 scrapy爬虫求帮
  • ¥15 imageEnView绘图问题
  • ¥15 关于#python#的问题:您好可以加您一下联系方式吗,在复现的时候确实有点问题难以解决
  • ¥15 联想电脑重装系统时无法发现硬盘
  • ¥15 MATLAB与UR10e实体机械臂建立通讯
  • ¥15 c++题需要快一点不用opencv