Snippets Databases
Databases
4 Column Spreadsheet
by: Neil Moomey
|
December 26, 2002
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>
PHPBuilder
Policies
PHP Developer
Topics
PHP Developer
Resources
PHP Developer
References
