[SOLVED] Organize LEFT JOIN results; SQL or PHP?

[xtopolis]

While trying to help another on phpfreaks, I was messing around with LEFT JOINS, and realized I didn't know how to organize the results of a query as I needed.

(Question):

How would I show the aname(Artist name) only once?

Query

SELECT a.aid, a.aname,s.sname
FROM artists a 
LEFT JOIN songs s USING (aid)

 

Table/DB Info: MySQL 5.0  (2 simple tables)

-- Table structure for table `artists`
CREATE TABLE `artists` (
  `aid` int(11) NOT NULL auto_increment,
  `aname` varchar(100) collate latin1_german2_ci NOT NULL,
  `adesc` varchar(500) collate latin1_german2_ci NOT NULL,
  PRIMARY KEY  (`aid`)
) 

-- Dumping data for table `artists`
INSERT INTO `artists` VALUES (1, 'Theme Song Heroes', 'We happily sing your theme songs.');
INSERT INTO `artists` VALUES (2, 'Ice Cream', 'Plays that ice cream truck jingle.');

-- Table structure for table `songs`
CREATE TABLE `songs` (
  `aid` int(11) NOT NULL,
  `sname` varchar(255) collate latin1_german2_ci NOT NULL
)

-- Dumping data for table `songs`
INSERT INTO `songs` VALUES (1, 'Mythbusters');
INSERT INTO `songs` VALUES (1, 'Batman');
INSERT INTO `songs` VALUES (1, 'Power Rangers');
INSERT INTO `songs` VALUES (1, 'Dirty Jobs');
INSERT INTO `songs` VALUES (2, 'We all scream');
INSERT INTO `songs` VALUES (2, 'Ice ice cream');

 

[pre]MYSQL RESULTS:

aid  aname                sname

1    Theme Song Heroes    We happily sing your theme songs.    Mythbusters

1    Theme Song Heroes    We happily sing your theme songs.    Batman

1    Theme Song Heroes    We happily sing your theme songs.    Power Rangers

1    Theme Song Heroes    We happily sing your theme songs.    Dirty Jobs

2    Ice Cream            Plays that ice cream truck jingle.    We all scream

2    Ice Cream            Plays that ice cream truck jingle.    Ice ice cream

[/pre]

 

How would I show the aname(Artist name) only once?

 

my test php code:

<?php
require 'engine/classes/mysql.php';
$db = &new MYSQL(xxxx,xxxx,xxxx,xxxx);

$sql ="SELECT a.aid, a.aname, a.adesc, s.sname
FROM artists a
LEFT JOIN songs s
USING ( aid )
LIMIT 0 , 30";

$result = $db->query($sql) or die(mysql_error());

while (list($id,$a,$d,$s) = mysql_fetch_array($result)){
  echo "Artist: $a<br />";
  echo "Desc: $d<br />";
  echo "Song: $s<br />";
  
  echo "<br />";
}
?>

Outputs all the data similar to the mysql table, with repeating the artist name, and description for each song..

[redarrow]

array_unique( ) php function will show 1 instence off the repeated words............

 

example

 

<?php

$x=array("redarrow","redarrow","redarrow","php");

$x=array_unique($x);

foreach ($x as $a){

echo "$a <br>";
}
?>

[xtopolis]

Can you provide an example in my context?

[discomatt]

GROUP BY is nicer.

 

SELECT a.aid, a.aname,s.sname

FROM artists a

LEFT JOIN songs s USING (aid)

GROUP BY a.aname

[xtopolis]

This query returns only 2 rows, instead of all..

SELECT a.aid, a.aname,s.sname
FROM artists a
LEFT JOIN songs s USING (aid)
GROUP BY a.aname

[pre]

aid  aname                sname

2    Ice Cream            We all scream

1    Theme Song Heroes    Mythbusters

[/pre]

So, it seems GROUP BY is not the solution?

[discomatt]

Well, show me the results you want then.

 

How would I show the aname(Artist name) only once?

 

My recommendation solves your problem perfectly

[discomatt]

<?php
require 'engine/classes/mysql.php';
$db = &new MYSQL(xxxx,xxxx,xxxx,xxxx);

$sql ="SELECT a.aid, a.aname, a.adesc, s.sname
FROM artists a
LEFT JOIN songs s
USING ( aid )
LIMIT 0 , 30";

$result = $db->query($sql) or die(mysql_error());

while (list($id,$a,$d,$s) = mysql_fetch_array($result)){
  if( !isset( $la ) || $la != $a ) {
  	$la = $a;
  	echo "<h2>Artist: $a</h2>";
  }
  echo "Desc: $d<br />";
  echo "Song: $s<br /><br />";
}
?>

 

Might be what you want.

[xtopolis]

Yes, that's exactly what I was trying to do.  Thanks very much, I was attempting this last night, but I'm sick, so my mind wasn't functioning properly

 

Appreciate it!