MySQL查询不返回PHP中的值

Question

So i am running the same query on phpmyadmin and in my php code.

The query is :

SELECT comments.roomID,comments.message, comments.dateTimeSent, sender.fname ,sender.lname,receiver.fname, receiver.lname
FROM comments
INNER JOIN users as sender ON comments.senderID = sender.id
INNER JOIN users as receiver ON comments.receiverID = receiver.id
INNER JOIN chatRooms ON comments.roomID = chatRooms.id WHERE comments.roomID = 8;

when i run this directly from phpmyadmin panel the result i get is this

But when i run this in my php code i get this as the result:

Array
(
    [roomID] => 8
    [message] => Hello from mysql database
    [dateTimeSent] => 2018-03-16 11:04:03
    [id] => 23
    [fname] => pavlos
    [lname] => elpidorou
)
Array
(
    [roomID] => 8
    [message] => asdasd;asda
    [dateTimeSent] => 2018-03-16 11:21:30
    [id] => 25
    [fname] => Antreas
    [lname] => antoniou
)

the sender.fname, sender.lname, receiver.fname,receiver.lname are missing from the array

the code i use to execute the query and get the results is as follows

foreach ($chatRoomArray as &$room) {
            $roomID = $room['id'];

            $query = "SELECT comments.roomID,comments.message, comments.dateTimeSent,sender.id, sender.fname ,sender.lname,receiver.fname, receiver.lname
                      FROM comments
                      INNER JOIN users as sender ON comments.senderID = sender.id
                      INNER JOIN users as receiver ON comments.receiverID = receiver.id
                      INNER JOIN chatRooms ON comments.roomID = chatRooms.id WHERE comments.roomID = ".$roomID;


            $stmt = $this->conn->prepare($query);
            $result = $stmt->execute();

            $commentArray = array();

            if ($result) {
                $num = $stmt->rowCount();
                if ($num > 0) {
                    while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
                        extract($row);
                        print_r($row);
                        $comment = array(
                            "commentID" => $row['id'],
                            "message"=>$row['message'],
                            "dateTimeSent"=>$row['dateTimeSent'],
                            "senderFname"=>$row['sender.fname'],
                            "senderLname" => $row['sender.lname'],
                            "receiverFname" => $row['receiver.fname'],
                            "receiverLname" => $row['receiver.lname']
                        );

                    }
                }

            }
        }

Answer 1

PHP + MySQL won’t include the table name in a field name.

When you’re running your query, give them a name in the SELECT part.

SELECT `sender`.`fname` AS `sender_fname`, ...

Then it’ll be in your array as sender_fname. I would avoid using dots in your names.