PHP: Fill fixed size chart with mysql data

Question

I have a table chart. Lets say 5 by 5. I run a loop such as

<table>
    <tbody>
     <?php for ($i = 0; $i < 5; $i += 5) {
           echo "<tr>
              <td>one box</td>
              <td>one two</td>
              <td>one three</td>
              <td>one four</td>
           <tr>";
      }?>
     </tbody>
</table>

It creates a table such as

 |      |      |      |      |       |
 -------------------------------------
 |      |      |      |      |       |
 -------------------------------------
 |      |      |      |      |       |
 -------------------------------------
 |      |      |      |      |       |
 -------------------------------------
 |      |      |      |      |       |
 -------------------------------------

Now I have mysql data I load for my purposes and I need it to put the data in respectively so the table looks like

 |      |      |      |      |       |
 -------------------------------------
 |      |      |      |      |       |
 -------------------------------------
 |      | Res 1|      |      |       |
 -------------------------------------
 |      |      |      | Res 3|       |
 -------------------------------------
 |      |Res 4 |      |      |  Res 2|
 -------------------------------------

How would I do this? I have 50 results and need to fill the results into the correct column and row. I need to do some sort of if(results[0-50]['id'] == rowcolumnid) echo the results for the correct table while doing the for loop.

Edit: Here is my full code.

    <table id="schedule">
        <tbody>
            <?php
            $time = mktime(0, 0, 0, 1, 1);
            for ($i = 28800; $i < 62200; $i += 1800) { ?>
             <tr id="row<?php echo $i; ?>">
                <td id="hour">
                    <?php 
                    printf('%1$s',date('g:i a', $time + $i)); 
                    ?>
                </td>
                                    <td id="sunday"></td>
                                    <td id="monday"></td>
                                    <td id="tuesday"></td>
                                    <td id="thursday"></td>
                                    <td id="friday"></td>
                                    <td id="saturday"></td>
            </tr>
            <?php } ?>
        </tbody>
    </table>

My mysql results are in datetime format that I'm going to use to propogate the table.

Results:

ID| EVENT NAME | DATEOFEVENT
1 | event name | 2012-11-20 12:00:00
2 | event name | 2012-11-21 13:30:00
3 | event name | 2012-11-22 13:00:00
4 | event name | 2012-11-23 11:00:00
5 | event name | 2012-11-24 08:00:00

etc.

I can do a strtotime of the dates and a date command to match.

What is the relation between Res 1 to Res 4 and the Table Cell 1 to Table Cell 25? — hakre
– hakre, Commented Dec 29, 2012 at 22:34
The only thing I've tried is running a while($row = mysql_fetch_array($result == "columnid")){ If match echo } for each column running 25 queries and looping through each one. :( Which is ridiculous. — Darius
– Darius, Commented Dec 29, 2012 at 22:34
To further explain my programming purpose. I'm creating a schedule planner. I do a for loop for hours 6AM to 6PM, and I fetch the events for that week. Then I match times according to day and time. — Darius
– Darius, Commented Dec 29, 2012 at 22:35

hakre · Accepted Answer · 2012-12-29 22:44:27Z

1

If you first fetch the data (or if it is sorted if you first fetch the first data), then you can just iterate over the data when you match the hour/time that you iterate over to draw the table.

As an example, I've chosen to display only one column that represents 5 hours (1-5) of which some can be matched. Those that are matched, are stored in an array and made available as an iterator (ArrayIterator):

$data = [3,5];
$datas = new ArrayIterator($data);
$datas->rewind();

Matched hours are represented with 1, unmatched ones with 0:

echo "+---+---+\n";

foreach(range(1, 5) as $hour)
{
    if ($hasData = ($datas->valid() and $datas->current() === $hour)) {
        $datas->next();
    }
    $hasData = (int) $hasData;


    echo "| $hour | $hasData |\n";
    echo "+---+---+\n";
};

Output:

+---+---+
| 1 | 0 |
+---+---+
| 2 | 0 |
+---+---+
| 3 | 1 |
+---+---+
| 4 | 0 |
+---+---+
| 5 | 1 |
+---+---+

This works perfectly if the data from the data is available as an iterator (often the case, for mysql_* you need to write you one) and if it is sorted.

Even this is only a single list here, for a table this works actually equally because a table is just a different form of representing the data.

answered Dec 29, 2012 at 22:44

hakre

200k55 gold badges454 silver badges865 bronze badges

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

2 Comments

Darius Over a year ago

Thank you for this, I'm going to try and understand this. Will post questions if it comes up while trying this method. Glad you understood my question, I'm not the best when it comes to asking in correct full form.

hakre Over a year ago

Your problem is that you first do the output and then you need to make your data "fitting". But actually you have the data first. But it's not so easy because it somehow requires to do "two things at once" which always needs some brain power. Start with pencil and paper.

Collectives™ on Stack Overflow

PHP: Fill fixed size chart with mysql data

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related