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 · Accepted Answer · 2013-06-26 00:39:22Z

3

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.

edited Jun 26, 2013 at 0:39

answered Jun 26, 2013 at 0:32

Sean

12.4k3 gold badges31 silver badges49 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Attis Over a year ago

Thank you for your answer Sean. It just gives me Fatal error: Call to undefined method Wall_Updates::fetchAll() on the $data = $this->FetchAll(); Any ideas? As I had tried that before

Sean Over a year ago

You need to do fetchAll() on your query $sth, not on your db connection $this->. So it should be $data = $sth->fetchAll();

showdev Over a year ago

Agreed +1. Alternately, use a while loop (like your mysql code) with fetch().

Attis Over a year ago

You're correct, perfect answer thank you. +1 & answer accepted.

Collectives™ on Stack Overflow

Query not displaying information : PDO, MYSQL, PHP

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related