For each result in MySQL query, push to array (complicated)

Question

Okay, here's what I'm trying to do. I am running a MySQL query for the most recent posts. For each of the returned rows, I need to push the ID of the row to an array, then within that ID in the array, I need to add more data from the rows. A multi-dimensional array.

Here's my code thus far.

$query = "SELECT * FROM posts ORDER BY id DESC LIMIT 10"; 
$result = mysql_query($query);

while($row = mysql_fetch_array($result)){

            $id = $row["id"];
            $post_title = $row["title"];
            $post_text = $row["text"];
            $post_tags = $row["tags"];
            $post_category = $row["category"];
            $post_date = $row["date"];



}

As you can see I haven't done anything with arrays yet. Here's an ideal structure I'm looking for, just incase you're confused.

The master array I guess you could call it. We'll just call this array $posts. Within this array, I have one array for each row returned in my MySQL query. Within those arrays there is the $post_title, $post_text, etc.

How do I do this? I'm so confused.. an example would be really appreciated.

-Dylan

hookedonwinter · Accepted Answer · 2010-06-15 18:35:35Z

1

    $master[$id]['post_title'] = $post_title;
    $master[$id]['post_text'] = $post_text;
    // etc

or, less code. With this one, you can get rid of where you set all those variables:

    $master[$row["id"]]['post_title'] = $row["title"];
    $master[$row["id"]]['post_text'] = $row["text"];
    // etc

Edit in answer to comment:

foreach( $master as $row )
{
    echo $row['post_title'];
}

// or if you know the id

echo $row[$id]['post_title'];

edited Jun 15, 2010 at 18:35

answered Jun 15, 2010 at 18:24

hookedonwinter

12.7k19 gold badges64 silver badges75 bronze badges

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

2 Comments

Dylan Taylor Over a year ago

Ok cool - thanks. Now I'm trying to retrieve my data. How do I, for each post or ID, get the post_title from the array?

hookedonwinter Over a year ago

No worries. Glad we could help.

Community · Accepted Answer · 2023-11-13 20:22:11Z

1

here you have the complete reference for arrays, anyway a common way to do this is to use $myarray[] = $aa; to "push" into it.

    <?php
    $query = "SELECT * FROM posts ORDER BY id DESC LIMIT 10"; 
    $result = mysql_query($query);
    $posts = array();
    while($row = mysql_fetch_array($result)){
           $posts[] = $row;
          // second option, index the post by their id
          // $posts[$row["id"]] = $row;
          // third option
          /*
            $tmp = array();
            $tmp["title"] = $row["title"];
            $tmp["desc"] = $row["desc"];
            $posts[$row["id"]] = $tmp;
          */

    }
    ?>

edited Nov 13, 2023 at 20:22

CommunityBot

11 silver badge

answered Jun 15, 2010 at 18:21

Gabriel Sosa

7,9644 gold badges42 silver badges48 bronze badges

2 Comments

hookedonwinter Over a year ago

I like this way. It's nice and clean. The only issue is if OP doesn't want every single field from the result in the array, just a select few.

Gabriel Sosa Over a year ago

yeah, that is why I've added other options

Justin K · Accepted Answer · 2010-06-15 18:34:28Z

1

I tend to like:

$posts = array();
while ($row = mysql_fetch_array($result)) {
    $posts[] = array(
         'id' => $row['id'],
         'title' => $row['title'],
         'text' => $row['text']
    );
}

answered Jun 15, 2010 at 18:34

Justin K

2,7041 gold badge19 silver badges16 bronze badges

1 Comment

Dylan Taylor Over a year ago

K.. Now that I have my array, how do I retrieve the post_title or text from the each array inside the master array?

Collectives™ on Stack Overflow

For each result in MySQL query, push to array (complicated)

3 Answers 3

2 Comments

2 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related