PHP / mySQL - Joins and output

[jsschmitt]

Here is the situation:

 

I am learning (teaching myself) about mySQL joins... and I have reached a spot where I need some help. Below is my table structure, and while I know it's not intricate and my not make much sense, it is how I am learning.

 

Table1

ID

uname

 

Table2

ID

aboutMe

uname_ID

 

Table3

ID

url

uname_ID

 

Table4

id

crap

uname_ID

 

The code I currently have is this:

 

$sql = 'SELECT t1.uname, t2.aboutMe, t3.url, t4.crap 
		FROM `table1` AS t1
		LEFT JOIN `table2` AS t2
		ON t2.uname_id=t1.id
		LEFT JOIN `table3` AS t3
		ON t3.uname_id=t1.id
		LEFT JOIN `table4` AS t4
		ON t4.uname_id=t1.id';
$run = mysql_query($sql) or die(mysql_error());

while($row = mysql_fetch_array($run)){
	echo $row['uname']. " - ". $row['aboutMe']. " - ". $row['url']. " - ". $row['crap'];
	echo "<br />";
}
mysql_free_result($run);

 

This outputs the following:

 

tester - I am tester! - http://www.tester.com - crap

tester - I am tester! - www.gmail.com - crap

tester - I am tester! - www.google.com - crap

bob - Testing? - www.ohcrap.com - crap2

 

What I want / need to understand, is how to have it loop through, but not output all of the information if there is duplicates. i.e.

 

tester - I am tester! - http://www.tester.com -  www.gmail.com - www.google.com - crap

bob - Testing? - www.ohcrap.com - crap2

 

Any help?

[jsschmitt]

No one can help me?

[jsschmitt]

Ok... so I sorta found something:

 

$sql = 'SELECT t1.uname, t2.aboutMe, t3.url, t4.crap 
		FROM `table1` AS t1
		LEFT JOIN `table2` AS t2
		ON t2.uname_id=t1.id
		LEFT JOIN `table3` AS t3
		ON t3.uname_id=t1.id
		LEFT JOIN `table4` AS t4
		ON t4.uname_id=t1.id';
$run = mysql_query($sql) or die(mysql_error());	

$i=0;
while($row = mysql_fetch_array($run, MYSQL_ASSOC))
{
	if (!is_array($records[$row['uname']])) 
	$records[$row['uname']] = array(
		'uname' => $row['uname'],
		'aboutMe' => $row['aboutMe'],
		'crap' => $row['crap']);
	$records[$row['uname']][] = array(
		'url' => $row['url']);
}

foreach ($records as $userinfo) {
	echo $userinfo['uname'].' - ';
	echo $userinfo['aboutMe'] . ' - ';
	foreach ($records as $userinfo => $urls) {
		foreach ($urls as $url) {
	   		echo $url['url'] . ' - ';
		}
	}
   	echo $userinfo['crap'] . '<br />';
}

echo "<pre>";
print_r($records);
echo "</pre>";

mysql_free_result($run);

 

It outputs the following:

 

tester - I am tester! - t - I - c - http://www.tester.com - www.gmail.com - www.google.com - b - T - c - www.ohcrap.com - t - - - - t
bob - Testing? - t - I - c - http://www.tester.com - www.gmail.com - www.google.com - b - T - c - www.ohcrap.com - t - - - - t
tom - - t - I - c - http://www.tester.com - www.gmail.com - www.google.com - b - T - c - www.ohcrap.com - t - - - - t

Array
(
    [tester] => Array
        (
            [uname] => tester
            [aboutMe] => I am tester!
            [crap] => crap
            [0] => Array
                (
                    [url] => http://www.tester.com
                )

            [1] => Array
                (
                    [url] => www.gmail.com
                )

            [2] => Array
                (
                    [url] => www.google.com
                )

        )

    [bob] => Array
        (
            [uname] => bob
            [aboutMe] => Testing?
            [crap] => crap2
            [0] => Array
                (
                    [url] => www.ohcrap.com
                )

        )

    [tom] => Array
        (
            [uname] => tom
            [aboutMe] => 
            [crap] => 
            [0] => Array
                (
                    [url] => 
                )

        )

)

 

Now I just need to figure out where the single character items are being output from!

 

Any help?

[jsschmitt]

Seriously? No one can help me here?

[jsschmitt]

Well... in case anyone cares.. I figured it out...

 

$sql = 'SELECT t1.uname, t2.aboutMe, t3.url, t4.crap 
		FROM `table1` AS t1
		LEFT JOIN `table2` AS t2
		ON t2.uname_id=t1.id
		LEFT JOIN `table3` AS t3
		ON t3.uname_id=t1.id
		LEFT JOIN `table4` AS t4
		ON t4.uname_id=t1.id';
$run = mysql_query($sql) or die(mysql_error());	

$i=0;
while($row = mysql_fetch_array($run, MYSQL_ASSOC))
{
	if (!is_array($records[$row['uname']])) 
	$records[$row['uname']] = array(
		'uname' => $row['uname'],
		'aboutMe' => $row['aboutMe'],
		'crap' => $row['crap']);
	$records[$row['uname']][] = array(
		'url' => $row['url']);
}

foreach ($records as $userinfo) {
	echo $userinfo['uname'].' <br/> ';
	echo $userinfo['aboutMe'] . ' <br/> ';
	for($i=0; $i<$n, $n=count($userinfo[$i]); $i++){
		echo $userinfo[$i]['url'] . ' <br/> ';
	}
   	echo $userinfo['crap'] . '<br /><br/>';
}

 

Outputs:

 

tester
I am tester!
http://www.tester.com
www.gmail.com
www.google.com
crap

bob
Testing?
www.ohcrap.com
crap2

tom