CODEWITHSUNDEEP
phpmysqlarrays
libertaire
libertaire

Reputation: 855

group similar results together

I'm looking for a way to group similar results together.

I have a table with a lot of bands images. Each images name are identified with the band's name, followed by a number. Example:

Metallica 1
Metallica 2
Iron Maiden 1
Iron Maiden 2
Iron Maiden 3
Pink Floyd 1

I'm looking for a way to group the results together. Something that would look like that:

Metallica (2 results)
Iron Maiden (3 results)
Pink Floyd (1 result)

So far i have this:

$asql = "SELECT * FROM bands WHERE GROUP BY image";
$ares = mysql_query($asql) or die(mysql_error());
while($row = mysql_fetch_array($ares)) {
$image = $row['image'];
echo "<a href=\"view_more_images.php?band=$image\">$image (X results)</a>";
}

But it only groups together the items that has exactly the same name

Upvotes: 1

Views: 96

Answers (1)

denisvm
denisvm

Reputation: 730

If image contains the string "BAND NUMBER", you should consider add one column containing only the band name and group by this column.

Anyways, you can ignore the last token with the following SQL:

SELECT
  LEFT(image, LENGTH(image) - LOCATE(' ', REVERSE(image))+1) bandname,
  COUNT(image)
FROM bands
GROUP BY bandname

Upvotes: 2

Related Questions