Adding Categories....table join?

[eluser]afterspark[/eluser]
Hello,

I'm trying to add categories to my database application. I already have the database structure set up like so....

articles
-ID
-Name

categories
-ID
-Name

articlecategories
-articleID
-CategoryID

Before I added the categories I'd been using this query to get the results from my database

Code:

$query2 = $this->db->getwhere('articles', array('articleSlug' => $this->uri->segment(2)));

Now I need to add to the query something that will identify the articleID and then query the other tables to bring back the categories assigned to the article.

I'm new to this so I'm a little stuck at this point. Any help would be greatly appreciated.

[eluser]Armchair Samurai[/eluser]
Probably something like this:

Code:

$this->db->select('x.*');
$this->db->join('articlecategories AS y', 'x.ID = y.articleID');
$this->db->join('categories AS z', 'y.CategoryID = z.ID');
$this->db->where('z.name', $this->uri->segment(2));
$query2 = $this->db->get('articles AS x');

[eluser]afterspark[/eluser]
Thanks so much for the reply

Could you please explain what the x's and y's mean?

afterspark

[eluser]Armchair Samurai[/eluser]
The x and y are aliases... I mainly use them because I'm lazy and it takes less typing, especially with more complex joins. For example, these two queries are the same:

Code:

SELECT articles.*
FROM articles
JOIN articlecategories ON articles.ID = articlecategories.articleID
JOIN categories ON articlecategories.CategoryID = categories.ID
WHERE categories.name = 'foo'

// Same query as above, but fewer keystrokes.

SELECT x.*
FROM articles AS x
JOIN articlecategories AS y ON x.ID = y.articleID
JOIN categories AS z ON y.CategoryID = z.ID
WHERE z.name = 'foo'

[eluser]afterspark[/eluser]
Thanks a lot for the help!

I've got the code running without errors, but now I'm not sure how to display the categories that the articles belong to.

I'm using the query you supplied, and its not giving me an error, but I need to display a list of the categories and I'm not sure how I would go about doing that, or even accessing the category information.

thanks again for the help, I really appreciate it!

afterspark

[eluser]Armchair Samurai[/eluser]
If you want to display the category of each article as well, just add 'y.name AS category' to your select statement. You'll need to alias it because you have two columns called 'name' (one from articles table and one from the categories).

[eluser]afterspark[/eluser]
Thanks again for the reply. I'm stuck on something simple that I can't get past....

Here is my query

Code:

$this->db->select('x.*', 'y.name AS category');
$this->db->join('articlecategories AS y', 'x.id = y.articleid');
$this->db->join('categories AS z', 'y.categoryid = z.id');
$this->db->where('x.articleSlug', $this->uri->segment(2));
$query2 = $this->db->get('articles AS x');

After this point, when I try to use the row_array() function to retrieve the article data, everything works fine except the category name. Here is an example

Code:

$row2= $query2->row_array();
            
$data['name'] = $row2['articleName'];
$data['description'] = $row2['articleDescription'];
$data['url'] = $row2['articleURL'];
$data['category'] = $row2['category'];

$this->load->view('article_view', $data);

I then use <?=$name?> and so on to display the data in the view. But for some reason I get a "Message: Undefined index: category" when I try to use the category name.

I feel like I'm really close to getting this, and your help have been very much appreciated.

[eluser]Armchair Samurai[/eluser]
The SELECT statement should be a single string - that should do it.

[eluser]afterspark[/eluser]
Could you explain how that would work?

I'm totally new to active record this is my first attempt at a query using it.


and again, thanks so much!

[eluser]Armchair Samurai[/eluser]
Take a look at the User guide - $this->db->select() takes only two parameters, a string and a boolean. In your query, you're passing two strings.

If your not comfortable with AR, you can also just write your own query and run it through $this->db->query().

Code:

$this->db->select('x.*, y.name AS category');