MySQL Select FROM 3 tables AND put that in PHP array

Question

Sorry for bad english and bad title!

I have the table "post"

id    title
1     test Thread
2     hello
3     just

so have "tags"

tagid   tagname
1       test
2       russia
3       new site

so have a post_tags

tagid    postid
1        1
2        1
3        1

I need an array from var_dump next below:

$posts = array(
    1 => array(
        'title' => 'test Thread',
        'tags' => array(
            'test', 'russia', 'new site',
        ),
    ),
    2 => array(
        'title' => 'hello',
        'tags' => NULL
    ),
    3 => array(
        'title' => 'just',
        'tags' => NULL
    ),
)

I trying do it, but i getting not that what i want.

SELECT `post`.`id`, `post`.`title`, `tags`.`tagname` FROM `post` 
LEFT JOIN `post_tags` ON `post_tags`.`tagid` = `post`.`id` 
LEFT JOIN `tags` ON `post_tags`.`tagid` = `tags`.`tagid`

I getting in SQL next following:

id  title            tagname
1   test Thread     test
1   test Thread     russia
1   test Thread     newsite
2   hello           NULL
3   just            NULL

PHP

$query = mysql_query("SELECT `post`.`id`, `post`.`title`, `tags`.`tagname` FROM `post` 
    LEFT JOIN `post_tags` ON `post_tags`.`tagid` = `post`.`id` 
    LEFT JOIN `tags` ON `post_tags`.`tagid` = `tags`.`tagid`");
$posts = array();
while ($row = mysql_fetch_assoc($query))
{
    $posts[] = $row;
}

var_dump($posts);

Thank you!!!

you cannot get a multi-dimensional arrays back from a mysql database. you must do your own post processing to the results if you want it in that form. — dqhendricks
– dqhendricks, Commented Apr 10, 2012 at 21:38
FYI: escaping table/field names is only required if you're using reserved words. Dumping everything into backticks just makes the query that much harder to read. — Marc B
– Marc B, Commented Apr 10, 2012 at 21:45

Jon · Accepted Answer · 2012-04-10 21:40:03Z

2

The query is fine. You just need some logic in your loop:

while ($row = mysql_fetch_assoc($query))
{
    if (isset($posts[$row['id']])) {
        $posts[$row['id']]['tags'][] = $row['tagname'];
    }
    else {
        $posts[$row['id']] = array(
            'title' => $row['title'],
            'tags' => $row['tagname'] === null ? null : array($row['tagname'])
        );
    }
}

If you have already seen a row with the same post id then all you want from the current row is the tag name (so add this to the "tags" array). If it's the first time a row with this post id is seen just add it to $posts, being a little careful to set "tags" to either null or an array with one element.

answered Apr 10, 2012 at 21:40

Jon

439k85 gold badges756 silver badges820 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

dqhendricks · Accepted Answer · 2012-04-10 21:43:26Z

2

you cannot get a multi-dimensional arrays back from a mysql database. you must do your own post processing to the results if you want it in that form. Something like this maybe?

$posts = array();
while ($row = mysql_fetch_assoc($query))
{
    if (!isset($posts[$row['id']])) {
        $posts[$row['id']] = array();
        $posts[$row['id']]['title'] = $row['title'];
        $posts[$row['id']]['tags'] = array();
    }
    if ($row['tagname'] != null) $posts[$row['id']]['tags'][] = $row['tagname'];
}

answered Apr 10, 2012 at 21:43

dqhendricks

19.2k11 gold badges54 silver badges88 bronze badges

Comments

Alon Eitan · Accepted Answer · 2012-04-10 21:41:03Z

1

Try this:

while ($row = mysql_fetch_assoc($query))
{
    if( !isset( $posts[$row["id"]] ) ) {
        $posts[ $row["id"] ] = array( "title" => $row["title"], "tags" => array() );
    }
    array_push( $posts[ $row["id"] ][ "tags" ], $row["tagname"] );
}

I can't debug it, so tell me if you get any errors

answered Apr 10, 2012 at 21:41

Alon Eitan

12k8 gold badges51 silver badges61 bronze badges

Collectives™ on Stack Overflow

MySQL Select FROM 3 tables AND put that in PHP array

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related