-1

I have two statements: one is written with mysql_query, another with PDO:

mysql_query:

$sql = $mysql_query("SELECT `user_report`.* FROM `user_report` LEFT JOIN `user` ON 
`user_report`.`user_id` = `user`.`user_id` WHERE `user`.`userBlocked` = 0 GROUP BY 
`user_id` ORDER BY `reports` DESC, `user_report`.`timestamp` ASC");

PDO prepared:

$sql = $pdo->prepare("SELECT `user_report`.* FROM `user_report` LEFT JOIN `user` ON 
`user_report`.`user_id` = `user`.`user_id` WHERE `user`.`userBlocked` = 0 GROUP BY 
`user_id` ORDER BY `reports` DESC, `user_report`.`timestamp` ASC");

$sql->execute();

PDO doesn't work with GROUP BY user_id.

Reason I need it is if two results come with the same user_id I want to show it as one.

How is it possible that PDO doesn't allow GROUP BY function ... in MySQL statement is there any substitute to that?

Thank you.

ekashking
  • 387
  • 6
  • 19
  • Your group by usage is invalid `SELECT * ... GROUP BY` column is in general a bad query.. Provid the error message it does not work is not a error message besides that also provide example data and expected results – Raymond Nijland Jun 24 '21 at 08:09
  • It might not be PDO at all.. it might be MySQL mode `ONLY_FULL_GROUP_BY`. Have you try to run the query on the same server your php is connecting to? – FanoFN Jun 24 '21 at 08:43
  • Well... I'm confused ... what's the definition of a BAD QUERY that actually WORKS every single time, cuz I used it on so many occasions before – ekashking Jun 24 '21 at 08:47
  • 1
    `GROUP BY` is used to divide the query result into groups into which you can then apply aggregate functions (`min`, `max` etc.). In your query, you do not have any aggregate functions so the use of `GROUP BY` does not make sense. Earlier versions of MySQL allowed this false use of `GROUP BY` by default, but newer ones (since 5.7.5) produce an error by default. What is it that you are trying to achieve with the `GROUP BY`? – slaakso Jun 24 '21 at 10:01
  • It looks like you might have PDO error reporting disabled. Read https://stackoverflow.com/questions/32648371/my-pdo-statement-doesnt-work – Dharman Jun 24 '21 at 10:26
  • The actual error seems to be that `user_id` is ambiguous in your query. You need to specify the table – Dharman Jun 24 '21 at 10:27
  • use GROUP BY `user_report.user_id` instead GROUP BY `user_id` – Yogendra Jun 24 '21 at 10:33

1 Answers1

-2

Try this

$sql = $pdo->prepare("SELECT user_report.* FROM `user_report` LEFT JOIN `user` ON 
user_report.user_id = user.user_id WHERE user.userBlocked = 0 GROUP BY 
user.user_id DESC, user_report.timestamp ASC");
$sql->execute();

changes i did `` removing on selecting tables and GROUP BY is pointing to which can be (user.user_id or user_report.user_id) you need to confirm? AND ORDER BY (reports) is pointing to ? so i removed ?

You try the above sql, and provide your output if there is any error.

Ajmal PraveeN
  • 414
  • 8
  • 16
  • This worked: `$sql = $pdo->prepare("SELECT user_id FROM user_report GROUP BY user_id");` But now I'm confused about using `` marks – ekashking Jun 24 '21 at 09:31
  • `` the marks which provided by phpmyadmin or manual marks mostly wont work with JOIN :) i tried it earlier it didn't worked. If this helpful please upvote. – Ajmal PraveeN Jun 24 '21 at 09:38