phpmyadmin Question posted in
The official documentation can be found here.

MySQL select works in phpMyAdmin but not within PHP?

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

Tags:

2

Answers


  1. 0 
      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
    Login or Signup to reply.
  2. 0

    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

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search