MySQL left join, after create a multidimensional array

Question

I have 2 table and the foreign key is 'timesection_id'. First table: timesection

---

| id | name | time | position |

---

Second table is content

---

| id | title | short_desc | desc | img | position | timesection_id |

I would like to create a left join to first table. It have to be order by position. The result should be looks like that:

array (size=1)
  'content' => 
    array (size=2)
      1 => 
        array (size=3)
          'time' => string '12-00' (length=5)
          'name' => string 'Start' (length=21)
          'performer' => 
            array (size=3)
                   0=>
                        'title' => string '12-00'
                        'desc' => string 'etc etc'
                        'shor_desc' => string 'etc etc'
                        'img' => string '1.jpg'
                   1=>
                        'title' => string '12-00'
                        'desc' => string 'etc etc'
                        'shor_desc' => string 'etc etc'
                        'img' => string '1.jpg'
      2 => 
        array (size=3)
          'time' => string '13-00' (length=5)
          'name' => string 'Something' (length=24)
          'performer' => 
            array (size=3)
                   0=>
                        'title' => string '12-00'
                        'desc' => string 'etc etc'
                        'shor_desc' => string 'etc etc'
                        'img' => string '1.jpg'
                   1=>
                        'title' => string '12-00'
                        'desc' => string 'etc etc'
                        'shor_desc' => string 'etc etc'
                        'img' => string '1.jpg'

My sql:

SELECT * FROM timesection
    LEFT OUTER JOIN content ON  timesection_id = timesection.id
    ORDER BY timesection.position ASC

And i have this result:

 0 => 
    array (size=9)
      'id' => null
      'name' => string 'KezdĂŠs' (length=7)
      'time' => string '11:00' (length=5)
      'position' => null
      'title' => null
      'short_desc' => null
      'desc' => null
      'img' => null
      'timesection_id' => null
  1 => 
    array (size=9)
      'id' => string '1' (length=1)
      'name' => string 'KezdĂŠs, Sikerdal debĂźtĂĄlĂĄsa' (length=31)
      'time' => string '12:00-14.00' (length=11)
      'position' => string '1' (length=1)
      'title' => string 'Ăv Embere orvoscsoport' (length=23)
      'short_desc' => string '' (length=0)
      'desc' => string 'PĂŠldĂĄtlan ĂśsszefogĂĄs eredmĂŠnyekĂŠnt egĂŠszsĂŠges gyermeket szĂźlt ĂŠs nĂŠgy ĂŠletet mentett meg egy tĂśbb hĂłnapja agyhalott asszony Debrecenben' (length=149)
      'img' => string '1.jpg' (length=5)
      'timesection_id' => string '2' (length=1)
  2 => 
    array (size=9)
      'id' => string '2' (length=1)
      'name' => string 'KezdĂŠs, Sikerdal debĂźtĂĄlĂĄsa' (length=31)
      'time' => string '12:00-14.00' (length=11)
      'position' => string '2' (length=1)
      'title' => string 'Varga RĂłbert' (length=13)
      'short_desc' => string '' (length=0)
      'desc' => string 'Varga RĂłbertVarga RĂłbertVarga RĂłbertVarga RĂłbertVarga RĂłbertVarga RĂłbertVarga RĂłbertVarga RĂłbertVarga RĂłbertVarga RĂłbertVarga RĂłbertVarga RĂłbert' (length=156)
      'img' => string '1.jpg' (length=5)
      'timesection_id' => string '2' (length=1)
  3 => 
    array (size=9)
      'id' => string '3' (length=1)
      'name' => string 'KezdĂŠs, Sikerdal debĂźtĂĄlĂĄsa' (length=31)
      'time' => string '12:00-14.00' (length=11)
      'position' => string '3' (length=1)
      'title' => string 'BenkĹ Vilmos ĂŠs fiatal tehetsĂŠgek' (length=36)
      'short_desc' => string '' (length=0)
      'desc' => string 'BenkĹ Vilmos ĂŠs fiatal tehetsĂŠgekBenkĹ Vilmos ĂŠs fiatal tehetsĂŠgekBenkĹ Vilmos ĂŠs fiatal tehetsĂŠgekBenkĹ Vilmos ĂŠs fiatal tehetsĂŠgekBenkĹ Vilmos ĂŠs fiatal tehetsĂŠgekBenkĹ Vilmos ĂŠs fiatal tehetsĂŠgekBenkĹ Vilmos ĂŠs fiatal tehetsĂŠgekBenkĹ Vilmos ĂŠs fiatal tehetsĂŠgekBenkĹ Vilmos ĂŠs fiatal tehetsĂŠgekBenkĹ Vilmos ĂŠs fiatal tehetsĂŠgekBenkĹ Vilmos ĂŠs fiatal tehetsĂŠgek' (length=396)
      'img' => string '1.jpg' (length=5)
      'timesection_id' => string '2' (length=1)

If the main table has more records from subtable i would like to create a performer value and put it in a sub array. Performen value can be null.

Answer 1

SQL Query:

SELECT timesection.id,timesection.name,timesection.time,
   content.title,content.short_desc,content.desc,content.img,content.id as cid
   FROM timesection
   LEFT JOIN content ON content.timesection_id = timesection.id
   ORDER BY timesection.position , content.position

PHP CODE: (note: using old deprecated mysql_* functions, consider using mysqli or PDO instead)

$r = mysql_query($query);
$array = array('content'=>array());
$i=0;
$lastid = null;
while($row=mysql_fetch_object($r))
{
 if($lastid!==$row->id)
 {
  $array['content'][++$i]=array('time'=>$row->time,'name'=>$row->name,'performer'=>array());
  $lastid=$row->id;
 }
 if($row->cid!==null)
 {
  $array['content'][$i]['performer'][]=array('title'=>$row->title,'short_desc'=>$row->short_desc,'desc'=>$row->desc,'img'=>$row->img);
 }
}