SQL query returns incorrect value for ID column in

问题:

I am using the following SQL query to extract content from the tables posts, users and comments

When I run the query on the database it correctly returns the value of 14 for the 'ID' column in the result.

However using PHP's mysqli it is returning 1 and sometimes 2. Can anyone see any issues with the query? I've been staring at it for hours and I really don't want to do a separate query just to get the correct ID value.

SELECT * FROM posts 
LEFT OUTER JOIN comments ON posts.ID = comments.comment_post_id AND comments.comment_approved = 1
LEFT OUTER JOIN users ON users.ID = posts.post_author WHERE posts.ID = '14' AND posts.post_type = 'post' AND posts.post_status = 'publish'
ORDER BY comments.comment_ID DESC 

PHP Code:

 while ($thisResult = $result->fetch_array(MYSQLI_ASSOC)) {
    echo $thisResult['ID']; // returns 1
    echo $thisResult['post_title']; // returns the correct post title
 }

回答1:

Multiple tables in your query have ID column. Use field aliases instead of * in select. For example,

SELECT posts.id as post_id, ..... FROM posts ....

Or you can get the right id using $thisResult[0] (assuming id is the first column in posts table)