MySQL select works in phpMyAdmin but not within PHP?

Question

I'm using MySQL 5.7 and PHP 7.4.

The following select statement runs perfectly fine in phpMyAdmin and returns a row, as I would expect.

select b.price,sum(ifnull(i.itemamount,0)) totalpaid,b.loyaltypoints
from bookings b 
    left join paymentitems i on i.bookingid = b.id
where b.id = 214

But if I try to run this exact same statement from within PHP using PDO, I get a 500 error. And yes, I've confirmed that the value I'm binding to :bookingid is 214.

$db->query("select b.price,sum(ifnull(i.itemamount,0)) totalpaid,
                   b.loyaltypoints
              from bookings b 
              left join paymentitems i on i.bookingid = b.id
             where b.id = :bookingid");

$db->bind(":bookingid",$bookingid);

I've narrowed the problem down to this part

sum(ifnull(i.itemamount,0)) totalpaid

If I remove the sum, then it works. At least in the sense that it considers it valid SQL and it runs. Of course I don't get the result I want because I need the sum value.

ifnull(i.itemamount,0) totalpaid

So I guess this is a two part question. Most importantly, how can I make this work, but also, why does it not work? This boggles my mind.

EDIT: I should add, since there seems to be a lot of confusion about my syntax, that I'm using a database class to prepare and bind, so when I say $db->query() this is actually doing a prepare. And the $db->bind() is doing a bindValue. I've been using this class for years on thousands of other statements and have never had this problem before so I know it works just fine.

EDIT: Found this error in the PHP logs: PHP Fatal error: Uncaught PDOException: SQLSTATE[42000]: Syntax error or access violation: 1140 In aggregated query without GROUP BY, expression #1 of SELECT list contains nonaggregated column 'benji_prod.b.price'; this is incompatible with sql_mode=only_full_group_by in

Missing information!!! `where b.id = :bookingid` required data binding to that variable? So how is that done — RiggsFolly, May 14 '21 at 19:22
Do you get errors message? Did you look at the PHP Error log — RiggsFolly, May 14 '21 at 19:22
I'm binding the data like this: $db->bind(":bookingid",$bookingid);. I've added it to the question but I can't imagine how that would be relevant when changing the select statement itself "fixes" it. — Vincent, May 14 '21 at 19:30
As to the edit, then it's not really PDO even though the method names are the same. Why would you wrap PDO in another class? — AbraCadaver, May 14 '21 at 19:35
RiggsFolly, I had not checked the error log but at your suggestion I did. I found this: PHP Fatal error: Uncaught PDOException: SQLSTATE[42000]: Syntax error or access violation: 1140 In aggregated query without GROUP BY, expression #1 of SELECT list contains nonaggregated column 'benji_prod.b.price'; this is incompatible with sql_mode=only_full_group_by in — Vincent, May 14 '21 at 19:37
AbraCadaver I'm using the class because it simplifies the syntax a lot. I like it. — Vincent, May 14 '21 at 19:38
So add a `GROUP BY` or https://stackoverflow.com/questions/23921117/disable-only-full-group-by — AbraCadaver, May 14 '21 at 19:40
I tried adding "group by i.itemamount" but that did not help. Am I grouping on the wrong column? EDIT: I think I got it to work. I included all the columns in the group by and that seems to have done the trick. group by i.itemamount,b.price,b.loyaltypoints But why would this not be required in phpMyAdmin? Is it not using the same sql_mode? What's the default and which should I use? — Vincent, May 14 '21 at 19:42
You should use strict sql mode, which includes only full group by. Your sql statement is not a valid one. — Shadow, May 14 '21 at 20:04

score 1 · Answer 1 · answered May 14 '21 at 20:35

1

  Working Code:
  -------------
  $servername = "localhost";
  $username = "root";
  $password = "";

  $bookingid=1;
  
  $conn = new PDO("mysql:host=$servername;dbname=test", $username, $password);
  
  $sth = $conn->prepare('select b.price,sum(ifnull(i.itemamount,0)) totalpaid,b.loyaltypoints from bookings b left join paymentitems i on i.bookingid = b.id where b.id = :bookingid');// if you change "prepare" with "query" it will show error
 
  $sth->bindParam(':bookingid',$bookingid);
  
  $sth->execute();
  
  $result = $sth->fetchAll();
  
  var_dump($result);     


    Mistakes as per  my opinion:
    ----------------------------
    $sth = $conn->query('select b.price,sum(ifnull(i.itemamount,0)) totalpaid,b.loyaltypoints from bookings b left join paymentitems i on i.bookingid = b.id where b.id>0');//here there is no binding values, it will work
    
    $sth = $conn->query('select b.price,sum(ifnull(i.itemamount,0)) totalpaid,b.loyaltypoints from bookings b left join paymentitems i on i.bookingid = b.id where b.id=:bookingid');//it will show error, since there is binding values
    
    $db->bind(":bookingid",$bookingid);-> incorrect syntax
    
    $sth->bindParam(':bookingid',$bookingid); -> correct syntax

answered May 14 '21 at 20:35

user27976

149
1
7

this will not work, because he has ONLY_FULL_GROUP_BY activated, so the query will always fail – nbk May 14 '21 at 20:45
It worked for me . I received following output: array(1) { [0]=> array(6) { ["price"]=> string(3) "100" [0]=> string(3) "100" ["totalpaid"]=> string(2) "45" [1]=> string(2) "45" ["loyaltypoints"]=> string(2) "20" [2]=> string(2) "20" } } – user27976 May 15 '21 at 07:12
Se3e my link in my answer and the error message, if you don't have ONLY_FULL_GROUP_BY enable this will run, but as Resultset aren't ordered by nature, you get a result, that can be wrong o not wanted – nbk May 15 '21 at 08:41
see this link 1. https://www.dropbox.com/s/oh84j2j78huxh70/screen1.png?dl=0,2. https://www.dropbox.com/s/d7qynoj0wnw42m5/screen2.png?dl=0,3. https://www.dropbox.com/s/kohhmlwaq1j65n3/screen3.png?dl=0. I saw your link, i am not sure what is issue? – user27976 May 15 '21 at 14:51
please check the dbfiddle below, and remove the Min around points and sum around loyaltypoints and run it again and you will find same error message, the basic problem, wuthpu group by you sum will only return 1 row amd mysql wants to know what he should do with the pother columns – nbk May 15 '21 at 18:03
select * from bookings (or) select * from paymentitems showing empty can u add some data. So that i will check the process! – user27976 May 15 '21 at 18:52
I don't see what for it seems straight forward,but added a row – nbk May 15 '21 at 19:10
I believe, In mysql, group by will accomplish the result, but has no meaning for the purpose. If we do same query in sql server management studio, it will not work. They follow some strict rules if it has purpose. – user27976 May 16 '21 at 07:39

nbk · Accepted Answer · 2021-05-15T19:09:36.737

The error message states that you need all Columns to have a aggregation function like below.

But i think you need to rethink your query and show us what you really want to accomplish, with your query

It seems, that phpmyadmin ignores the only_full_group_by of the server. But the solition here to disable it, isn't the right choice, write a correct query, that delivers the correct result

CREATE tABLE bookings (id int,price DECIMAL(8,2),loyaltypoints int)

INSERT INTO bookings VALUES (214,12.12,1)

CREATE TABLE paymentitems (bookingid int,itemamount int)

INSERT INTO paymentitems VALUES (214,10.11),(214,2.01)

select MIN(b.price),sum(ifnull(i.itemamount,0)) totalpaid,SUM(b.loyaltypoints)
from bookings b 
    left join paymentitems i on i.bookingid = b.id
where b.id = 214

MIN(b.price) | totalpaid | SUM(b.loyaltypoints)
-----------: | --------: | -------------------:
       12.12 |        12 |                    2

db<>fiddle here

MySQL select works in phpMyAdmin but not within PHP?

2 Answers2