Spreadsheet-Like HTML Table View

PHP Best Practices

How to make your data look like a spreadsheet in a web browser


Date : 2006-04-26
A couple of weeks ago I needed to show the management at my office a bunch of data that was in an SQL table but I needed to make sure the data was live, and I needed to break it down into 'just the facts' without all the extraneous data id's that the table used. So instead of exporting the information to csv and importing it to excel I did some poking around with PHP and came up with a way to view the data through a web browser in a fairly aesthetically pleasing way.

Heres what I did:

First of course you want to define what database you need to work with and what fields you are interested in displaying:

Code:
<?

// Define the table and access vars

$table = "4sale";
$db_host="localhost";
$db_user="webuser";
$db_pass="";
$db="inns4sale";

// Define the fields to view

$field1 = "name";
$field1_label = "Name";

$field2 = "address";
$field2_label = "Address";

$field3 = "city";
$field3_label = "City";

$field4 = "state";
$field4_label = "State";

$field5 = "zip";
$field5_label = "Zip";

// Connect to database

mysql_connect($db_host,$db_user,$db_pass);
mysql_select_db($db);

?>


So, once we're connected we move on to building the page:

Code:
<html>
<head>
<script language="JavaScript">
function focusform()
{
    document.forms[1].field1_value.focus();
}
</script>
</head>
<body OnLoad="focusform()">


<!-- build a search box -->

<center>
<form action="<?echo$PHP_SELF;?>" method="post">
Keyword <input type="text" name="keyword">
<input type="submit" value="Search">
</form>
</center>


Note here the inclusion of the search form - this is going to let you search the entire dataset once it gets pulled from the database.

So on with the show:

Code:
<?PHP

  // for adding items LIVE

  if ($insert) {
    mysql_query( "insert into $table ($field1, $field2, $field3) values ("$field1_value","$field2_value","$field3_value","$field4_value","$field5_value")");
  }

  // for updating the data LIVE

  if ($update) {
    mysql_query( "update $table set $field1="$field1_value", $field2="$field2_value",$field3="$field3_value",$field4="$field4_value",$field5="$field5_value" where id=$update");
  }

  // DANGER! This will actually delete items right from the table!

  if ($delete) {
    mysql_query( "delete from $table where id=$delete");
  } 

  // check for errors and pull the column names from the table

  if (mysql_errno()!=0) {
    switch (mysql_errno()) {
      default:
        echo  "Error #".mysql_errno(). " (".mysql_error(). ")<br>";
    }
  }
  if (!$sort) $sort="$field1";
  $query =  "select * from $table";
  switch ($sort) {
    case  "id": $query=$query. " order by id"; break;
    case  "$field1": $query=$query. " order by $field1"; break;
    case  "$field2": $query=$query. " order by $field2"; break;
    case  "$field3": $query=$query. " order by $field3"; break;
    case  "$field4": $query=$query. " order by $field4"; break;
    case  "$field5": $query=$query. " order by $field5"; break;
  }
 
  // get ALL the records with an ID - if you dont use id then this has to be some other unique value

  if ($read) {
    $query = "select * from $table where id=$read";
  }
 
  if ($keyword) {
    $query = "select * from $table where $field1 LIKE '%$keyword%' OR $field2 LIKE '%$keyword%' OR $field3 LIKE '%$keyword%' OR $field4 LIKE '%$keyword%' OR $field5 LIKE '%$keyword%'";
  }   
  $result = mysql_query($query);
  $rows = mysql_num_rows($result);


At this point we've pulled the data and populated an array that we can traverse to get the actual records from. Also, you probably noticed several if statements regarding inserts, updates and deletes. These are going to be used on the front end to work with the data directly as if we were using a real spreadsheet.

The switch section above builds the list of field headers so that you know what data you are viewing.

Next we start to build in the html for the tables that will show the actual data. Note that this is a nested table just to make things look nice but could easily be modified with css or other methods to suit your own style.

Code:
// build the web table

  echo  "<table width='100%' bgcolor=#BFAC60 border=0 cellspacing=5>n";
  echo  "<tr><td>";
  echo  "<table width='100%' bgcolor=#FFFAE6 border=0 cellspacing=0>n";
  echo  "<tr>n";

  // fill in the row heading values

  echo  "<td bgcolor=#FFE680><a href="$PHP_SELF?sort=id">ID</a></td>n";
  echo  "<td bgcolor=#FFE680><a href="$PHP_SELF?sort=$field1">$field1_label</a></td>n";
  echo  "<td bgcolor=#FFE680><a href="$PHP_SELF?sort=$field2">$field2_label</a></td>n";
  echo  "<td bgcolor=#FFE680><a href="$PHP_SELF?sort=$field3">$field3_label</a></td>n";
  echo  "<td bgcolor=#FFE680><a href="$PHP_SELF?sort=$field4">$field4_label</a></td>n";
  echo  "<td bgcolor=#FFE680><a href="$PHP_SELF?sort=$field5">$field5_label</a></td>n";
  echo  "<td  bgcolor=#FFE680 colspan=2>";
  if ($keyword) echo "<a href="$PHP_SELF?sort=$field1">View All</a>";
  echo  "</td>n";
  echo  "</tr>n";
 
  if (!$read)
  {
    echo  "<form action="$PHP_SELF" method="post">n";
    echo  "<tr>n";
    echo  "<td bgcolor=#FFE680><b> New: </b></td>n";
    echo  "<input type=hidden name=insert value=1></td>n";
    echo  "<td bgcolor=#FFE680><input type=text size=10 name=field1_value value=$today></td>n";
    echo  "<td bgcolor=#FFE680><input type=text size=10 name=field2_value></td>n";
    echo  "<td bgcolor=#FFE680><input type=text size=15 name=field3_value></td>n";
    echo  "<td bgcolor=#FFE680><input type=text size=15 name=field4_value></td>n";
    echo  "<td bgcolor=#FFE680><input type=text size=15 name=field5_value></td>n";
    echo  "<td bgcolor=#FFE680 colspan=2 align=center>";
    echo  "<input type=submit value="   Add    "></td>n";
    echo  "<td></td>n";
    echo  "</tr>n";
    echo "<tr><td bgcolor=#FFE680 colspan=8></td></tr>";
    echo  "</form>n";
  }


This section basically takes the data that the case statement pulled in order to populate the headings as well as taking care of some basic user input via the web form.

Finally we get to display the actual data from our previous query:

Code:
if (!$read)
  {
    // set $colored to 0 initiailly
    $colored = 0;
    while ($row = mysql_fetch_row($result))
    {
      if ($colored == 0)
      {

        // write the row

        echo  "<tr>n";
        echo  "<td bgcolor=#FFE680>$row[0]</a></td>n";
  echo  "<td>$row[1]</td>n";
        echo  "<td>$row[2]</td>n";
        echo  "<td>$row[3]</td>n";
        echo  "<td>$row[4]</td>n";
        echo  "<td>$row[5]</td>n";
        echo  "<td width=5 bgcolor=#FFFAE6><a href="$PHP_SELF?read=$row[0]">Edit</a></td>n";
  echo  "<td width=5 bgcolor=#FFE680><a href="$PHP_SELF?delete=$row[0]">Delete</a></td>n";
  echo  "</tr>n";
       
        // set $colored to 1 for the next pass 
        $colored = 1;
      }
      else
      {
        echo  "<tr>n";
        echo  "<td bgcolor=#FFE680>$row[0]</a></td>n";
  echo  "<td bgcolor=white>$row[1]</td>n";
        echo  "<td bgcolor=white>$row[2]</td>n";
        echo  "<td bgcolor=white>$row[3]</td>n";
        echo  "<td bgcolor=white>$row[4]</td>n";
        echo  "<td bgcolor=white>$row[5]</td>n";
        echo  "<td width=5 bgcolor=#FFFAE6><a href="$PHP_SELF?read=$row[0]">Edit</a></td>n";
  echo  "<td width=5 bgcolor=#FFE680><a href="$PHP_SELF?delete=$row[0]">Delete</a></td>n";
  echo  "</tr>n";
        $colored = 0;
      }
    }
  }

  // form to allow editing of live items

  if ($read)
  {
    $row = mysql_fetch_row($result);
    echo  "<form action="$PHP_SELF" method="post">n";
    echo  "<input type=hidden name=update value="$row[0]">n";
    echo  "<tr>n";
    echo  "<td>$row[0]</a></td>n";
    echo  "<td><input type=text size=10 name=field1_value  value=$row[1]></td>n";
    echo  "<td><input type=text size=10 name=field2_value  value=$row[2]></td>n";
    echo  "<td><input type=text size=15 name=field3_value value=$row[3]></td>n";
    echo  "<td colspan=2 align=center><input type=submit value="  Apply  "></td>n";
    echo  "</tr>n";
    echo  "</form>n";     
  }
  mysql_close();
?>
</table>
</table>
</body>
</html>


You'll notice the $colored var and the fact that the table data appears to be written depending whether the var is either 0 or 1, this is simply a way to color every other row so that the information is easier to see across the entire screen since when we defined our tables we set them to have no borders.

The form at the bottom of the code is used to edit a single entry. And then there is the closing tags for the tables.

Thats it! This code can be modified very easily to handle tons of data but be aware that your web browser may warn you that the script has 'stopped responding' if you try to pull a 1000 rows or more.

All in all though, this is a great substitution for an actual spreadsheet if for some reason you either dont want to show everything or you need to be able to show live data to a customer or manager.

Til next time, Happy coding!

Comments :

No comments yet
  • Search For Articles