displaying query results with php

[bibiki]

Hey there,
the following is what I get when I perform a typical query on my database:

+--------+----+---+---+----------+
| length | id | l | p | username |
+--------+----+---+---+----------+
| 50 | 12 | 1 | 1 | bibiki |
| 50 | 12 | 1 | 2 | bibiki |
| 50 | 12 | 2 | 1 | bibiki |
| 50 | 12 | 2 | 2 | bibiki |
| 50 | 12 | 3 | 1 | bibiki |
| 50 | 12 | 3 | 2 | bibiki |
| 40 | 13 | 4 | 5 | bibiki |
| 40 | 13 | 4 | 6 | bibiki |
| 40 | 13 | 5 | 5 | bibiki |
| 40 | 13 | 5 | 6 | bibiki |
+--------+----+---+---+----------+

Note that column id has two different values.

I want my view to look like this:
----------------
id 12
l: 1 2 3
p: 1 2
----------------
id 13
l: 4 5
p: 5 6
----------------

Instead of the above, what I am getting right now is:

------------------
id 12
l: 11 22 33(here I do not want these output twice)
.....and so on

//$mainarray contains the table I put above.
//a print_r on $mainArray looks like this:

/*
Array
(
    [0] => Array
        (
            [12] => Array
                (
                    [date] => 
                    [length] => 50
                    [id] => 12
                    [l] => 1
                    [p] => 1
                    [username] => bibiki
                )

        )

    [1] => Array
        (
            [12] => Array
                (
                    [date] => 
                    [length] => 50
                    [id] => 12
                    [l] => 1
                    [p] => 2
                    [username] => bibiki
                )

        )

    [2] => Array
        (
            [12] => Array
                (
                    [date] => 
                    [length] => 50
                    [id] => 12
                    [l] => 2
                    [p] => 1
                    [username] => bibiki
                )

        )

    [3] => Array
        (
            [12] => Array
                (
                    [date] => 
                    [length] => 50
                    [id] => 12
                    [l] => 2
                    [p] => 2
                    [username] => bibiki
                )

        )

    [4] => Array
        (
            [12] => Array
                (
                    [date] => 
                    [length] => 50
                    [id] => 12
                    [l] => 3
                    [p] => 1
                    [username] => bibiki
                )

        )

    [5] => Array
        (
            [12] => Array
                (
                    [date] => 
                    [length] => 50
                    [id] => 12
                    [l] => 3
                    [p] => 2
                    [username] => bibiki
                )

        )

    [6] => Array
        (
            [13] => Array
                (
                    [date] => 
                    [length] => 40
                    [id] => 13
                    [l] => 4
                    [p] => 5
                    [username] => bibiki
                )

        )

    [7] => Array
        (
            [13] => Array
                (
                    [date] => 
                    [length] => 40
                    [id] => 13
                    [l] => 4
                    [p] => 6
                    [username] => bibiki
                )

        )

    [8] => Array
        (
            [13] => Array
                (
                    [date] => 
                    [length] => 40
                    [id] => 13
                    [l] => 5
                    [p] => 5
                    [username] => bibiki
                )

        )

    [9] => Array
        (
            [13] => Array
                (
                    [date] => 
                    [length] => 40
                    [id] => 13
                    [l] => 5
                    [p] => 6
                    [username] => bibiki
                )

        )

)
*/

$differentIDs = array();
		foreach($mainArray as $row)
		{
			foreach($row as $k=>$v)
			{
				$differentIDs[] = $k;
			}
		}
		$activityIDs = array_unique($activityIDs);
		//print_r($activityIDs);//this now only contains 12 and 13
		echo "<br />";
		echo "<br />";
		foreach($differentIDs as $q=>$qq)
		{
			foreach($mainArray as $k=>$row)
			{
			/*
				echo $k;
				print_r($row);
				echo "<br />";
				*/
				foreach($row as $r=>$v)
				{
					if($r == $qq)
					{
						echo $v['l'];
					}
				}
			}
			echo "<br />";
		}
		print_r($mainArray);
	}

Now, as code might indicate, I put column on a separate id, and the iterate on each id. so I have two iterations here.

Then, inside this array I iterate on the main array. Again, I iterate on each array inside the main array. if the array I am iterating in is on an index equal to id, i output the value at index l inside this array.

I could just build an array for index l for each id, and then make sure duplicate l's are removed and output the l's... but I am afraid that is not the best way to do this. I am looking for a better way. if anyone has an idea I would greatly appreciate it.

[diafol]

$r = mysql_query("SELECT id,l,p FROM table ORDER BY id,l,p");
if(mysql_num_rows($r)){
  $id=0;$l=array();$p=array();
  while($d=mysql_fetch_assoc($r)){
    if($d['id']!= $id){
       if(!empty($l))echo 'l: ' . implode(' ',$l) . "<br />\n";
       if(!empty($p))echo 'p: ' . implode(' ',$p) . "<br />\n";
       echo "id $id<br />\n";
       $l=array();$p=array();
    }else{
      $l[] = $d['l'];$p[] = $d['p'];
    }
    $id = $d['id'];
  }
  if(!empty($l))echo 'l: ' . implode(' ',$l) . "<br />\n";
  if(!empty($p))echo 'p: ' . implode(' ',$p) . "<br />\n";
}

Definitely not tested. Am falling asleep...zzzz. Looks a little uncouth and repetitive.

//EDIT - just realised - I don't think this will work as you have repeats - I assumed that this was a code problem - you want unique values right?

if(!empty($l))echo 'l: ' . implode(' ',array_unique($l)) . "<br />\n";
       if(!empty($p))echo 'p: ' . implode(' ',array_unique($p)) . "<br />\n";

In which case that may do it.

[bibiki]

thanks ardav. that is pretty much how I did it. I am using the framework CodeIgniter. I am very interested to utilize MVC as best as possible that sometimes I lose the sense of what is right and what is not right. Somehow I limited myself by assuming that I am supposed to only have one array instead of three passed to my view and so I got stuck. but anyways. thanks for your input.

[diafol]

DOh! Idiot as I am - how's this?

$r=mysql_query("SELECT id, GROUP_CONCAT(l) AS l, GROUP_CONCAT(p) AS p FROM table GROUP BY id ORDER BY id");
while($d = mysql_fetch_assoc($r)){
   echo "id {$d['id']}<br />\n";
   $l = array_unique(explode(',',$r['l']));
   $p = array_unique(explode(',',$r['p']));
   sort($l);sort($p);
   echo "l: " . implode(' ',$l) . "<br />\n";
   echo "p: " . implode(' ',$p) . "<br />\n";
}

Think that should do it. The sql definitely works.

[bibiki]

this is a lot shorter. however, it would not work on my mainArray as it is three dimensional. anyways, thanks.