PHP query looping

Question

I am having trouble finding the right way to loop over values when creating a table. I have a table with a list of dates, ids and other data. I need to loop over the table that looks like:

rest_id    date          galls
133        2011-06-01    75 
139        2011-06-12    60 
133        2011-06-13    85 
139        2011-06-22    25 
133        2011-06-30    80

And end up with:

  id     week 1    week 2   week 3   week 4   week 5
  133    75        85       wk3      4        80
  139    wk1       60       wk3      25       wk5

I have tried many variations but can't get the loop right. I have been playing with this type of loop for weeks and can't get it.

I have a routine that gets the week of the month:

$weekNum = intval((intval($row_rests['day'])+6)/7);

do {  ?>
  <td><?php if($weekNum == 1 AND !in_array('1',$weeklist)) {echo $row_rests['galls']; array_push($weeklist, '1'); continue;} else echo '1'; array_push($weeklist, '1'); ?></td>
  <td><?php if($weekNum == 2 AND !in_array('2',$weeklist)) {echo $row_rests['galls']; array_push($weeklist, '2'); continue;} else echo '2'; array_push($weeklist, '2'); ?></td>
  <td><?php if($weekNum == 3 AND !in_array('3',$weeklist)) {echo $row_rests['galls']; array_push($weeklist, '3'); continue;} else echo '3'; array_push($weeklist, '3'); ?></td>
  <td><?php if($weekNum == 4 AND !in_array('4',$weeklist)) {echo $row_rests['galls']; array_push($weeklist, '4'); continue;} else echo '4'; array_push($weeklist, '4'); ?></td>
  <td><?php if($weekNum == 5 AND !in_array('5',$weeklist)) {echo $row_rests['galls']; array_push($weeklist, '5'); continue;} else echo '5'; array_push($weeklist, '5'); ?></td>
<?php
} while ($row_rests = mysql_fetch_assoc($rests));
$weeklist = array();
?>

I have been trying to populate an array as I loop. If the week number is already in the array, than skip that entry. Trouble is, sometimes there will be 5 entries, sometimes 0 or anywhere in between.

I need to get SOME text in each cell but I haven't found the right combo of loops (I should always loop 5 times I think) and array writing. I have tried all combos of continue; and break; and resetting the array in different places.

The above code is within a loop that goes through the rest_ids, and that works fine. It's just this sub-loop that is stumping me.

Ideas?

Don, you're trying way to hard with the markup in your answer. Format questions/answers typically as plaintext, with two returns meaning a paragraph break, and see the preview below the edit box. — Jared Farrish
– Jared Farrish, Commented Sep 5, 2011 at 18:05
I just edited the answer to remove all that - as soon as it's approved, you'll see the updated version. — jimbo
– jimbo, Commented Sep 5, 2011 at 18:09

arijeet · Accepted Answer · 2011-09-05 18:06:25Z

1

U realise, u dont need to use PHP/OOP loops to accomplish that..

Looking at your table and the result that you want to get, all you need to do is to group your query result using the GROUPBY clause in SQL. In that very query, you can rename the attribute/column name and put conditions on it.

answered Sep 5, 2011 at 18:06

arijeet

1,88418 silver badges28 bronze badges

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

Comments

Wulf · Accepted Answer · 2011-09-05 18:12:30Z

function getWeekOfMonth($date) {
    return intval((intval(substr($date,8))+6)/7); //Took just your function
}


$rows = array();
$sql = "SELECT * FROM table";
$result = mysql_query($sql);
while ($row = mysql_fetch_array($result, MYSQL_ASSOC)) {
    $found = false;
    foreach ($rows as &$savedRow) {
        if ($savedRow['id'] == $row['id']) {
            $found = true;
            $savedRow['wk'.getWeekOfMonth($row['date'])] = $row['galls'];
            break;
        }
    }
    if (!$found) {
        $tempRow = array(
            'id' => $row['id'],
            'wk1' => 'wk1',
            'wk2' => 'wk2',
            'wk3' => 'wk3',
            'wk4' => 'wk4',
            'wk5' => 'wk5'
        );
        $tempRow['wk'.getWeekOfMonth($row['date'])] = $row['galls'];

        $rows[] = $tempRow;
    }
}

As you see, it first looks, if the row for the id already exists. If not, it initializes the row with its 5 standard values and then sets the current row to its value. If it already exists, it just sets the right wk to the correct value.

Don · Accepted Answer · 2011-09-08 19:24:18Z

Thanks for the replies! This was my first post here so I struggled with the formatting a bit. I solved it soon after I posted...of course.

<?php $i = 0;
        $y = 0;
     while ($i < 5): 
          $weekNum = intval((intval($row_rests['day'])+6)/7);
    ?>
      <td align="center"><php if(intval($weekNum) == intval($i+1)) {echo $row_rests['galls'];} else echo "-"; ?></td>
      <?php  
         $i++;
         if ($weekNum == $i)
           $y++;
           if($totalRows_rests = 0){
               $y = 0;
               }
               else
               { $y = $y;}
           mysql_data_seek ($rests, $y);
           $row_rests = mysql_fetch_assoc($rests);
    endwhile; ?>

Thanks for the help!

Collectives™ on Stack Overflow

PHP query looping

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related