Version: 2.0

Type: Full Script

Category: Databases

License: GNU General Public License

Description: This script simulates a spreadsheet using any MySQL table with 4 columns (fields). The first column must be an auto_increment integer named "id" but the rest you can rename using the config variables.



<?PHP
/*******************************************************************************
This script simulates a spreadsheet using any MySQL table with 4 columns (fields).   
The first column must be an auto_increment integer named "id" but the rest you 
can rename using the config variables below. Created by Neil Moomey 
neilmoomey@gci.net.  Feel free to use it as you wish.  I only ask you give me credit.

Here is an example of how to set up a table and field names.  
SQL query to create the table phone_book:

create table phone_book( 
id integer not null auto_increment, 
first_name varchar(50), 
last_name varchar(50), 
phone varchar(50),
primary key (id) 
) 
 
Now change these variables to fit the table you just created.  For example:
$table = "phone_book";
$field1 = "first_name";
$field1_label = "First Name";

$field2 = "last_name";
$field2_label = "Last Name";

$field3 = "phone";
$field3_label = "Phone";
*******************************************************************************/

// Change these variables to fit your needs:
$table = "";
$field1 = "";
$field1_label = "";

$field2 = "";
$field2_label = "";

$field3 = "";
$field3_label = "";

$db_host="localhost";
$db_user="";
$db_pass="";
$db="";
// End of variables definitions.  No need to edit code beyond this line

// Connect to database
mysql_connect($db_host,$db_user,$db_pass);
mysql_select_db($db);
?>
<html>
<head>
<script language="JavaScript">
function focusform()
{
    document.forms[1].field1_value.focus();
}
</script>
</head>
<body OnLoad="focusform()">

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

<?PHP

  if ($insert) { 
    mysql_query( "insert into $table ($field1, $field2, $field3) values (\"$field1_value\",\"$field2_value\",\"$field3_value\")"); 
  } 
  if ($update) { 
    mysql_query( "update $table set $field1=\"$field1_value\", $field2=\"$field2_value\",$field3=\"$field3_value\" where id=$update"); 
  } 
  if ($delete) { 
    mysql_query( "delete from $table where id=$delete"); 
  }  
  
  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; 
  } 
  
  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%'"; 
  }   
  $result = mysql_query($query); 
  $rows = mysql_num_rows($result); 
  echo  "<table border=1 cellspacing=0>\n"; 
  echo  "<tr>\n";
  echo  "<td><a href=\"$PHP_SELF?sort=id\">ID</a></td>\n"; 
  echo  "<td><a href=\"$PHP_SELF?sort=$field1\">$field1_label</a></td>\n"; 
  echo  "<td><a href=\"$PHP_SELF?sort=$field2\">$field2_label</a></td>\n";
  echo  "<td><a href=\"$PHP_SELF?sort=$field3\">$field3_label</a></td>\n"; 
  echo  "<td 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>New</td>\n"; 
  echo  "<input type=hidden name=insert value=1></td>\n"; 
  echo  "<td><input type=text size=10 name=field1_value value=$today></td>\n"; 
  echo  "<td><input type=text size=10 name=field2_value></td>\n";
  echo  "<td><input type=text size=15 name=field3_value></td>\n"; 
  echo  "<td colspan=2 align=center>";
  echo  "<input type=submit value=\"   Add    \"></td>\n";
  echo  "<td></td>\n"; 
  echo  "</tr>\n";
  echo  "</form>\n";
  }

  if (!$read) { 
	  while ($row = mysql_fetch_row($result)) { 
	  echo  "<tr>\n"; 
      echo  "<td>$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><a href=\"$PHP_SELF?read=$row[0]\">Edit</a></td>\n";
	  echo  "<td><a href=\"$PHP_SELF?delete=$row[0]\">Delete</a></td>\n";
	  echo  "</tr>\n";
      }
  }

  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=\"  Do it!  \"></td>\n"; 
	  echo  "</tr>\n";
      echo  "</form>\n"; 	  
    }
  mysql_close(); 
?>
</table>
</body>
</html>