If an article has related categories then these are shown on the individual article page.
Let's add the article categories to the articles on the index page too.
As we saw earlier to get a list of articles along with their categories we need a query like this that
joins the article and category tables via the join table.
Note how the article data are repeated.
If the article is related to more than one category.
This is fine if we want all the article records and their categories.
However on the index page we're using the limit and offset clauses to paginate the list of articles.
If I add these to the query.
Along with an ORDER BY clause when we execute this esky Well we get a page of results but this article
is repeated once for each category it's related to.
We can't use this to display a page of article records as there aren't enough articles for a full page
and the number of unique articles we get back depends on how many categories an article is related to.
So what options do we have for getting the categories for each article on the index page.
We could get the page as we're doing now without any joins.
And then when we display each article get the categories far that article in a separate query.
This would mean many queries to the database though and wouldn't be very efficient.
We could loop around these results collect the article ideas in an array and then get the associated
categories for those I.D. This would only be to query's to the database per page.
There is a third way we could do it with just one query.
Let's start with the ASCII wealth for a single page which is what we're using now.
This will get a page of article Ricardos based on the limit and offset clauses.
If I put brackets around this query.
This makes it a sub query.
We can select data from this just as though it was a normal table.
We have to add an alias when selecting from a sub query like this by putting As and a table name of
our choice after the select.
Like this.
If I run this query we get the same results as before.
However now we can join these results to the category table.
So if I add those joins to this ask you well.
Specifying the join condition using the alias we just added instead of on the article table directly.
When we execute this esky well.
We get several Ricardos including the categories for each article.
However even though we have more than four records the number of unique articles is limited to for the
article data are repeated for each category.
The article is related to.
So we can use this query to get pages of articles including related categories in a single query.
First let's make the existing query in the Get page method into a sub query bank closing it in brackets
and adding an alias.
Then we'll select all the columns from that sub query and join that to the article category.
Join table.
And then that table to the category table.
In addition to the data from the article table.
We also want the category name which we'll rename as we did in the get with categories method we added
earlier.
We don't need to change anything else in this method.
Binding the values and so on.
Now when we view the index page we get the article data as before but several of the articles are repeated.
These are the articles that have more than one related category.
To see what each category name is.
Let's print this out after the article title.
Now we can see the articles that are repeated and repeated once for each different category.
They're related to.
So before we return this data from the get page method let's process it so that we consolidate all the
repeated article records into one for each article with the category names in an array.
We'll start by assigning the data to a variable instead of returning it.
Then let's create a new array that will contain the consolidated data and loop around the data that's
come from the database.
To add to the article data to the new array.
First let's assign the article idae from the row to a variable.
Then we can add the row to the new array indexed on the article idae.
As we we'll do pass through the data.
We only want to add each article to the new array wants to do this.
Let's keep track of the idea of the article from the previous time through the loop.
So before the loop Let's add a variable which will initialize to no.
Then inside the loop.
If the eidy if the article on this row isn't equal to the idea of the article on the previous row.
Only then do we add to the row to the new array.
If the idea is the same we don't add it.
Then at the end of the loop we set the previous eidy to the current one.
Then at the end of the function.
Let's return the new array.
Now in the browser we only get the articles once so for per page.
Next we can add the categories to each article in the new array.
If this is a new article let's add an array element to the row for the category names which will initialize
to an empty array.
Then each time through the loop.
Let's append the value of the category name far this row to this array.
Then before we return the new array.
Let's print out its contents so we can see what it contains.
Now in the browser if we view the source we can see that each article has a new element for the category
names.
And if the article is related to more than one category this element contains multiple values.
So now we can get rid of this vardhan line and we can display the categories in the index page.
So instead of printing out the category name element if the category names every element isn't empty
will add a paragraph with some text then loop around the array and print out each name in turn.
Now in the browser each article is displayed with its related categories.
If it has any.
The pagination still works showing 4 articles per page and were doing all this in a single query to
the database.