Query not displaying information : PDO, MYSQL, PHP

Question

I've been banging my head over this simple PDO query for hours. What is the correct way to fetch this type of information from MySQL multiple databases. In this query should Fetch or FetchAll be used? as It's querying multiple databases.

Below is my attempt to convert the deprecated MySQL to PDO.

MySQL.

    public function Comments($post_iD) {
        $query = mysql_query("
                                SELECT C.com_id, C.uid_fk, C.comment, C.created, U.username 
                                FROM comments C, users U 
                                WHERE U.status = '1' 
                                AND C.uid_fk = U.uiD 
                                AND C.msg_id_fk = '$msg_id'
                                ORDER by C.com_id"); or die(mysql_error());

            while( $row = mysql_fetch_array($query) )
                $data[] = $row;
            if( !empty( $data ) ){
                return $data;
            }
    }

PDO:

    PUBLIC FUNCTION Comments( $post_iD ){
        $sth = $this->db->prepare("
                                SELECT C.com_id, C.uid_fk, C.comment, C.created, U.username 
                                FROM comments C, users U 
                                WHERE U.status = '1' 
                                AND C.uid_fk = U.uiD 
                                AND C.msg_id_fk = ?
                                ORDER by C.com_id");

        $sth->execute(array($post_iD));

        $data = $this->fetch();
        return $data;
        }
    }

This is how I'm displaying the database.

    <?php   
        $commentsarray = $Wall->Comments( $post_iD );

        if( $commentsarray ){
            foreach($commentsarray as $data){
                $com_id     =   $data['com_id'];
                $comment    =   tolink(htmlcode($data['comment'] ));
                $time       =   $data['created'];
                $mtime      =   date("c", $time);
                $username   =   $data['username'];
                $com_uid    =   $data['uid_fk'];
                $cface      =   $Wall->Profile_Pic($com_uid);
    ?>
                <div class="stcommentbody" id="stcommentbody<?php echo $com_id; ?>">
                    <div class="stcommentimg">
                        <img src="<?php echo $cface;?>" class="small_face" alt=<?php echo $username;?>">
                    </div> 

                    <div class="stcommenttext">
                        <?php echo clear($comment); ?>
                    </div>
            </div>
<?php
        } 
    }
?>

Sean Sean · Accepted Answer · 2013-06-26T00:32:44

You want (using fetchAll() on your query $sth)

$data = $sth->fetchAll();

instead of (fetch() on your db connection $this->)

$data = $this->fetch();

as
fetch() Fetches the next row from a result set
where
fetchAll() Returns an array containing all of the result set rows.

Query not displaying information : PDO, MYSQL, PHP

1 Answers