PHP7 / mySQL query with DISTINCT plus showing the other columns in $row

Question

I have the following file_tbl table: id fname fdescription ftype fsize wbsid timestamp

And I do no not want to select duplicate rows with same fname and wbsid, but same fname and different wbsid shall be selected.

my query:

$stmt = $pdo->prepare("SELECT DISTINCT(fname), wbsid from file_tbl ORDER BY wbsid ASC");
$stmt->execute();

while ($row = $stmt->fetch(PDO::FETCH_ASSOC)){
    echo'<hr>file-name:'.$row['fname'];
    echo'<br>file-description:'.$row['fdescription'];
    echo'<br>file-wbs-id:'.$row['wbsid'];
    echo'<br>file-wbs-id:'.$row['timestamp'];
    }

This will cause a Notice: Undefined index: fdescription, timestamp in ... on line ...

the query is working for the distinct, but I need all the columns to be fetched - and I do not know how to SELECT all of them without destroying the query. (I made a lot trials with JOIN and GROUP BY, but nothing worked for me - eg this solution does not meet my requirements: Select distinct column along with some other columns in MySQL)

notice: I created the timestamp via PHP , its a (varchar) if this is of any interest.

My approach now is to bring two SELECT queries from the same table together - unfortunately I wasn't able to accomplish this yet. (INNER JOIN; LEFT JOIN; some sorts of subqueries (http://www.w3resource.com/mysql/subqueries/index.php) ...

edit: a working sql-fiddle is now available - its working fine there, but not on my own LAMP-stack: http://sqlfiddle.com/#!9/95bad/1 or, the copy-pasted original with InnoDB and latin1: http://sqlfiddle.com/#!9/b3bce0/2

`SELECT * FROM file_tbl ft GROUP BY ft.wbsid ORDER BY ft.wbsid ASC` is not enough? — Sepultura, Jan 05 '17 at 11:57
@Sepultura : "SELECT * FROM file_tbl ft GROUP BY ft.wbsid ORDER BY ft.wbsid ASC" is not working at all - it would technically work with ft.id instead ft.wbsid - but has not the result i need here... (btw: the wbsid is not a number (int), its a varchar with IP-comperable-format). — flowfab, Jan 05 '17 at 13:15

edigu · Accepted Answer · 2017-01-13T10:41:04.387

1

You are trying to use a column (fdescription) which you didn't select.

Try to add fdescription to your SELECT clause:

SELECT DISTINCT(fname), wbsid, fdescription
FROM file_tbl
ORDER BY wbsid ASC

If this does not produce the results you want, try to use grouping:

SELECT fname, wbsid, fdescription
FROM file_tbl
GROUP BY fname, wbsid
ORDER BY wbsid ASC

edited Jan 13 '17 at 10:41

answered Jan 05 '17 at 23:08

edigu

9,878
5
57
80

:... Unfortunately, if I add the columns I need to the SELECT DISTINCT, no filtering out of the duplicates is the result. And if I try the GROUP BY fname, wbsid the query selects nothing – flowfab Jan 06 '17 at 09:52
The reason, why the SELECT DISTINCT try fails seems to be the 'timestamp' column. if I add it -> never a duplicate -> SELECTS all is the result. (I did not mention the timestamp-column, sorry) – flowfab Jan 06 '17 at 10:05
Since you dont use the timestamp column in query its not the case. Distinct is also eliminates duplicate records for selected columns. Can you create the scenario on sqlfiddle? – edigu Jan 06 '17 at 10:11

score 0 · Answer 2 · edited May 23 '17 at 11:45

edigu's answer seems to offer the correct query for me - unfortunately only in sqlfiddle, and not within my own LAMP stack with Ubuntu 16.04.1 LTS | Apache/2.4.18 | MySQL 5.7.11-0ubuntu6 | PHP7.0.8-0Ubuntu0.16.04.3

here is the working result, based on edigu's GROUP BY suggestion: http://sqlfiddle.com/#!9/335584/5

the 2nd and 3rd rows are filtered out (same fname, same wbsid of the data) - just the way it should!

... now, i need to figure-out why its not working on my own installation...

AND I FOUND OUT: The default SQL mode in MySQL 5.7 includes these modes: ONLY_FULL_GROUP_BY, STRICT_TRANS_TABLES, NO_ZERO_IN_DATE, NO_ZERO_DATE, ERROR_FOR_DIVISION_BY_ZERO, NO_AUTO_CREATE_USER, and NO_ENGINE_SUBSTITUTION. source: http://dev.mysql.com/doc/refman/5.7/en/sql-mode.html

Solution:

1.sudo nano /etc/mysql/my.cnf

Add the following 2 lines of code at the end of the my.cnf (even if most of configuration content is now (Ubuntu 16.) elswhere & only linked herein):

[mysqld]
sql_mode = "STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"

sudo service mysql restart

Solution can be found here, too: https://stackoverflow.com/a/37248560/4747545

PHP7 / mySQL query with DISTINCT plus showing the other columns in $row

2 Answers2

Linked