PDO Returned array into more accessible array

Question

Information

This should be a fairly simple question. On my website I have a post_categorys in my database that has a structure like below

    ID    |    Name
    1     |   Sports
    2     |   Health
    3     |   Movies
    4     |   Music

Now when I save a blog post I save the category_id such as 1 for movies in the blog_post table.

When printing out the blog post to my users I want to be able to get the blog post category name by something like $categorys[$blog_cat_id']['name']

Here is how I get all categorys for $categorys used above.

function getCategorysArray(){
    $STH = $this->database->prepare('SELECT * FROM project_categorys');
    $STH->execute();
    return $STH->fetchAll();
}

But this returns the array in this format

Array
(
[0] => Array
    (
        [id] => 1
        [0] => 1
        [name] => Sports
        [1] => Sports
    )

[1] => Array
    (
        [id] => 2
        [0] => 2
        [name] => Health
        [1] => Health
    )

[2] => Array
    (
        [id] => 3
        [0] => 3
        [name] => Movies
        [1] => Movies
    )

[3] => Array
    (
        [id] => 4
        [0] => 4
        [name] => Music
        [1] => Music
    )

Therefor I am unable to use my desired method to get the category name ($categorys[$blog_cat_id']['name'])

Question

Is there a way I can remap the array somehow so that $array[1]['name'] = Sports would be the output?

user399666 · Accepted Answer · 2013-11-06 13:59:43Z

2

Try this:

function getCategorysArray(){
    $STH = $this->database->prepare('SELECT * FROM project_categorys');
    $STH->execute();
    $res = $STH->fetchAll(PDO::FETCH_ASSOC);
    $categories = array();
    foreach($res as $row){
        $categories[$row['id']] = $row;
    }
    return $categories;
}

answered Nov 6, 2013 at 13:59

user399666

20k7 gold badges49 silver badges68 bronze badges

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

Comments

lonesomeday · Accepted Answer · 2013-11-06 14:05:51Z

1

So, basically, you want the array indexed by the value of id?

First, you can simplify the array by calling fetchAll(PDO::FETCH_ASSOC). This means you will no longer have those unnecessary 0 and 1 indexes in your returned array.

Then the simplest solution is simply to loop over the returned array.

$STH = $this->database->prepare('SELECT * FROM project_categorys');
$STH->execute();
$results = $STH->fetchAll(PDO::FETCH_ASSOC);
$ret = array();
foreach ($results as $row) {
    $ret[$row['id']] = $row['name'];
}
return $ret;

Perhaps even better, we can remove the fetchAll call, since PDOStatement is Traversable:

$STH = $this->database->prepare('SELECT * FROM project_categorys');
$STH->execute();

$ret = array();
foreach ($STH as $row) {
    $ret[$row['id']] = $row['name'];
}
return $ret;

edited Nov 6, 2013 at 14:05

answered Nov 6, 2013 at 14:00

lonesomeday

239k54 gold badges330 silver badges329 bronze badges

2 Comments

Harry Over a year ago

Works perfectly, thank you. One last question what do you mean by Traversable?

lonesomeday Over a year ago

@Harry It means that PDOStatement implements the Traversable interface. This means you can run foreach on it.

AlexP · Accepted Answer · 2013-11-06 14:00:03Z

1

public function getCategories()
{
  if (null == $this->categories) {
    $stmt = $this->database->preapre('SELECT id, name FROM project_categorys')->execute();
    $categories = $stmt->fetchAll();

    foreach($categories as $category) {
      $this->categories[$category['id']] = $category;
    }
  }
  return $this->categories;
}

answered Nov 6, 2013 at 14:00

AlexP

9,8571 gold badge28 silver badges44 bronze badges

Collectives™ on Stack Overflow

PDO Returned array into more accessible array

3 Answers 3

Comments

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related