Split query into multiple arrays based on column name

Question

I want to run a query that gets all the data from a database then have the data split into arrays for each column. With this I intend to dynamically populate html. I am not very experienced with php and could use some assistance with how to put my query into multiple arrays depending on what column it was in.

Example: For the column name I want an array $itemName[] and it will contain every item name in asc order. Then for the image column I want an array $itemImage[] for every image/image url in the same order.

With this I plan to run a for loop where as x increases it will go through each diff array and pull from the specified location. There are no null values in my DB so I don't need to worry about that.

Any help you can give me with the writing the query into multiple arrays based on the column name is appreciated.

    $mPos = array(mPos1, mPos2, mPos3, mPos4);

    for (x=0; x<4; x++){

    echo "<div class="$mPos[x]"> <div class="$mPos[x] . '_1'">"$title[x]"</div><div class="$mPos1 . '_2'">"$image[x]"</div>

Paul · Accepted Answer · 2012-08-31 14:37:53Z

1

Still doesn't make sense for me to separate it that way, but here you go.

Since you didn't provide a database/table structure, I will assume your db table got the following columns:

itemId | itemName | itemImage | itemDescription

In PHP you loop through the result row for row and populate your arrays like

foreach ( $result AS $row ) {
  $itemNames[$row->itemId] = $row->itemName;
  $itemImages[$row->itemId] = $row->itemImage;
  $itemDescriptions[$row->itemId] = $row->itemDescription;
}

EDIT: After question was updated and now includes the HTML output, I'd suggest something like this.

foreach ( $result AS $row ) {
  $items[$row->itemId] = array(
    'name' => $row->itemName,
    'image' => $row->itemImage,
    'description' => $row->itemDescription,
    'price' => $row->itemPrice,
    'link' => $row->itemLink,
  );
}
$x = 0;
while ($x<4) {
  $x++;
  $item = array_shift($items);
  echo '<div class="mPos'.$x.'"> 
    <div class="mPos'.$x.'_1">"'.$item['name'].'"</div>
    <div class="mPos'.$x.'_2">"'.$item['price'].'"</div>
    <div class="mPos'.$x.'_3"><a href="'.$item['link'].'">
      <img src="'.$item['image'].'" /></a>
    </div>
  </div>';
}

edited Aug 31, 2012 at 14:37

answered Aug 31, 2012 at 14:13

Paul

9,0523 gold badges30 silver badges48 bronze badges

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

4 Comments

weewa Over a year ago

why does it not make sense to separate it this way? I want to list items in specific style and be able to do it for a lot of pages. This way I can write the code for one item and use a loop to populate it for as many instances that it finds in the database. Splitting the query by columns lets me control the design. Could you elaborate on the errors of my plan?

Paul Over a year ago

Each db table row probably defines a single item. By separating the item attributes into different arrays the danger of loosing the relationship is present. Reading your question and comments, I am wondering how your design looks like, but in any case I think design shouldn't influence the code (although good HTML output is needed to give it a nice design).

weewa Over a year ago

Thanks for the help! Does my design make more sense now or is there still danger of loosing the relationship?

Paul Over a year ago

Can't tell you about the actual design w/o taking a look. But regarding code, if you follow my second approach, each item is an array of its attributes. Hence, all attributes of each item are kind of in one place. This is way better than to have arrays for each attribute and to make sure all attribute arrays are processed in a synchronous way.

user399666 · Accepted Answer · 2012-08-31 14:10:24Z

0

$sth = $dbh->prepare("SELECT itemName, itemImage FROM myTable");
$sth->execute();
$result = $sth->fetchAll();

$myArr = array();
foreach($result as $row){
    foreach($row as $colName => $colVal){
         $myArr[$colName][] => $colVal;
    }
}
echo '<pre>';
print_r($myArr);
echo '</pre>';

Although I do have misgivings about how you're actually approaching this from a design perspective.

answered Aug 31, 2012 at 14:10

user399666

20k7 gold badges49 silver badges68 bronze badges

1 Comment

weewa Over a year ago

could you elaborate on what you think is wrong with my design?

Collectives™ on Stack Overflow

Split query into multiple arrays based on column name

2 Answers 2

4 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related