PDO changes query

Question

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?

What were you using before, and how did you resolve the naming conflict between the two date columns? — Barmar
– Barmar, Commented Sep 18, 2019 at 21:40
WARNING: When using PDO you should be using prepared statements with placeholder values and supply any user data as separate arguments. In this code you have potentially severe SQL injection bugs. Never use string interpolation or concatenation and instead use prepared statements and never put $_POST, $_GET or any user data directly in your query. Refer to PHP The Right Way for general guidance and advice. — tadman
– tadman, Commented Sep 18, 2019 at 22:11

GMB · Accepted Answer · 2019-09-18 21:50:48Z

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

Collectives™ on Stack Overflow

PDO changes query

1 Answer 1

Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Related