How to both use a DISTINCT row and a non DISTINCT row

Question

all! I have a bit of a tricky one for you today, I want to use the select DISTINCT statement to both select a row that needs to be distinct but also in the same statement (or the way I a have tried?) a row that doesn't/can't be distinct. My desired result is to only have one of each of the classnames. Currently it outputs like this:

English: textbook, folder, laptop
English: textbook
Media:   textbook, folder
English: textbook, folder
English: textbook, folder
Art:     textbook

And this is how I want it to output:

English: textbook, folder, laptop
Media:   textbook, folder
Art:     textbook

This is the layout of the database:

|ID|classname|Book  
|49|English  |textbook, folder, laptop
|50|English  |textbook  
|53|Media    |textbook, folder 
|54|English  |textbook, folder 
|55|Art      |folder

I'm obviously VERY new to php so any help would be appreciated!

This is my approach so far:

$sql = "SELECT DISTINCT classname FROM classes ORDER BY Due;";
$result1 = mysqli_query($conn, $sql);
$resultCheck = mysqli_num_rows($result1);
if ($resultCheck > 0){
    while ($row = mysqli_fetch_assoc($result1)){
        $classname = $row["classname"];

        if ($classname == "English"){
            $newName = $classname;
            $sql = "SELECT Book FROM classes WHERE Book='$newName';";
            $result1 = mysqli_query($conn, $sql);
            $resultCheck = mysqli_num_rows($result1);

            if ($resultCheck > 0){
                while ($row = mysqli_fetch_assoc($result1)){
                    $materials = $row["Book"]; 
                    echo "<div class='subname'>$newName:";
                    echo "<div class='wow'>$materials</div>";
                    echo "</div><br>";
                }

            }
        }
    }
}

Unclear what you are asking. Please rephrase your question by showing us _clear_ sample input table data, along with the exact expected output and your best description about what is happening there. From what I can see, PHP code may not even have any relevance here, assuming that SQL by itself can do the job you want. — Tim Biegeleisen, Feb 04 '19 at 08:24
`English: textbook, folder, laptop` & `English: textbook` are not distinct. What do you expect when the rows are `English: textbook, audio CD` & `English: textbook, folder, laptop`? — Pinke Helga, Feb 04 '19 at 08:29
@TimBiegeleisen Hey sorry about the confusion, basically what i want is the "class name" to be only repeated once i.e; english, math, art NOT english, english, math, english, art, math — Sonder12, Feb 04 '19 at 08:30
@guradio Well PHP _could_ be used to solve this, but yes, it is also my take that this should be a database problem. — Tim Biegeleisen, Feb 04 '19 at 08:36
You seem to have a database normalization problem. Split it up into multiple joined tables: subjects (math, engl...), materials, classes, students. — Pinke Helga, Feb 04 '19 at 08:45
It is hard to give anexact answer without seeing your database table structure. did you keep `textbook, folder, laptop` in a single column or is it a concatinated output? Best resolution would be doing this in on database side if your database structuer is right. You can select distinct values by groupping by `classname` and concatinate them in your select clause... Many similar examples exists on SO like [How can I concatenate set of results in MySQL?](https://stackoverflow.com/questions/1728417/how-can-i-concatenate-set-of-results-in-mysql) — Mp0int, Feb 04 '19 at 08:47
With a properly normalized db and showing your tables structure a `GROUP BY` query aggregating your desired format could easily written. — Pinke Helga, Feb 04 '19 at 08:53

score 2 · Answer 1 · answered Feb 04 '19 at 08:50

Well, given the crappy design of your database the first step you need to do is itemize your lists such as "textbook, folder, laptop" [such that you get three rows with one item instead of one row with three items]. The semantics of that operation is a bit like SQL UNNEST but sadly, the "structure" (insofar as using that word is appropriate for what you have) of your database is unfit for using that. I'm not sure it can be done without some form of procedural coding, so the answer most likely to be correct is "just forget doing that in one SQL statement".

What you call DISTINCT can only be applied [to the results you get] after the itemization.

After applying the DISTINCT, you then need to re-group. Maybe it can be done in client-side languages, but those are not my cup of tea.

Mureinik · Answer 2 · 2019-02-04T12:29:26.143

As a general statement, this isn't a very robust database design. You may be better served to normalize your table and have a single classname-book combination in every row.

If that is not a possibility, I'd group_concat all the books per class and then explode them to an array on the PHP side, make the result unique, and join it back to a string:

$sql = "SELECT classname, GROUP_CONCAT(book SEPARATOR ', ') AS materials FROM classes GROUP BY classname";
$result = mysqli_query($conn, $sql);

while ($row = mysqli_fetch_assoc($result)) {
    $classname = $row["classname"]; 
    $materials = $row["materials"];
    $materials = implode(',', array_unique(explode(', ', $materials)));
    echo "<div class='subname'>${classname}:";
    echo "<div class='wow'>$materials</div>";
    echo "</div><br/>";
}

How to both use a DISTINCT row and a non DISTINCT row

2 Answers2