PDO changes query

I want to have result of my query as it was before I replaced db connection using PDO. How can I get the query as i t was before I implemented PDO?

This is my query:

$query = 
    "SELECT 
        `id_affirmation`, 
        `affirmation`, 
        `author`, 
        `user_rate`, 
        am.date, 
        am.time, 
        hua.date, 
        hua.time 
    FROM `affirmation_male` am 
    JOIN `history_user_affirmation` hua ON am.id_affirmation = ua.affirmation_id 
    WHERE hua.user_id = '" . $id_user . "' 
    ORDER BY 
        STR_TO_DATE(hua.date, '%d-%m-%Y') DESC, 
        hua.time DESC";

For some reason the result of query when I use PDO is i got date from affirmation_male. Do you know why?

Answer

Your query returns two columns that have the same name, hence PDO gets lost when it fetches the results; since each records is represented as an associative array, duplicate keys generate ambiguity (only one key will be retained).

You would need to alias those columns to remove ambiguity:

$query = 
    "SELECT 
        `id_affirmation`, 
        `affirmation`, 
        `author`, 
        `user_rate`, 
        am.date am_date, 
        am.time am_time, 
        hua.date AS hua_date, 
        hua.time AS hua_time
    FROM `affirmation_male` am 
    JOIN `history_user_affirmation` hua ON am.id_affirmation = hua.affirmation_id 
    WHERE hua.user_id = '" . $id_user . "' 
    ORDER BY 
        STR_TO_DATE(hua.date, '%d-%m-%Y') DESC, 
        hua.time DESC";

Notes:

• it would also be a good idea to prefix the first columns in the query with the alias of the table they belong to, as this makes the query more readable (and will avoid conflicts if ever these columns names were available in more than one table coming into play in the query)

• you could remove backticks to make the query more readable, as the column and table names that you are quoting do not seem to contain any special characters