Bruce Bruce - 4 months ago 13
MySQL Question

PHP Groupings and LOOPS

How would I group this by skill and echo all the photo's for each skill.

SELECT skills.teacher, skills.student, skills.skill, profile.photo
FROM skills
INNER JOIN profile ON skills.student = profile.username
WHERE teacher = 'teach1'

teacher | student | skill | photo
------- | -------- | ------ | ------------
teach1 | student1 | skill1 | student1.jpg
teach1 | student2 | skill1 | student2.jpg
teach1 | student3 | skill2 | student3.jpg
teach1 | student3 | skill1 | student3.jpg
teach1 | student4 | skill3 | student4.jpg
teach1 | student1 | skill2 | student1.jpg


I stress this is an example. I do not actually know the names of the "skill" as they are created by the students. I don't know how many skills will be listed in all. I don't know what users will add what skills to what teachers.

I have tried while loops and foreach loops. I just can't figure out how to group this and what type of loop to use...

I need my end result to allow me to echo.

<div>skill1
<img src="student1.jpg">
<img src="student2.jpg">
<img src="student3.jpg">
...
...
...
</div>

<div>skill2
<img src="student1.jpg">
<img src="student3.jpg">
...
...
...
</div>

<div>skill3
<img src="student4.jpg">
...
...
...
</div>

Answer

First off, order by skill, so that you get them in order:

SELECT skills.teacher, skills.student, skills.skill, profile.photo
FROM skills
INNER JOIN profile ON skills.student = profile.username
WHERE teacher =  'teach1'
ORDER BY skills.skill

Assign a variable (In this case $lastSkill) outside of your while loop. Check if it's changed, and output things accordingly. Take a look at the example below. Your PHP code would look something similar to this:

$lastSkill = null;

while ( $row = fetchrow() )
{
    if ( $row['skill'] !== $lastSkill )
    {
        //Don't close a div, if it's the first row
        if ( $lastSkill !== null )
            echo '</div>';

        //We've moved onto a new row, echo it out
        echo '<div>' . $row['skill'];
        //Store the lastSkill
        $lastSkill = $row['skill'];
    }

    echo '<img src="' . $row['photo'] . '">';
}
Comments