Help With 1 To Many Join

[antmeeks]

I've been stuck on this for a couple of hours now & I'd really appreciate some help(!)

 

I have 2 tables:

 

entries
+----+--------+------+-------+------------+
¦ id ¦ status ¦ type ¦ order ¦ date       ¦
+----+--------+------+-------+------------+
¦ 1  ¦ 1      ¦ term ¦ 4     ¦ 0000-00-00 ¦
¦ 2  ¦ 0      ¦ item ¦ 6     ¦ 0000-00-00 ¦
¦ 3  ¦ 1      ¦ term ¦ 1     ¦ 0000-00-00 ¦
¦ 4  ¦ 1      ¦ term ¦ 7     ¦ 0000-00-00 ¦
¦ 5  ¦ 0      ¦ item ¦ 5     ¦ 0000-00-00 ¦
¦ 6  ¦ 1      ¦ term ¦ 8     ¦ 0000-00-00 ¦
¦ 7  ¦ 1      ¦ term ¦ 3     ¦ 0000-00-00 ¦
¦ 8  ¦ 0      ¦ item ¦ 2     ¦ 0000-00-00 ¦
+----+--------+------+-------+------------+

entry_text
+----+----------+----------+---------+---------+
¦ id ¦ entry_id ¦ language ¦ name    ¦ value   ¦
+----+----------+----------+---------+---------+
¦ 1  ¦ 7        ¦ en       ¦ title   ¦ varchar ¦
¦ 2  ¦ 7        ¦ en       ¦ content ¦ varchar ¦
¦ 3  ¦ 7        ¦ fr       ¦ title   ¦ varchar ¦
¦ 4  ¦ 7        ¦ fr       ¦ content ¦ varchar ¦
¦ 5  ¦ 3        ¦ en       ¦ title   ¦ varchar ¦
¦ 6  ¦ 3        ¦ en       ¦ content ¦ varchar ¦
¦ 7  ¦ 3        ¦ fr       ¦ title   ¦ varchar ¦
¦ 8  ¦ 3        ¦ fr       ¦ content ¦ varchar ¦
¦ 9  ¦ 6        ¦ en       ¦ title   ¦ varchar ¦
¦ 10 ¦ 6        ¦ en       ¦ content ¦ varchar ¦
¦ 11 ¦ 6        ¦ fr       ¦ title   ¦ varchar ¦
¦ 12 ¦ 6        ¦ fr       ¦ content ¦ varchar ¦
¦ 13 ¦ 2        ¦ en       ¦ title   ¦ varchar ¦
¦ 14 ¦ 2        ¦ en       ¦ content ¦ varchar ¦
¦ 15 ¦ 2        ¦ fr       ¦ title   ¦ varchar ¦
¦ 16 ¦ 2        ¦ fr       ¦ content ¦ varchar ¦
+----+----------+----------+---------+---------+

 

What I'm trying to do is some sort of join statement like:

 

select * from entries where status = 1 and type = term

join name,value from entry_text where entry_id = entries.id and language = en

order by entries.order, entries.date ASC

 

where I end up with an associative result, indexed by entries.id with the result from entry_text being a name/val pair nested array:

array(

[3] => array(
          [id] => 3
          [status] => 1
          [type] => term
          [order] => 1
          [date] => 0000-00-00
          [text] => array(
                     [title] => varchar
                     [content] => varchar
                     )
          )

[7] => array(
          [id] => 7
          [status] => 1
          [type] => term
          [order] => 3
          [date] => 0000-00-00
          [text] => array(
                     [title] => varchar
                     [content] => varchar
                     )
          )

[1] => array(
          [id] => 1
          [status] => 1
          [type] => term
          [order] => 4
          [date] => 0000-00-00
          [text] => array(
                     [title] => varchar
                     [content] => varchar
                     )
          )
etc...
)

 

Doable...?

 

(sorry for the wonky formatting - I can't get the editor to preserve my spacing)

[Christian F.]

I think you're using the wrong JOIN syntax there, actually never seen that variant myself.

What you want to be doing is using a straight INNER JOIN, using the following syntax:

SELECT {$Fields} FROM {$Table} [AS alias_1]
INNER JOIN {$Table_2} [AS alias_2] ON alias_2.entry_id = alias_1.id AND {$Lang}
{$Conditions}