get from 2 tables which are connected

get from 2 tables which are connected - Printable Version

+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: Archived Discussions (https://forum.codeigniter.com/forumdisplay.php?fid=20)
+--- Forum: Archived Development & Programming (https://forum.codeigniter.com/forumdisplay.php?fid=23)
+--- Thread: get from 2 tables which are connected (/showthread.php?tid=54140)

Pages: 1 2

get from 2 tables which are connected - El Forum - 08-24-2012

[eluser]UnknownPlayer[/eluser]
Hi,
i have article, cats and article_cats table.

article table fields:

Code:
- id

- name

cats table table fields:

Code:
- id

- name

article_cats table fields:

Code:
- id

- article_id

- cat_id

now i wonna select that article with this fields:

Code:
- id

- name

- cats (array of categories)

How can i do this with codeigniter query helper and with 1 query ?

get from 2 tables which are connected - El Forum - 08-24-2012

[eluser]CroNiX[/eluser]
Look into JOINs using mysql to get related data from other tables and you can use GROUP_CONCAT() to grab all of the categories for each article.

Once you figure out the regular SQL to do the query you can convert it to active record.

get from 2 tables which are connected - El Forum - 08-24-2012

[eluser]UnknownPlayer[/eluser]
I tried with JOIN, but no success Sad

Can you give me code and i will check ?

get from 2 tables which are connected - El Forum - 08-24-2012

[eluser]CroNiX[/eluser]
Post what you have tried.

get from 2 tables which are connected - El Forum - 08-24-2012

[eluser]UnknownPlayer[/eluser]
I removed that code, now my code is doing 3 sql queries.
First get article info, then function that get cats for that article and get cat name for each cat, and at end save that object to $data->cats.

Can you give me example for join method, so i can do it on that way ?

get from 2 tables which are connected - El Forum - 08-24-2012

[eluser]vitoco[/eluser]
This is the query, but analize it, and read documentation to learn well.

Code:
SELECT

  a.id AS article_id ,

  a.name AS article_name ,

  COUNT( c.id ) AS num_categories ,

  GROUP_CONCAT( c.name ) AS categorie_names

 FROM

  article AS a

 LEFT JOIN

  article_cats AS ac

 ON

  ( a.id = ac.article_id )

 LEFT JOIN

  cats AS c

 ON

  ( ac.cat_id = c.id )

 GROUP BY

  a.id

if you need to add conditions, use WHERE and/or HAVING depending on the field

Saludos

get from 2 tables which are connected - El Forum - 08-24-2012

[eluser]DarkManX[/eluser]
That was a mistake vitoco... CroNiX was doing it right. the topic starter didnt try anything at all i guess, just want to get working code and cronix tried to force him to learn something by himself - you just blew it Sad

get from 2 tables which are connected - El Forum - 08-24-2012

[eluser]UnknownPlayer[/eluser]
I don't get JOIN at all, tomorrow i will analyze this code, and implement it in mine.
I just gave an example what i mean, this is not my tables..
Thank you all.

get from 2 tables which are connected - El Forum - 08-25-2012

[eluser]UnknownPlayer[/eluser]
Hi,
I analyzed this code, and i tried with right and only join method, result is the same, so can someone explain me what is the difference ?

get from 2 tables which are connected - El Forum - 08-25-2012

[eluser]UnknownPlayer[/eluser]
I have 1 more question, can this field categorie_names be an array with id and name of each categorie ?
Is this possible in mysql ?