5 Replies - 863 Views - Last Post: 19 March 2011 - 06:29 PM

#1 MrLuke187  Icon User is offline

  • D.I.C Head
  • member icon

Reputation: 81
  • View blog
  • Posts: 237
  • Joined: 09-July 10

[PHP + MySQL] Join in one field?

Posted 19 March 2011 - 08:36 AM

Hi there!

Situation:
I'm working on a Notebook for my website. Every entry has one or more category's. The MySQL Database looks like this:

Quote

Category <- Where the category's name and ID is stored
---
Entry <- Where the Values (text, etc) and ID from the Entry's are stored
---
EntCat <- Where the Entry-ID and the Category-ID are put together


Problem:
The Query, which is used to show all the latest entry's (on the start-site) looks like this:
SELECT bb_eintrag.headline, bb_eintrag.datum, bb_eintrag.preview, bb_kategorie.name AS "kategorie", bb_eintrag.id
FROM bb_eintrag, bb_kategorie, bb_ein_kat
WHERE bb_eintrag.id = bb_ein_kat.ein_id
AND bb_kategorie.id = bb_ein_kat.kat_id
ORDER BY bb_eintrag.datum DESC
LIMIT 0,20


Now, it works wonderful with entry's with only one category. As soon as I have an entry with two or more category's, it's shown twice (one line for each category).

My question now:
What can I do to get the Entry's with two or more category's to be shown in one line (like all category-names in one field or something). I'm thinking of a join, which puts all the category's from the "category"-table in one row in the result-set.

Hope you understand what i mean ;)

Greetings: Luke

Is This A Good Question/Topic? 0
  • +

Replies To: [PHP + MySQL] Join in one field?

#2 skyhawk133  Icon User is offline

  • Head DIC Head
  • member icon

Reputation: 1858
  • View blog
  • Posts: 20,275
  • Joined: 17-March 01

Re: [PHP + MySQL] Join in one field?

Posted 19 March 2011 - 09:31 AM

I'm actually going to move this to our MySQL forum since it's more of a database question. I was going to say a join is what you want, but on second though, you're trying to pull back a single column with a list of all the categories an entry belongs to. This may be better suited for a second query, or possibly a sub-query.
Was This Post Helpful? 1
  • +
  • -

#3 MrLuke187  Icon User is offline

  • D.I.C Head
  • member icon

Reputation: 81
  • View blog
  • Posts: 237
  • Joined: 09-July 10

Re: [PHP + MySQL] Join in one field?

Posted 19 March 2011 - 09:54 AM

I mean, it doesn't have to be in one column, anything that works would do it. But i think, using two query's (and that for all 30 entry's just to show the start-page) isn't very performant.

I mean, basically it's something that is used in any bloging-system. So how do they do this ? (To anyone out there who might wrote something like this himself. I know there are many blogging-systems out there, but most of them are way to complex, so just looking at their source code might take longer then finding another way).

Greetings: Luke
Was This Post Helpful? 0
  • +
  • -

#4 AdaHacker  Icon User is offline

  • Resident Curmudgeon

Reputation: 452
  • View blog
  • Posts: 811
  • Joined: 17-June 08

Re: [PHP + MySQL] Join in one field?

Posted 19 March 2011 - 10:56 AM

Well, the standard, database-agnostic method is to either use more than one query, or handle collapsing the categories in code. Standard SQL doesn't have any sort of facility for this, as far as I know.

Now, if you're talking MySQL specifically, that's a different story. You should be able to use the GROUP_CONCAT() function to get what you're looking for.
Was This Post Helpful? 3
  • +
  • -

#5 MrLuke187  Icon User is offline

  • D.I.C Head
  • member icon

Reputation: 81
  • View blog
  • Posts: 237
  • Joined: 09-July 10

Re: [PHP + MySQL] Join in one field?

Posted 19 March 2011 - 05:22 PM

Thx AdaHacker and skyhawk133, I'll look into this and get back to you guys.

Greetings: Luke
Was This Post Helpful? 0
  • +
  • -

#6 Atli  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 3635
  • View blog
  • Posts: 5,756
  • Joined: 08-June 10

Re: [PHP + MySQL] Join in one field?

Posted 19 March 2011 - 06:29 PM

The GROUP_CONCAT function really is great for these kind of situations, where the standard 2D result set is to simple for the data you need.

As an example of what you can do with it, I was creating a VIEW the other day for a forum system I'm doing; making MySQL create a Javascript-like array of forum sub-sections.
CREATE VIEW `section_details` AS
SELECT
    tls.*,
    CONCAT(
        '[', 
        IFNULL(
            GROUP_CONCAT(
                CONCAT('{"id":', sls.`id`, ',"name":"', sls.`name`, '"}') SEPARATOR ','
            ), 
            ''
        ), 
        ']'
    ) AS 'subsections_json'
FROM sections AS tls
LEFT JOIN sections AS sls
    ON tls.`id` = sls.`parent_id`
WHERE tls.`parent_id` IS NULL
GROUP BY tls.`id`;


The fields that generates look something like this:
[{"id":4,"name":"Subsection1"},{"id":5,"name":"Subsection2"},{"id":6,"name":"Subsection3"}]

I can pass that directly into Javascript or PHP and decode it into an object there. Very handy.
Was This Post Helpful? 2
  • +
  • -

Page 1 of 1