0

I have an issue where Group By isn't working in my PHP code on Localhost, but it works when I run it as a query in terminal and mySQLworkbench. This is for an inventory function on a website.

This is the line of code in question:

SELECT i.id, i.itemID, ii.name, ii.category, ii.rarity, ii.image 
FROM inventory AS i 
LEFT JOIN items AS ii ON i.itemID = ii.itemID 
WHERE userID = 1 
GROUP BY i.itemID ORDER BY i.id DESC LIMIT 1, 5

What I've tried to do is run it in PHP the way we run queries with multiple results and output each line (with foreach).

The query runs fine in the PHP if I take the GROUP BY out, but unfortunately, I need the GROUP BY and I'll need it again in the future for other features.

I'm running mysql 5.7.21 Homebrew if that matters and PHP 5.6 (the site is a bit old).

The query runs on the site's production and dev servers.

edit Forgot to mention, I've tried running this code - didn't solve the issue:

SET sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));

edit This is the PHP code:

    $items     = database::retrieveAllRows( database::query("
      SELECT i.id, i.itemID, ii.name, ii.category, ii.rarity, ii.image 
         FROM inventory AS i LEFT JOIN items AS ii ON i.itemID = ii.itemID WHERE userID = %u 
        GROUP BY i.itemID ORDER BY i.id DESC LIMIT $limit, $pageLimit", $id) );

retrieveAllRows is one of our functions:

        public function retrieveAllRows ( array $res )
    {
            if ( $this -> validateResultSet ( $res ) === FALSE )
            {
                    return ( NULL );
            }

            return ( $res [ 0 ] -> fetchAll () );
    }
hungrykoala
  • 1,083
  • 1
  • 13
  • 28
kredgons
  • 21
  • 5
  • Can you post PHP code that execute this SQL? Including how you connect PHP with MySQL. – Zamrony P. Juhara Apr 27 '18 at 02:52
  • Do you have an output in `GROUP BY`? Or is it giving you an error? – Romeo Sierra Apr 27 '18 at 02:53
  • Show us the PHP code when executing the mentioned query and turn on the error reporting – hungrykoala Apr 27 '18 at 02:54
  • like PHP code that works or the PHP code in question? – kredgons Apr 27 '18 at 02:54
  • @Rom No error, just no output – kredgons Apr 27 '18 at 02:54
  • 1
    `GROUP BY` is for aggegating, but you aren't actually doing that. Please show us sample data. – Tim Biegeleisen Apr 27 '18 at 02:54
  • 1
    Now the point here is you are running a `GROUP BY` on a **PRIMARY KEY** field which is going to correspond to **one single tuple**. And you are not having any aggregate function. So what is it to aggregate there? – Romeo Sierra Apr 27 '18 at 02:54
  • PHP code that you use to execute the query. Something like this: `mysqli_query($your_query_here)` – hungrykoala Apr 27 '18 at 02:56
  • I've added the PHP code in question but it's pretty basic. Also, the query isn't mine - I just need it for something else I'm doing, and it's in my way. It's actually not on the Primary Key, the Primary Key is the 'id' field so this 'itemID' is a foreign key. – kredgons Apr 27 '18 at 02:57
  • @kredgons I would recommend running this in **phpMyAdmin** or something of that sort first and checking the output.. – Romeo Sierra Apr 27 '18 at 02:57
  • @RomeoSierra yeah, it works in both terminal and mySQLWorkbench just fine. It's just through the PHP that it fails – kredgons Apr 27 '18 at 02:58
  • Can you show us the content of this function `database::query` as it's there that the query is being executed – hungrykoala Apr 27 '18 at 02:59
  • @kredgons If you have the output there, then it's a problem with the query execution in the php script. By the way, are you getting the desired output when you run this in a client? – Romeo Sierra Apr 27 '18 at 03:00
  • @hungrykoala it's a really long function, can you maybe give me an idea of what to look for? – kredgons Apr 27 '18 at 03:02
  • @RomeoSierra yeah this just doesn't work with my localhost or my friend who has a similar set up - it does work for the other three people coding as well as on the live server, we haven't been able to figure out where the big difference is here and why some are failing and some aren't – kredgons Apr 27 '18 at 03:03
  • 1
    What **exactly** happens when you execute the query with the `GROUP BY`? You haven't stated at all how this _isn't working_. Do you have error reporting enabled and visible? What database library are you using; mysql, mysqli or PDO? – Phil Apr 27 '18 at 03:14
  • @Phil Nothing at all is happening. As I noted, I try to run it with foreach and output lines, so the error is simply that nothing is outputted UNLESS I remove the group by - then it works fine. There are no errors on the page or in my error log. The site uses a mysql database library. – kredgons Apr 27 '18 at 03:16
  • What is `$items` when you try? Is it an array or `null`? What is `database::query()` returning when you try? Have you attempted any debugging at all? – Phil Apr 27 '18 at 03:18
  • @Phil $items prints Array, query returns to retrieveAllRows which returns to $items which behaves as if it is empty in all its fields. – kredgons Apr 27 '18 at 03:23
  • This is like pulling teeth... What **exactly** is `$res[0]` as passed to `retrieveAllRows()`? You say you are using the deprecated (and removed in PHP7) _mysql_ library but it definitely does not have any `fetchAll()` methods. Where does that come from? – Phil Apr 27 '18 at 03:26
  • Phil, my guess is it's in that long function he mentioned. @kredgons try turning PHP errors on. Check [here](https://stackoverflow.com/questions/1053424/how-do-i-get-php-errors-to-display) – hungrykoala Apr 27 '18 at 04:10

0 Answers0