Jump to content

Doctrine fetches 160 results from a table when 3 are present

Stefany93

By Stefany93
in Third Party Scripts

 Share

Recommended Posts

Stefany93 Regular Member

Stefany93

Posted
Posted (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 by Stefany93
requinix Prolific Member

requinix

Posted
Posted

So if you go into a MySQL client program (like phpMyAdmin) and execute

SELECT * FROM reactions WHERE topic_id = 1
then 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.

  • Like 1
Stefany93 Regular Member

Stefany93

Posted
  • Author
Posted

So if you go into a MySQL client program (like phpMyAdmin) and execute

SELECT * FROM reactions WHERE topic_id = 1
then 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.

Stefany93 Regular Member

Stefany93

Posted
  • Author
Posted

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.

This thread is more than a year old. Please don't revive it unless you have something important to add.

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.

×
 Share
Go to topic listing
×
×
  • 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.