CODEWITHSUNDEEP
phparraystransformrows
Per
Per

Reputation: 149

Transform rows from a SQL result into columns in an array

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>";
}

Upvotes: 0

Views: 105

Answers (1)

Don&#39;t Panic
Don&#39;t Panic

Reputation: 41820

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.

Upvotes: 2

Related Questions