PHP foreach statement within MySQL select query

Question

This is my data

id          cost    cat         foreign     colours
--------------------------------------------------------   
385_white   99      swarovski   12          black;blue
386_white   99      swarovski   12          black;blue;green
387_white   99      swarovski   12          yellow;green
389_white   99      swarovski   12          white;silver
385_white   99      swarovski   12          silver

This is my query

$checkSQL=mysql_query("SELECT * FROM `products`");
while($r = mysql_fetch_array($checkSQL)) {
    echo '
        <ul class="ColourList">
            <li class="$row">$row</li>
        </ul>
    ';
}

Pseudo code - this should split the list into unique li

foreach ($r["colours"] as $key){
                        $key = explode(";", $r["colours"]);
                        <li class="$key"></li>
                    }

I want to create a list li item for each colour in the table row, so basically split the colon into separate rows - think it is explode?

Solution

This ought to do it :)

$checkSQL=mysql_query("SELECT * FROM `products`");
while($r = mysql_fetch_assoc($checkSQL)) {

    echo '<ul class="ColourList">';
    $cols = explode( ';', $r['colours'] );
    foreach ( $cols as $col ){
        echo '<li class="' . $col . '">' . $col . '</li>';
    }
    echo '</ul>';
}

Comments

[Patrick Moore]

Pretty sure it needs to be $r[4], considering 0 = id, 1 = cost, 2 = cat, 3 = foreign, 4 = colour

[Travesty3]

+1 Good answer and glad you changed mysql_fetch_array to mysql_fetch_assoc. Might want to point out the reason why you did that. Also, might want to point out that you shouldn't use mysql_* functions at all. Aside from that, this answer is fundamentally the same as the answer that I was going to post before you beat me to it :-)

[Admin]

yep, my mistake, array starts on 0 :) thanks