Return a nested array of rows in SELECT statement?

Question

I'm trying to return an nested array from a MYSQL query. I have a table of posts, users, keywords, and categories and I'd like to optimize the queries so I don't have so many selects. I currently have 4 SELECT queries for one post, each looping through a foreach, and creating a nested array in PHP. Is there a way to consolidate those into less queries, and faster execution?

Here's what one row would look like. The keywords, category, and author numbers are all ids in their respective tables.

Posts table:

| id | title |    content     | keywords | category | author |
|----|-------|----------------|----------|----------|--------|
|  1 | Test  | Lorem ipsum... | 1, 2     |        1 |      1 |
|----|-------|----------------|----------|----------|--------|

Keywords table:

| id |   name   |   url    |  description   |
|----|----------|----------|----------------|
|  1 | keyword1 | keyword1 | Lorem ipsum... |
|  2 | keyword2 | keyword2 | Lorem ipsum... |
|----|----------|----------|----------------|

User table:

| id |   name   |    email     |
|----|----------|--------------|
|  1 | John Doe | [email protected] |
|----|----------|--------------|

Category table:

| id |   name    |    url    |
|----|-----------|-----------|
|  1 | Category1 | category1 |
|----|-----------|-----------|

Here's the output I'd like to achieve:

Array
(
    [0] => Array
    (
        [id] = 1
        [title] = Test
        [content] = Lorem ipsum dolor sit amet, consectetur adipisicing elit. Alias, sapiente assumenda ratione dicta cumque accusantium id labore cupiditate maiores obcaecati repudiandae at eum fuga doloremque commodi. Quidem, nulla cupiditate aperiam!
        [keywords] => Array
            (
                [0] => Array
                (
                    [id] => 1
                    [name] => keyword1
                    [url] => keyword1
                    [description] => Lorem ipsum dolor sit amet, consectetur adipisicing elit. Asperiores, dolorem, consectetur voluptatem amet hic placeat alias rerum unde quis quia aperiam officia aliquam incidunt sit fugit quo iusto porro repellat!
                )
                [1] => Array
                (
                    [id] => 2
                    [name] => keyword2
                    [url] => keyword2
                    [description] => Lorem ipsum dolor sit amet, consectetur adipisicing elit. Magnam, culpa, repudiandae voluptatibus odit nam id sed maxime ullam quia accusamus minima nisi! Dolor, doloremque similique voluptatibus at eos vitae id?
                )
        )
        [category] => Array
        (
            [id] => 1
            [name] => Category1
            [url] => category1
        )
        [author] => Array
        (
            [id] => 1
            [name] => John Doe
            [email] => [email protected]
        )
    )
)

Combine all the tables into a single query with a JOIN. Then loop through these rows and create the appropriate entries in the nested array. — Barmar
– Barmar, Commented Sep 14, 2013 at 19:47
How will the keywords work, since there's always a variable number of keywords? — daviesgeek
– daviesgeek, Commented Sep 14, 2013 at 19:57
You mean keywords? You should normalize your schema instead of using a comma-separated list. — Barmar
– Barmar, Commented Sep 14, 2013 at 19:59
If you can't do that, you'll have to do that part as a separate query. But all the normalized fields can be done in a join. — Barmar
– Barmar, Commented Sep 14, 2013 at 20:00

gwc · Accepted Answer · 2013-09-14 21:09:07Z

First off, you should normalize your data, Create a new table for post_keywords that looks something like:

| post_id | keyword_id |
|---------|------------|
|       1 |          1 |
|       1 |          2 |
|---------|------------|

Then, it's a simple JOIN to get the data you need.

Once you have a post_keywords table, you can do something like:

    $db = new PDO('mysql:host=localhost;dbname=<SOMEDB>', '<USERNAME>', 'PASSWORD');

    $sql = "
       SELECT p.id      as p_id
          ,p.title   as p_title
          ,p.content as p_content
          ,c.id      as c_id
          ,c.name    as c_name
          ,c.url     as c_url
          ,u.id      as u_id
          ,u.name    as u_name
          ,u.email   as u_email
          ,GROUP_CONCAT( CONCAT( k.id, '|', k.name, '|', k.url, '|', k.description )
                         SEPARATOR '||' ) as keywords

      FROM posts p

      LEFT OUTER
      JOIN post_keywords pk
        ON pk.post_id = p.id

      LEFT OUTER
      JOIN keywords k
        ON k.id = pk.keyword_id

      LEFT OUTER
      JOIN category c
        ON c.id = p.category

      LEFT OUTER
      JOIN user u
        ON u.id = p.author

     GROUP BY p.id";

    $final = array();
    $results = $db->query( $sql );
    while ( $row = $results->fetch(PDO::FETCH_ASSOC) ) {
            $k = array();
        foreach ( ecplode( '||', $row[ 'keywords' ] as $kw ) {
            $kw = explode( '|', $kw );
            $k[] = array( 'id' => $kw[0], 'name' => $kw[1], 'url' => $kw[2], 'description' => $kw[3] );
        }
        $final[] = array( 'id'       => $row[ 'p_id' ]
                , 'title'    => $row[ 'p_title' ]
                , 'content'  => $row[ 'p_content' ]
                , 'keywords' => $k
                , 'category' => array( 'id' => $row[ 'u_id' ], 'name' => $row[ 'u_name' ], 'url' => $row[ 'c_url' ] )
                , 'author'   => array( 'id' => $row[ 'u_id' ], 'name' => $row[ 'u_name' ], 'email' => $row[ 'u_email' ] )
                );
    }

Please note that this is untested, so may not be 100%. The SQL assumes that it is possible to have posts without keywords, and/or with null category or author. If that is not the case, "LEFT OUTER" can be removed from the JOINs.

The $final array should be formatted as you required. SQL can be reviewed on SQL Fiddle at http://sqlfiddle.com/#!2/fd630/1

Update

You can use the following SQL instead of the SQL above to do the same thing with your current tables (doesn't require post_keywords). It does assume that all keyword ids are separated by a comma with no embedded spaces:

SELECT p.id      as p_id
      ,p.title   as p_title
      ,p.content as p_content
      ,c.id      as c_id
      ,c.name    as c_name
      ,c.url     as c_url
      ,u.id      as u_id
      ,u.name    as u_name
      ,u.email   as u_email
      ,GROUP_CONCAT( CONCAT( k.id, '|', k.name, '|', k.url, '|', k.description )
                     SEPARATOR '||' ) as keywords

  FROM posts p

  LEFT OUTER
  JOIN keywords k
    ON k.id REGEXP REPLACE(p.keywords,',','|')

  LEFT OUTER
  JOIN category c
    ON c.id = p.category

  LEFT OUTER
  JOIN user u
    ON u.id = p.author

 GROUP BY p.id

SQLFiddle for this is at http://sqlfiddle.com/#!2/2e1ec7/2

Hmm...I tried it, but I still can't end up with a nested array of keywords?
Okay I'll try that…might take me awhile to implement :) but I'll work on it and let you know how it works out…

Sergey Bulavkin · Accepted Answer · 2013-09-14 19:47:26Z

0

Use JOIN to reduce the amount of select

answered Sep 14, 2013 at 19:47

Sergey Bulavkin

2,4262 gold badges20 silver badges26 bronze badges

Collectives™ on Stack Overflow

Return a nested array of rows in SELECT statement?

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related