Transform rows from a SQL result into columns in an array

Question

I would like to take the result from an SQL that looks like this

id    name    kg     date
1     ABC     3      2017-09-14
1     ABC     2      2017-09-15
2     DEF     2      2017-09-14
2     DEF     5      2017-09-15
2     DEF     5      2017-09-16
2     DEF     3      2017-09-17
3     GHI     3      2017-09-15
3     GHI     6      2017-09-17

And put it into an array (or something else that I can loop through) so it looks like this

id    name    2017-09-10    2017-09-11    2017-09-12    2017-09-13    2017-09-14    2017-09-15    2017-09-16    2017-09-17
1     ABC     3 kg          2 kg          
2     DEF     2 kg          5 kg          5 kg          3 kg
3     GHI                   3 kg                        6 kg

It will only hold dates from last sunday until this sunday (Active week).

I haven't worked with arrays before, but of what I have read so far I think I have to create an array inside another array to achive this? I am thinking that from the SQL result I will go through each row and push the values into right row depending if the id already exists. One id could only have unique dates.

while ($row = odbc_fetch_row($resultSQL)){
    Create an array with the columns above and push the result from the SQl
    into the array on a new row or an existing column if the ID is
    already in the array.
}

Anyone have any ideas how to achieve this? Thanks.

EDIT:

Solution: I have now done like this which achives the result I wanted.

//create two arrays
$items=array();
$dates=array();

//loop through the SQL result and put it into the arrays
while ($row = odbc_fetch_row($resultSQL)){
$ITEMNO=odbc_result($resultSQL,"id");
$ITEMNAME=odbc_result($resultSQL,"name");
$RecQtyDay=odbc_result($resultSQL,"kg");

$items[$ITEMNO] = $ITEMNAME;
$dates[$REGDATE][$ITEMNO] = $RecQtyDay;
}

//sort the dates array
ksort($dates);

//loop through and print out dates array within the item array
foreach ($items as $ITEMNO => $ITEMNAME) {
echo "$ITEMNO $ITEMNAME ";
foreach ($dates as $REGDATE => $values) {
    if (isset($values[$ITEMNO])) echo "$REGDATE $values[$ITEMNO]";
}
echo"<BR>";
}

Don't Panic · Accepted Answer · 2017-09-14 14:10:32Z

2

As you fetch your query results, build two arrays, one with all the items, and another that's grouped by date with values for any items that have records for each date.

while ($row = odbc_fetch_row($resultSQL)) {
    $items[$row['id']] = $row['name'];
    $dates[$row['date']][$row['id']] = $row['kg'];
}

Sort the dates/values array by key (date)

ksort($dates);

Then loop over your array of items to generate the rows. Within each row, loop over the array of dates and output the value for that id and date.

foreach ($items as $id => $name) {
    echo "$id   $name";
    foreach ($dates as $date => $values) {
        if (isset($values[$id])) echo "$value kg";
    }
}

This output won't be neatly formatted, but I don't know exactly how you're planning to output this, so I'll leave that up to you.

answered Sep 14, 2017 at 14:10

Don't Panic

41.9k11 gold badges70 silver badges87 bronze badges

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

4 Comments

Per Over a year ago

Thanks! Now I am close but I have some issues with the $kg. I will output the same $kg for every date.

Per Over a year ago

I have updated my original post with the complete code and the output I got. Are you able to see where I have failed?

Per Over a year ago

I realized I wasn't handling the kg from the array just took the static value. So now it is working. Thanks a lot!

Don't Panic Over a year ago

You're welcome! Sorry I didn't reply earlier, I was asleep. :-)

Collectives™ on Stack Overflow

Transform rows from a SQL result into columns in an array

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related