Php7 & group by

#1

Hi All,

In my transfered application (from PHP 5+ to PHP7), I have a problem with the following SQL regarding GROUP BY. Throughout my app I have many similar SELECT statement so one fix with solve them all (I think).

If I remove the “GROUP BY week_commence, client_id, location_key, user_key”, the code works. If I try just one field, ie, “GROUP BY week_commence” it doesn’t like it. The wholw GROUP BY has to be removed.

There are a number of post/tutorials regard the php7/group by problem, but I don’t follow the code correction I need to make.

The code starts with “SELECT * FROM tbl_timesheet LEFT JOIN…” I have checked tbl_timesheet and the four fields in the GROUP BY section all exist In that table.

Regards

Gary

===================================================
$sql=“SELECT * FROM tbl_timesheet LEFT JOIN tbl_location ON tbl_timesheet.location_key = tbl_location.address_id
LEFT JOIN tbl_user ON tbl_timesheet.user_key = tbl_user.user_id
WHERE user_key = ‘$search_user_id’ $sql_add_prefix $sql_search GROUP BY week_commence, client_id, location_key, user_key ORDER BY submitted_date, client_id, location_key ASC LIMIT $limit_start, $limit_end”;

#2

What do you mean it doesn’t like it? Do you get an error?

#3

Sorry. I meant I get the same error as if I had the complete four field GROUP BY statement.

With the group by code - I get a blank area where the results should be.

Without the group by code, I get the results, but not grouped (of course)

#5

I expect this comes from upgrading MySQL to 5.7 or above. It is much better answered on SO than I’d have time to atm

When SELECT ing a column that is not part of the GROUP BY there could be multiple values for that column within the groups, but there will only be space for a single value in the results. So, the database usually needs to be told exactly how to make those multiple values into one value. Commonly, this is done with an aggregate function like COUNT() , SUM() , MAX() etc… I say usually because most other popular database systems insist on this. However, in MySQL prior to version 5.7 the default behaviour has been more forgiving because it will not complain and then arbitrarily choose any value ! It also has an ANY_VALUE() function that could be used as another solution to this question if you really needed the same behaviour as before. This flexibility comes at a cost because it is non-deterministic, so I would not recommend it unless you have a very good reason for needing it. MySQL are now turning on the only_full_group_by setting by default for good reasons, so it’s best to get used to it and make your queries comply with it.

So why my simple answer above? I’ve made a couple of assumptions:

  1. the group_id is unique. Seems reasonable, it is an ‘ID’ after all.

  2. the group_name is also unique. This may not be such a reasonable assumption. If this is not the case and you have some duplicate group_names and you then follow my advice to add group_id to the GROUP BY , you may find that you now get more results than before because the groups with the same name will now have separate rows in the results. To me, this would be better than having these duplicate groups hidden because the database has quietly selected a value arbitrarily!

It’s also good practice to qualify all the columns with their table name or alias when there’s more than one table involved…

2 Likes
#6

Hi Jim,

What is “SO?” Yes, the issue is caused by the PHP 5 to PHP 7 upgrade, according to Google, etc.

My problem is how do I now ‘group’ my sql results now that PHP7 is picky?

What is the correct group by code that is PHP7 compatible or how do I achieve the same without the group by command?

Thanks for the above article. Completely over my head.

Cheers

Gary

#7

It isn’t a PHP issue, it is a database issue. It breaks in PHP, because you are not sending out the errors from the Database.

The change is how grouping is done. In SQL Server, it has been that was for years, and is a pain there too. It just means that if you want to use a group by clause, you have to specify every column you are selecting… Another reason why SELECT * is a bad idea from the start!

2 Likes
#8

Got it. I’ll remove the ‘*’ and specify each field required by the rest of the query.

Many Thanks

Gary

#9

Hi astonecipher

Having got my ‘conntest.php’ script working for PHP7 and MySQLi (Thanks to you), I have been trying to get the ‘GROUP BY’ bit working also. To this end, I have added this ‘GROUP BY’ test to the bottom of my ‘conntest.php’ script.

If I run the script as below (GROUP BY IGNORED), I get the table results but not grouped and no warning message

If I run the alternate script (GROUP BY SQL), I just get the warning:
Warning: mysqli_num_rows() expects parameter 1 to be mysqli_result, boolean given in /var/www/csg/conntest.php line 25.
This refers to the $resultcheck line which actually works as it correctly reports 1335 rows.

I have specified two fields in the SELECT, one of which is used in the GROUP BY. Now a single GROUP BY upsets everything. Does GROUP BY ever work with PHP7 & MySQLi?

Cheers

Gary

//=========================================
//***** GROUP BY *****
//=========================================

// GROUP BY IGNORED
$sql=“SELECT timesheet_id,user_key FROM tbl_timesheet”;

// -----------------------------------------------

// GROUP BY SQL
// $sql="SELECT timesheet_id,user_key FROM tbl_timesheet GROUP BY user_key";

// -----------------------------------------------

$result = mysqli_query($conn,$sql);
$resultcheck = mysqli_num_rows($result);

echo “There are $resultcheck rows in the timesheet table
\n”;

// The answer is 1335. This is correct.

if ($resultcheck > 0) {

while ($row = mysqli_fetch_array($result))

{	
$str.="<tr>";
$str.="<td>".$row['timesheet_id']."</td>";
$str.="<td>".$row['user_key']."</td>";
$str.="</tr>";
}

}

echo $str;

#10

Your query is failing due to an error, but since you don’t have any error handling in your code, the code continues to run and produces follow-on php errors.

The simplest way of adding error handling for all the database statements, without adding code for each statement that can fail, is to enable exceptions for errors and in most cases let php catch and handle the exception, where it will use its’ error_reporting, display_errors, and log_errors settings to control what happens with the actual error information (short version - enable exceptions for the database extension you are using and database errors will get displayed/logged the same as php errors.) To enable exceptions for the mysqli extension, add the following line of code before the point where you make the database connection -

mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
#11

Again: this has absolute nothing to do with PHP. Just leave every PHP code out, it doesn’t matter for your problem and won’t lead anywhere here. Your problem is SQL, and GROUP BY works within SQL when applied correctly.

You just have to read probably what was already said:

When SELECT ing a column that is not part of the GROUP BY there could be multiple values for that column within the groups, but there will only be space for a single value in the results. So, the database usually needs to be told exactly how to make those multiple values into one value. Commonly, this is done with an aggregate function like COUNT() , SUM() , MAX() etc

#12

Hi phdr and chorn,

Thanks for the replies. I’m beginning to see daylight. The mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT) producde the following error message;

Fatal error: Uncaught mysqli_sql_exception: Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column ‘csg.tbl_timesheet.timesheet_id’ which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by in /var/www/csg/conntest.php:79 Stack trace: #0 /var/www/csg/conntest.php(79): mysqli_query(Object(mysqli), ‘SELECT `timeshe…’) #1 {main} thrown in /var/www/csg/conntest.php on line 79

I have been reading up on aggregation but nothing seems to match with what I need. This is the problem.

I have a number of people submitting timesheets on a weekly basis so I have ended up, over the last 10 weeks of 70 timesheets submitted by 7 people. What I need to achieve is the list of all the timesheets grouped by the people who submitted them, like this:

Person #1 (user_key)
timesheet_id #1
timesheet_id #8
timesheet_id #15
timesheet_id #22
timesheet_id #29
timesheet_id #36
timesheet_id #42

Person #2 (user_key)
timesheet_id #2
timesheet_id #9
timesheet_id #16
timesheet_id #23
timesheet_id #30
timesheet_id #37
timesheet_id #43

etc

My sql is $sql="SELECT timesheet_id,user_key FROM tbl_timesheet GROUP BY user_key";

I can see I have to ‘aggregate’ user_key, but which one? None of AVG, COUNT, SUM, MIN, MAX seem relevant.

Cheers

Gary

#13

You mix up database logic and presentation. In database terms you just want to sort by person, then sort by timesheet ID - but at least you want to keep all information, which person relates to which timesheets. GROUP BY is primary used to reduce information. You may have to define a seperator character and concat all timesheets into a string to explode them later.

But what you actually want to do in your presentation layer is group break, so you want to react on the privious item

if($previous['person'] !== $current['person']) show new headline...

Another way that i would recommend is to foreach over your complete dataset and use the person key as an index like

forach($data as $current) $indexed[$current['person']][] = $current;

and you have a nice hierarchical structure to build your template upon.

#14

Thanks Chorn. I’ll do some ‘foreach’ research

Regards

Gary

Sponsor our Newsletter | Privacy Policy | Terms of Service