Stefany93 Posted November 13, 2015 Share Posted November 13, 2015 (edited) Please note this is a DEV version, so I know there are security glitches. I am currently developing it. Okay, so I have this DB table reactions and I want to fetch all the records inside that match a certain topic_id column. I am using Doctrine DBAL I am using SQLite as a DB. So I wrote this code: public function displayComment() { $row = []; $query = $this->queryBuilder ->select('*') ->from('reactions') ->where('topic_id = '.$this->topicId) ->execute(); // echo $sql=$query->getSQL(); while($row = $query->fetch(PDO::FETCH_ASSOC)) { $row2[] = $row['id']; } return $row2; } I set $topic_id to always equal 1 for testing purposes. And the freaking thing display me 96 results, even tho there are only three in the DB! Array ( [0] => 35 [1] => 36 [2] => 34 [3] => 35 [4] => 36 [5] => 34 [6] => 35 [7] => 36 [8] => 34 [9] => 35 [10] => 36 [11] => 34 [12] => 35 [13] => 36 [14] => 34 [15] => 35 [16] => 36 [17] => 34 [18] => 35 [19] => 36 [20] => 34 [21] => 35 [22] => 36 [23] => 34 [24] => 35 [25] => 36 [26] => 34 [27] => 35 [28] => 36 [29] => 34 [30] => 35 [31] => 36 [32] => 34 [33] => 35 [34] => 36 [35] => 34 [36] => 35 [37] => 36 [38] => 34 [39] => 35 [40] => 36 [41] => 34 [42] => 35 [43] => 36 [44] => 34 [45] => 35 [46] => 36 [47] => 34 [48] => 35 [49] => 36 [50] => 34 [51] => 35 [52] => 36 [53] => 34 [54] => 35 [55] => 36 [56] => 34 [57] => 35 [58] => 36 [59] => 34 [60] => 35 [61] => 36 [62] => 34 [63] => 35 [64] => 36 [65] => 34 [66] => 35 [67] => 36 [68] => 34 [69] => 35 [70] => 36 [71] => 34 [72] => 35 [73] => 36 [74] => 34 [75] => 35 [76] => 36 [77] => 34 [78] => 35 [79] => 36 [80] => 34 [81] => 35 [82] => 36 [83] => 34 [84] => 35 [85] => 36 [86] => 34 [87] => 35 [88] => 36 [89] => 34 [90] => 35 [91] => 36 [92] => 34 [93] => 35 [94] => 36 [95] => 34 ) If I change the query to fetch the data from the posts table with the ID number of one, it works perfectly. I have no idea why it is being such a baby for the reactions table. Schema for the reactions table: BEGIN TRANSACTION; ---- -- Table structure for reactions ---- CREATE TABLE "reactions" ('id' INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, 'comment' TEXT, 'user_id' TEXT, 'topic_id' INTEGER, 'date_posted' INTEGER); ---- -- Data dump for reactions, a total of 3 rows ---- INSERT INTO "reactions" ("id","comment","user_id","topic_id","date_posted") VALUES ('34','qwdqdqdqwdqdqdqdqdqdqdqdqdqdqwdqdq','1','1','1447356567'); INSERT INTO "reactions" ("id","comment","user_id","topic_id","date_posted") VALUES ('35','qdwdqwdqwdqwdqwdqdqwdqwd','1','1','1447357049'); INSERT INTO "reactions" ("id","comment","user_id","topic_id","date_posted") VALUES ('36','qdwdqwdqwdqwdqwdqdqwdqwd','1','1','1447357054'); COMMIT; And now the whole Posts class: <?php class Posts { protected $db; protected $queryBuilder; // private $topicId = $_GET['id']; protected $topicId = 1; public function __construct($db) { $this->db = $db; $this->queryBuilder = $this->db->createQueryBuilder(); } public function listTopics() { $query = $this->queryBuilder ->select('posts_id, posts_title, posts_date, author_id') ->from('posts') ->where('posts_categories_id = 1') ->execute(); return $query->fetchAll(); } public function countComments() { $query = $this->queryBuilder ->select('comments_id') ->from('comments') ->where('comments_topic_id = 1') ->execute(); return $query->fetchAll(); } public function displaySingleTopic() { $query = $this->queryBuilder ->select('*') ->from('posts') ->where('posts_id = '.$_GET['id']) ->execute(); return $query->fetchAll(); } public function insertComment($comment_data) { extract($comment_data); $user_id = 1; $query = $this->queryBuilder ->insert('reactions') ->values( array( 'comment' => '?', 'user_id' => '?', 'topic_id' => '?', 'date_posted' => "STRFTIME('%s','now')" ) ) ->setParameter(0, $comment) ->setParameter(1, $user_id) ->setParameter(2, $this->topicId) ->execute(); } public function displayComment() { $row = []; $query = $this->queryBuilder ->select('*') ->from('reactions') ->where('topic_id = '.$this->topicId) ->execute(); // echo $sql=$query->getSQL(); while($row = $query->fetch(PDO::FETCH_ASSOC)) { $row2[] = $row['id']; } return $row2; } } single_topic.php where I am calling the method: <?php include 'html/header.php'; $profile = new Profile($conn); // Select all from a single post $post = new Posts($conn); // Turn the array keys into variables like ['title'] => $title extract($post->displaySingleTopic()[0]); $profile->author_id = $author_id; if(isset($_POST['comment'])) { $post->insertComment($_POST); } echo '<pre>'; print_r($post->displayComment()); ?> <!-- Post data --> <script> window.onload = function() { var height_of_topic = document.getElementById('general_topic').offsetHeight; document.getElementById('poster_info').style.height = height_of_topic+'px'; } </script> <article id="general_topic"> <h1 id="single_topic"><a href="post/{$posts_id}-$post_title" class="title_link"><?php echo $posts_title ?></a></h1> <section id="poster_info"> <ul> <li> <a href="profile.php?id=<?php echo $author_id?>"> <img src="<?php echo AVATAR_DIR.$profile->avatar();?>" width="140" height="130" /></li> </a> <li> <a href="profile.php?id=<?php echo $author_id?>"> <?php echo $profile->username(); ?> </a> </li> <li><?php echo $profile->country(); ?></li> <li><?php echo $profile->number_of_posts(); ?></li> </ul> </section> <!-- <hr></hr> --> <ul id="post_data"> <li id="post_date"> <a href="#"><?php print date('M d Y',$posts_date); ?></a> </li> <hr> <li id="post_category"> <a href="category/<?php //echo $categories_id ?>-<?php //echo clean_url($categories_name); ?>"><?php //print $categories_name ?></a> </li> <li id="post_comments"> <a href="/post/<?php //echo $posts_id ?>-<?php //echo $posts_title?>#comments_post_id<?php //echo $posts_id ?>"> <?php //echo $display_number_comments ?> </a> </li> </ul> <p> <?php echo nl2br($posts_contents); ?> </p> <!-- End of post data --> </article> <!-- Comment form --> <br> <br> <br> <br> <br> <br> <br> <br> <br> <br> <form method="post" action="" id="comment_form" > <fieldset> <?php if(!$profile->isLoggedIn()) { ?> <label> Name:<span>*</span> <input type="text" class="long_input_field" name="commentator" id="text_name" maxlength="50" /> </label> <label> Website: <input type="text" class="long_input_field" name="website" id="text_name" maxlength="50" /> </label> <?php } ?> <label> What's your opinion...:<br /> </label> <textarea rows="10" cols="50" id="text_comment" name="comment"></textarea> <br> <input type="submit" name="commented" id="comments_button" value="Publish Comment" /> <!-- <input type="hidden" name="post_id" value="<?php //print $this_fuking_post; ?>" /> --> <label for="" class="secret"> Do not populate this field<input type="text" name="url" /></label> </fieldset> <!-- End of comment form --> <?php /* Start the foreach loop to display all comments related to the article. The function display_comments is in functions.php document. The function returns an array that holds absolutely all comments for that article. */ /* if(is_array(display_comments_data($post_id, $db ))) { foreach(display_comments_data($post_id, $db ) as $comment) {*/ ?> <div class="comment" id="comments_post_id<?php //print $comment['comments_post_id'] ?>"> <h3> <?php /*echo $comment['comments_commentator'];*/ ?> </h3> <h5><?php /*echo date('d F Y',$comment['comments_date']);*/ ?></h5> <p> <?php /*echo $comment['comments_comment'];*/ ?> </p> </div> <?php /* } }*/ ?> </form> <?php include 'html/footer.php'; ?> Thank you SO MUCH in advance! Please help! Edited November 13, 2015 by Stefany93 Quote Link to comment Share on other sites More sharing options...
requinix Posted November 14, 2015 Share Posted November 14, 2015 So if you go into a MySQL client program (like phpMyAdmin) and execute SELECT * FROM reactions WHERE topic_id = 1then you only get three rows? Dump out or log the value of $row2 somewhere. If it shows three rows then you're looking in the wrong place, and if it shows more than three rows then Doctrine is doing something spooky. 1 Quote Link to comment Share on other sites More sharing options...
Stefany93 Posted November 14, 2015 Author Share Posted November 14, 2015 So if you go into a MySQL client program (like phpMyAdmin) and execute SELECT * FROM reactions WHERE topic_id = 1then you only get three rows? Dump out or log the value of $row2 somewhere. If it shows three rows then you're looking in the wrong place, and if it shows more than three rows then Doctrine is doing something spooky. Thank you for the reply. Yes, I went to PhpLiteAdmin, and it showed three rows with the above query. The spooky thing is, that even if it is a bug of Doctrine, why the same query works excellent with the posts table but freaks out with the reactions one? Maybe you are right, Doctrine internal bug. Idk what to do, tho. Quote Link to comment Share on other sites More sharing options...
Stefany93 Posted November 14, 2015 Author Share Posted November 14, 2015 Solved it. Apperantly, Doctrine stores all the queries it makes in the variable that made it, in my case protected $queryBuilder; Thats why it displays so many rows. I executed the displayComment function before DisplaySIngle Post and again, the single post ones had too many rows, since it memorized the executed stuff from displayComment So, what I did was to mimic the behaviour of $queryBuilder into a method inside the class like this: public function newQuery() { return $this->queryBuilder = $this->db->createQueryBuilder(); } So every time we call $query_Builder, it gets $this->db->createQueryBuilder(); brand new, like this: public function displayComment() { $row = []; $query = $this->newQuery() ->select('*') ->from('reactions') ->where('topic_id = '.$this->topicId) ->execute(); // echo $sql=$query->getSQL(); while($row = $query->fetch(PDO::FETCH_ASSOC)) { $row2[] = $row['id']; } var_dump( $row2 ); } And the whole Posts.php class: <?php class Posts { protected $db; protected $queryBuilder; protected $topicId = 1; public function __construct($db) { $this->db = $db; } public function newQuery() { return $this->queryBuilder = $this->db->createQueryBuilder(); } public function listTopics() { $query = $this->queryBuilder ->select('posts_id, posts_title, posts_date, author_id') ->from('posts') ->where('posts_categories_id = 1') ->execute(); return $query->fetchAll(); } public function countComments() { $query = $this->queryBuilder ->select('comments_id') ->from('comments') ->where('comments_topic_id = 1') ->execute(); return $query->fetchAll(); } public function displaySingleTopic() { $query = $this->newQuery() ->select('*') ->from('posts') ->where('posts_id = '.$_GET['id']) ->execute(); return $query->fetchAll(); } public function insertComment($comment_data) { // extract($comment_data); $user_id = 1; $query = $this->newQuery() ->insert('reactions') ->values( array( 'comment' => '?', 'user_id' => '?', 'topic_id' => '?', 'date_posted' => "STRFTIME('%s','now')" ) ) ->setParameter(0, $comment) ->setParameter(1, $user_id) ->setParameter(2, $this->topicId) ->execute(); } public function displayComment() { $row = []; $query = $this->newQuery() ->select('*') ->from('reactions') ->where('topic_id = '.$this->topicId) ->execute(); // echo $sql=$query->getSQL(); while($row = $query->fetch(PDO::FETCH_ASSOC)) { $row2[] = $row['id']; } var_dump( $row2 ); } } Remember people, always use a fresh variable for multiple queries if you are using the QueryBuilder of Doctrine DBAL. @requinix, many thanks for your help. Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.