I'm trying to write a MySQLi query- and some fellow PHP code - that will imulate functionality from Wordpress and other blog engines: categories.
As MySQLi can be either OO or procedural, I'd prefer to figure this with OO-style code.
Let's assume my tables are the following:
posts:
+-------------------------
| posts_id | posts_content |
| 1 | I like oatmeal |
| 2 | Bacon + Eggs |
| 3 | Ninjas are awesome |
+----------+--------------
posts2cats:
+----------+-----------+--
| rel_id | posts_id | cat_id |
| 1 | 1 | 1 |
| 2 | 1 | 2 |
| 3 | 2 | 1 |
| 4 | 3 | 2 |
| 5 | 3 | 3 |
+----------+-----------+--
cats:
+----------+--------------
| cat_id | cat |
| 1 | Breakfast |
| 2 | Opinion |
| 3 | Truth |
+----------+--------------
I want to generate (echo/print) content like the following:
Post: Snap, Crackle, pop!
Categories: Breakfast, Opinion
Post: Bacon + Eggs
Categories: Breakfast
Post: Ninjas are awesome
Categories: Opinion, Truth
Well, the formatting may have messed with the tables a bit, but they should be readable. I can handle this w/o the categories, but with categories, I can't get my head around it.
How can this be done?
This Question has been solved and asker verified All Experts Exchange premium technology solutions are available to subscription members.
Experts Exchange has been collecting answers to technology questions since 1996…3 million and counting! If you have a question, chances are we already have your answer.
If you can't find the exact answer you're looking for, ask our exclusive community of 50,000 experts. You’ll get a personalized answer from a trusted professional.
Thousands of free tech tips, tricks, how-to’s and tutorials are available in our peer reviewed articles section. See for yourself how smart our experts are, no login required.
Save solutions to your questions, answers you’ve discovered through searching plus helpful articles in your personal knowledgebase for easy future access.
Access the answers to your technology questions today.
30-day free trial. Register in 60 seconds.
Members of the expert community talk about why the experience at Experts Exchange is different than what you will find anywhere else.
Try it out and discover for yourself.
30-day free trial. Register in 60 seconds.
Join the community of experts here and help other tech pros by answering question in your area of expertise. You can earn FREE access to all Experts Exchange's premium features and resources.
ms!:
awesome stuff - very helpful. works great.
could you do a favor talk me through
SELECT a_t.posts_content, GROUP_CONCAT((SELECT cat FROM cats WHERE cat_id = b_t.cat_id) SEPARATOR ', ') AS cat_names FROM posts AS a_t LEFT JOIN posts2cats b_t ON(a_t.posts_id = b_t.posts_id) GROUP BY a_t.posts_content ORDER BY a_t.posts_id;
real quick? (what's up with a_t and b_t?)
I'm just trying to understand better so I can learn from your great answer.
Business Accounts
Answer for Membership
by: mensuckPosted on 2006-08-25 at 21:40:28ID: 17394554
If I am understanding you, then you can do something like below! mysql (5)
returns...
Showing rows 0 - 2 (3 total, Query took 0.0001 sec)
posts_content | cat_names
I like oatmeal | Breakfast, Opinion
Bacon + Eggs | Breakfast
Ninjas are awesome | Opinion, Truth
example....
SELECT a_t.posts_content, GROUP_CONCAT((SELECT cat FROM cats WHERE cat_id = b_t.cat_id) SEPARATOR ', ') AS cat_names FROM posts AS a_t LEFT JOIN posts2cats b_t ON(a_t.posts_id = b_t.posts_id) GROUP BY a_t.posts_content ORDER BY a_t.posts_id;
// tables
CREATE TABLE cats (
cat_id int(10) unsigned NOT NULL auto_increment,
cat varchar(255) collate latin1_general_ci NOT NULL,
PRIMARY KEY (cat_id),
KEY cat (cat)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci;
INSERT INTO cats VALUES (1, 'Breakfast');
INSERT INTO cats VALUES (2, 'Opinion');
INSERT INTO cats VALUES (3, 'Truth');
CREATE TABLE posts (
posts_id int(10) unsigned NOT NULL auto_increment,
posts_content varchar(255) collate latin1_general_ci NOT NULL,
PRIMARY KEY (posts_id)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci;
INSERT INTO posts VALUES (1, 'I like oatmeal');
INSERT INTO posts VALUES (2, 'Bacon + Eggs');
INSERT INTO posts VALUES (3, 'Ninjas are awesome');
CREATE TABLE posts2cats (
rel_id int(10) unsigned NOT NULL auto_increment,
posts_id int(10) unsigned NOT NULL,
cat_id int(10) unsigned NOT NULL,
PRIMARY KEY (rel_id),
KEY posts_id (posts_id,cat_id)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci;
INSERT INTO posts2cats VALUES (1, 1, 1);
INSERT INTO posts2cats VALUES (2, 1, 2);
INSERT INTO posts2cats VALUES (3, 2, 1);
INSERT INTO posts2cats VALUES (4, 3, 2);
INSERT INTO posts2cats VALUES (5, 3, 3);
ms!