Mysql/php novice to ninja list categories for each joke and order by category

Databases
1

I have converted the article database to an article database.
So I have a list of articles on a web page. This works just fine. Now I would like to add the categories for each article. But to really take this further what I would like the final result page to be is a list of articles sorted by category and each category(s) listed with each article.
I believe I need some type of join for this. So I have listed my last attempt at the bottom. I know I am lost in the forest, so can anyone help me get back on the path.
This is from the book. So I have the following 3 tables;
TABLE article
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
articletext TEXT,
articledate DATE NOT NULL,
authorid INT
) DEFAULT CHARACTER SET utf8 ENGINE=InnoDB;

TABLE category (
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(255)
) DEFAULT CHARACTER SET utf8 ENGINE=InnoDB;

(index)TABLE articlecategory (
articleid INT NOT NULL,
categoryid INT NOT NULL,
PRIMARY KEY (articleid, categoryid)
) DEFAULT CHARACTER SET utf8 ENGINE=InnoDB;

So I am trying a query like this.
$sql = ‘SELECT categoryid,articleid, category.id, name FROM articlecategory
INNER JOIN category
ON
categoryid = category.id’;
$result = $pdo->query($sql);

foreach ($result as $row)
{
$cat = array(
‘id’ => $row[‘id’],
‘categoryid’ => $row[‘categoryid’],
‘catname’ =>$row[‘name’],
‘articleid’ =>$row[‘articleid’]);
}
On the display page I have a foreach for the article and inside of this a foreach for $cat.
I was closer at one time than I am now.
Thank you
Darrel

2

I would add the name of the table always, not only for category.id so you know that the id field comes from that particular table. Apart from that, you got the idea.

Since categoryid and category.id are supposed to have the same value, you don’t need both in the SQL result. Unless you want to make sure.

a.categoryid, a.articleid, c.name FROM articlecategory as a INNER JOIN category as c ON a.categoryid=c.id ORDER BY c.name

This would be valid if you need the article id only. There’s no field article_title, and I assume that articletext would be the whole text, too much to list.

Of course, if you want to order by the category id, just change the ORDER BY clause to ORDER BY a.categoryid

3

[quote=“darrel2, post:1, topic:114331, full:true”]
I have converted the article database to an article database.[/quote]
the subtlety of this is way beyond my comprehension

(i guess i should let you know that i traded in my old subtlety for a nuance)

anyhow…

okay, i think i understand this, and the query will be a bit trickier than what you’ve got so far

you need to select all categories, find all articles for each category (using a LEFT OUTER JOIN to cover those cases where a category has no articles), and then join each article to its other categories… which means you have to have a couple of the tables in the query twice each, and you have to use aliases for this

SELECT category.name AS categoryname , article.name AS articlename , GROUP_CONCAT(othercategory.name) AS othercategories FROM category LEFT OUTER JOIN articlecategory ON articlecategory.categoryid = category.id LEFT OUTER JOIN article ON article.id = articlecategory.articleid LEFT OUTER JOIN articlecategory AS otherarticlecategory ON otherarticlecategory.articleid = articlecategory.articleid LEFT OUTER JOIN category AS othercategory ON othercategory.id = otherarticlecategory.categoryid GROUP BY category.name ASC , article.name ASC

4

My apologies for the ambiguity. I am blind and use a screen reader. I had to try to post 3 times before I figured out that the button which my screen reader says “new topic” is the submit button.
So I did not proof read it like I should have.
Right now I have to assume your answer is correct. But it is way over my head for the moment. So how do I set up my array and foreach loop to get the article and it’s categories. When I get this working I will go back and study this and chapter 11 much harder.
Also it should have said I changed joke cms to article cms and the article table I listed is from the book. The real table does have a articletitle column.

5

sorry, can’t help you with that

run the query and see what it produces, the results will have three columns that should look like this –

category1 articleA category1,category6,category7
category1 articleB category1,category5
category1 articleC category1,category6,category9
category2 articleX category2
category3 articleY category3,category7,category9

notice the third column, the result of the GROUP_CONCAT function, contains a list of categories that the article belongs to

p.s. did you like my nuance joke? :slight_smile:

6

First let me say, I know this is old hat to you but your query is a thing of beauty.
I forgot I could just go into php my admin and run the query.
My question on the array was dumb. I guess I was just tired.
I still have the problem of having duplicates for each article with more than one category. I know it is because the table articlecategory has more rows than the article table. I just do not know how to get past it.
The duplicates do have category1, category2 however. So this is an improvement.
Is it possible to create a temporary table from the results and query this table and ask mysql to ignore duplicates?
Here is the query with all the columns.

$sql = 'SELECT category.name AS categoryname
     , article.articletitle  AS articletitle, article.articletext as articletext, article.articlev as articlev, article.visible as visible
     , GROUP_CONCAT(othercategory.name) AS othercategories
  FROM category
LEFT OUTER
  JOIN articlecategory
    ON articlecategory.categoryid = category.id
LEFT OUTER
  JOIN article
    ON article.id = articlecategory.articleid
LEFT OUTER
  JOIN articlecategory AS otherarticlecategory
    ON otherarticlecategory.articleid = articlecategory.articleid
LEFT OUTER
  JOIN category AS othercategory
    ON othercategory.id = otherarticlecategory.categoryid
GROUP
    BY category.name ASC
     , article.articletitle  ASC';

PS. I did get your joke. I am still amazed you actually understood my problem by the way I described it.

7

No offense but… me too!

8

[quote=“darrel2, post:6, topic:114331, full:true”]
I still have the problem of having duplicates for each article with more than one category.[/quote]
i’m not sure i understand what you mean by duplicates

is it …

(a) that a category appears more than once in the result set?

category1 articleA category1,category6,category7
category1 articleB category1,category5
category1 articleC category1,category6,category9

here there are 3 articles in category1

or

(b) that the same category appears in an article’s GROUP_CONCAT list of categories?

the first one, (a), is handled by your php logic while looping over the query’s results – show category1 with some kind of header markup (e.g. an H3 tag, and then the articles would be listed under it with a UL)

the second one, (b), can be avoided with a small addition to the query –

for this join –

LEFT OUTER JOIN articlecategory AS otherarticlecategory ON otherarticlecategory.articleid = articlecategory.articleid
just add the line

   AND otherarticlecategory.categoryid <> articlecategory.categoryid

so in the example i gave for category2, articleX would have no “other categories” returned in the GROUP_CONCAT

helps?

9

Actually neither A or B. Here is what a sample out put would look like.

<html> page)
<h1>List of articles</h1>
<p>Category(s) </p>
<h3> article title  as a link to see the full article </h3>
<p> first 255 characters of the article text. </p> 
</html>

If the above article pertains to more than one category, e.g. finishing, sanding
The article is listed twice on the same page or in the same results.

<h3> article one </h3>
<h3> article one </h3>

To see the problem in action:

This is my test site.

10

@darrel2 I really want to fix this, can you point me at the screen reader software you are using so I can simulate what is going on. There may be some simple markup changes we can make to fix this up.

3 Likes
11

sorry, but i cannot see why your test site is duplicating the articles

(also, it is wider than my browser, resulting in the left side being cut off, with no horizontal scrollbar… sorry, but this appears to be a css issue?)

you’re gonna need someone experienced in php (if that’s what you’re using) to debug it

is there any chance you can run the query for that page, and print the query results instead of showing the page itself? i think phpmyadmin will let you export a csv…

12

I think I have made this more difficult than it needs to be.
I should be able to use array_unique to fix the problem. It is not your query.
I do infact do this when I edit an article. On the edit form all the categories are listed as check boxes and the proper ones are checked by a select. But it uses a multi (whatever the official name is) array and is also based on a single article ID. Also 3 querys and about 100 lines of code.
I am sorry about the page display problem. I am also trying to learn Boot strap. I will use php my admin and print the results for you. You have been so great.

13

Here is the results from PHP MY admin
Notice a duplicate only acurs with an article where other category has more than one value. I marked them with ***

Category: Finishing
Article title: Dying Wood
article text: Larry Martin explains how he uses water born dyes …
Published status: YES
members only: NO
Other categoryFinishing
Next article
Finishing
When are you done sanding
When to Stop Sanding? Depending on the finish, pro…
YES
YES
Finishing
This next article is listed twice. Notice it has two values for other category (finishing, informational)
*** Finishing
Working With Poplar
By Chris Baylor, About.com Guide
Poplar is a spe…
YES
YES
Finishing,informational
informational
Molding Shapes Described
Here are some verbal descriptions of various moldi…
YES
NO
informational
informational
Types of Wood for Woodworking
By Jeff Strong from Woodworking For Dummies
Soli…
YES
YES
informational
informational
Wood Screw Pilot Hole Chart
This is an easy to read table for drilling pilot h…
YES
NULL
informational
*** Here it is again
informational
***Working With Poplar
By Chris Baylor, About.com Guide
Poplar is a spe…
YES
YES
Finishing,informational

14

[quote=“darrel2, post:13, topic:114331, full:true”]
Notice a duplicate only acurs with an article where other category has more than one value. [/quote]
i don’t doubt you

pretty busy today, can’t work on it till tomorrow, but i’m going to whip up some test data for myself and debug my query… i thought i had it figured out, but three decades of experience with SQL teaches me that all queries are unreliable until thoroughly tested!!

15

Whatever you need. Your help is great. If you want you can send me a private message with your email and I will send you the address for my php admin and the credentials you need to get in.

16

sorry for the delay…

i have tested my query and it does work as i expected

here is my test data, using the same articles and categories in my sample data above –

CREATE TABLE article ( id INTEGER , articletext VARCHAR(37) ); INSERT INTO article VALUES ( 111 , 'ArticleA' ) ,( 222 , 'ArticleB' ) ,( 333 , 'ArticleC' ) ,( 444 , 'ArticleX' ) ,( 555 , 'ArticleY' ) ; CREATE TABLE category ( id INTEGER , name VARCHAR(9) ); INSERT INTO category VALUES ( 1 , 'category1' ) ,( 2 , 'category2' ) ,( 3 , 'category3' ) ,( 4 , 'category4' ) ,( 5 , 'category5' ) ,( 6 , 'category6' ) ,( 7 , 'category7' ) ,( 9 , 'category9' ) ; CREATE TABLE articlecategory ( articleid INTEGER , categoryid INTEGER , PRIMARY KEY (articleid, categoryid) ); INSERT INTO articlecategory VALUES ( 111 , 1 ) ,( 111 , 6 ) ,( 111 , 7 ) ,( 222 , 1 ) ,( 222 , 5 ) ,( 333 , 1 ) ,( 333 , 6 ) ,( 333 , 9 ) ,( 444 , 2 ) ,( 555 , 3 ) ,( 555 , 7 ) ,( 555 , 9 ) ;

then i tried my query, and it produces exactly what i thought it should –

SELECT category.name AS categoryname , article.articletext , GROUP_CONCAT(othercategory.name ORDER BY othercategory.name ) AS othercategories FROM category LEFT OUTER JOIN articlecategory ON articlecategory.categoryid = category.id LEFT OUTER JOIN article ON article.id = articlecategory.articleid LEFT OUTER JOIN articlecategory AS otherarticlecategory ON otherarticlecategory.articleid = articlecategory.articleid LEFT OUTER JOIN category AS othercategory ON othercategory.id = otherarticlecategory.categoryid GROUP BY category.name ASC , article.articletext ASC

results –

categoryname   articletext   othercategories
category1      ArticleA      category1,category6,category7
category1      ArticleB      category1,category5
category1      ArticleC      category1,category6,category9
category2      ArticleX      category2
category3      ArticleY      category3,category7,category9
category4      null          null       
category5      ArticleB      category1,category5
category6      ArticleA      category1,category6,category7
category6      ArticleC      category1,category6,category9
category7      ArticleA      category1,category6,category7
category7      ArticleY      category3,category7,category9
category9      ArticleC      category1,category6,category9
category9      ArticleY      category3,category7,category9

so any duplication is down to either your data or your php code

17

I think I understand. I setup my array to get the articles and then the category. I think I need to build it on the category table instead. I will give it a shot. Thanks for all your kind help.
Darrel

18

i have no idea what “setting up an array” means

most php code i’ve seen (and remember, i don’t do php) simply involves a loop over the query results

you need a “next/prev” test on the category, to print out a new category heading, and then simply print out each article line – no need for any arrays!

19

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.