Display both empty & complete fields in results page

[thedc]

First, thank you for your hard work.

I'm attempting to recreate a classroom maintenance ticket system using the latest Form Tools. Here's the situation:

We have 20 rooms that need to be checked for problems/cleanliness every day. Student workers will fill out a simple form that allows them to enter their name, select a room (dropdown), enter notes, and then submit. All submissions can be edited be all.

I need to display daily form results on a page that will show all rooms in a column. The student's name, timestamp, notes, IP address, etc will be in columns next to the rooms. The page needs to show the room (row) even if no data has been submitted yet.

Example Form:


Example Results Page:


If I did a search for any given day, I should see the entire room list even if no entries have been made. In the screenshot above, the last four rooms have no submissions.

Is there a way to accomplish this kind of results page with Form Tools? Any help would be greatly appreciated.

[Ben]

Neat problem!

Generating the table with the data isn't tricky at all, but including rows for rooms that don't have any problems reported makes it a little more difficult.

Hmm... I think you'll probably need to write a custom PHP page for this. It's a drag, but I can't see a way around it. In broad terms, I'd do this: in your PHP create a single array storing all 20 room numbers. Then, loop through each, querying the database for any info for that room number. For each item, if there's no data, just output an empty row.

Sorry this is so very general, but If you need some more help, let me know! :-)

- Ben

[thedc]

Thanks for your replay. Any help with sample php code would be greatly appreciated. I'm pretty good at figuring things out if I'm given a good start.

[Ben]

Sure! Sorry for the wait. I'm just at a conference today, but I'll take a look at this when I get home tonight.

[Ben]

Ack! Sorry for the wait. Try this (see below for explanation of the code, and what you'll need to do to tweak it for your case):

PHP Code:

<?php
require_once("/path/to/global/api/api.php");
$rooms = array("BIO 301", "GEA 403", "GEA 247", "GEA 337B", "GEA 100");
$form_id = 1;
?>

<table>
<?php
// loop through each of the rooms and query the database for problems reported
// for each room
foreach ($rooms as $room)
{
  $query = mysql_query("
    SELECT * 
    FROM   {$g_table_prefix}form_{$form_id}
    WHERE  room = '$room'
      ");
  
  while ($row = mysql_fetch_assoc($query))
  {
    echo "<tr>
            <td>{$room}</td>
            <td>{$row["checker"]}</td>
            <td>{$row["submission_date"]}</td>
            <td>{$row["ip_address"]}</td>
            <td>{$row["technical_issue"]}</td>                                    
            <td>{$row["status"]}</td>
          </tr>";
  }
  
  // if there weren't any problems reported for this room, output a blank row
  if (mysql_num_rows($query) == 0)
  {
    echo "<tr>
            <td>{$room}</td>
            <td> </td>
            <td> </td>
            <td> </td>
            <td> </td>                                    
            <td> </td>
          </tr>";
  }
}
?>
</table> 


The code is hopefully pretty self-documenting. But let me explain each thing:

1. First off, edit the path to the Form Tools API. You can either enter a relative or an absolute path to the file - but NOT a URL!
   
   
2. It start off by creating an array of all possible rooms in the database (I just put 5, but you can pad it out to include all the rooms you have in the dropdown). Note that the display value and the actual value of the dropdown field should be the same (e.g. <option value="GEA 100">GEA 100</option>). If not, it's no problem but you'll need to change the values in the $rooms array to store the VALUES of the dropdown, not the DISPLAY values. The values are what's stored in the database, hence that's what you'll be running your query against.
   
   
3. Next, you need to set the $form_id to the ID of your own form.
   
   
4. This next bit may be a little confusing, but hopefully not too bad. All the SQL queries depend on the actual database column names of your fields. So for example this bit...
   
   
   PHP Code:

   SELECT * 
    FROM   {$g_table_prefix}form_{$form_id}
    WHERE  room = '$room' 

   
   ...assumes the field that stores the room is called "room". To find out what your fields are called, log into Form Tools, edit the form and click on the database tab. That page lists the database column names for each field. It never hurts to rename them from the default col1_, col_2, ... etc. to something more descriptive. The "Smart Fill" button at the top of the column does this for you.
   
   Once you've settled on an appropriate database column name, edit that SQL query to change the WHERE clause. e.g.
   
   
   PHP Code:

   WHERE myroomfield = '$room' 

   
   
5. Lastly, you'll need to do the same for the various fields outputted in the HTML table row generation part:
   
   
   PHP Code:

   echo "<tr>
            <td>{$room}</td>
            <td>{$row["checker"]}</td>
            <td>{$row["submission_date"]}</td>
            <td>{$row["ip_address"]}</td>
            <td>{$row["technical_issue"]}</td>                                    
            <td>{$row["status"]}</td>
          </tr>"; 

   
   The 1st, 3rd and 4th columns will probably be left alone, but you'll need to tweak the others.
   

After all this, you can set about styling it however you wish or just embed the whole lot into an existing PHP page. Obviously you'll want things like <html><body> tags etc.

Hope this helps...! (and I hope it's fairly clear).

- Ben

[thedc]

Ben,

You are awesome! It worked perfectly. Thank you so much for your help. I am very impressed with the service and usefulness of this forum.

I have one last question... for now:-)

What code would I need to add to only display the entries from the current day? For example, if I submitted info for each room on a Monday, on Tuesday, all cells in the display page will be clear. Basically, I just want the page to show entries from the current day.

Thanks again.

[Ben]

Heya,

Excellent! Glad it's working out so far.

To only display entries on the current day, you'll need to doctor the SQL to limit the results returned. Try this.

In your PHP, add these lines. They compute the current day in MySQL-friendly format and the FOLLOWING day.

PHP Code:

list($year, $month, $day) = split(",", date("Y,m,d"));
$start_date = "$year-{$month}-{$day}";
$end_date = date("Y-m-d", mktime(0, 0, 0, $month, $day+1, $year)); 


In the example I listed above, try adding these lines before this line:

PHP Code:

foreach ($rooms as $room) 


They only need to be called once, so they needn't be in the loop.

Lastly, edit the SQL query to include the date range. So change it to this:

PHP Code:

$query = mysql_query("
    SELECT * 
    FROM   {$g_table_prefix}form_{$form_id}
    WHERE  room = '$room' AND
              submission_date > '$start_date' AND 
              submission_date < '$end_date'
      "); 


And I think that's it! Unless I messed something up, that should return everything on the current day - regardless of the time the person visits the page.

Hope this helps!

- Ben