SUSE Conversations


Web Access For Any MySQL Table



By: lxzndr

December 28, 2007 10:59 am

Reads:167

Comments:0

Rating:0

Use MySQL, SLES 10, Apache, PHP to quickly create web pages for display, entry, and editing of any MySQL database without needing to know the DB field names or layout.

After migrating a MS Access database some of our users use into MySQL and creating a web form for display, entry, and editing of the data, it became apparent that it would be much easier if I could use a sort of template for migrating other simple databases into web page forms for users.

While this is a set of 8 php documents, you could combine them into fewer pages using various php options. Also, you could hard code various values into the later pages for use with a particular table. The pages make frequent use of PHP session variables, and HTML form values for passing information between the various pages.

Note: comment lines should be a single line. Some lines have been wrapped within the document, all PHP statements (not functions such as IF, UNTIL, etc) end with “;” which should allow you to reform the lines properly. Unlike HTML, PHP is affected by line breaks.

If a line was split, you will usually end up with a blank page, even with debugging turned on, if that happens, check that there wasn’t an extra line break on any of the lines (turn wrapping OFF in your editor to see those easier)

The first page of the set requests the MySQL database and table connection information.

File: generic_post_form.php

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01//EN"
"http://www.w3.org/TR/html4/strict.dtd">
<html>
<body>

<?php
session_start();
$_SESSION["debug"]=false;
// global debug option allows double checking of values and passed along variables.
$_SESSION["returning"]=false;
// global ‘returning’ from an edit page to keep the selected query active.

// create a form requesting the MySQL database connection details
// if the web page resides on a different server than the web page, you may need to specify something other
// than ‘localhost’ for the server

// warning: in debug mode, password is viewable on the web pages!!
?>

<form method=”post” action=”./generic_post_action.php”>
Server: &nbsp <input type=”Text” name=”p_server” value=”localhost”><br>

User ID: &nbsp <input type=”Text” name=”p_user”><br>
Password:<input type=”password” name=”p_pass”><br>

Database:<input type=”Text” name=”p_db”><br>
Table:<input type=”Text” name=”p_table”><br>

<input type=”Submit” name=”DISPLAY” value=”DISPLAY”>
<input type=”Submit” name=”ADD” value=”ADD”>

<input type=”Submit” name=”QUERY” value=”QUERY”>
</form>

</body>

</html>

This page then ‘posts’ the values to the next page determines which action to take depending on the submit button pressed.

File: generic_post_action.php

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01//EN"
"http://www.w3.org/TR/html4/strict.dtd">
<html>
<body>

<?php
session_start();

// allow for debugging this specific page.
$local_debug = false;

// this form will auto submit if we are not in debug mode.
if (! $_SESSION["debug"] && ! $local_debug) {
echo “<body onload=’document.f_p_action.submit();’>”;
}

if ($_SESSION["debug"] || $local_debug) { Print_r ($_POST); echo “<br>\n”; }

// check which mode button was pressed… default to display mode.
// $f_text is the variable used to build the HTML FORM text
if (isset($_POST["ADD"])) {
if ($_SESSION["debug"] || $local_debug) { echo “Add <br>\n”; }
$f_text = “<form name=’f_p_action’ method=’post’ action=’./generic_add.php’><br>\n”;
} else {
if (isset($_POST["QUERY"])) {
if ($_SESSION["debug"] || $local_debug) { echo “Query <br>\n”; }
$f_text = “<form name=’f_p_action’ method=’post’ action=’./generic_query.php’><br>\n”;
} else {
if ($_SESSION["debug"] || $local_debug) { echo “Other – Display <br>\n”; }
$f_text = “<form name=’f_p_action’ method=’post’ action=’./generic_display_table.php’><br>\n”;
}
}

$f_text .= ‘<input type=”Text” name=”posted_server” value=”‘ . $_POST["p_server"] . ‘”>’ . “<br>\n”;
$f_text .= ‘<input type=”Text” name=”posted_user” value=”‘ . $_POST["p_user"] . ‘”>’ . “<br>\n”;
$f_text .= ‘<input type=”password” name=”posted_pass” value=”‘ . $_POST["p_pass"] . ‘”>’ . “<br>\n”;
$f_text .= ‘<input type=”Text” name=”posted_db” value=”‘ . $_POST["p_db"] . ‘”>’ . “<br>\n”;
$f_text .= ‘<input type=”Text” name=”posted_table” value=”‘ . $_POST["p_table"] . ‘”>’ . “<br>\n”;
$f_string .= “<input type=’submit’ name=’Submit’ value=’submit’>\n”;
$f_text .= ‘</form>’;

// output the form text into the HTML page
echo $f_text;
?>

</body>
</html>

The next page in the set is our query request page.

File: generic_query.php

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01//EN"

“http://www.w3.org/TR/html4/strict.dtd”>
<html>
<body>

<?php
session_start();

// allow for local debug option
$local_debug = false;

if ($_SESSION["debug"] || $local_debug) {
echo “SID = ” . session_id() . “<BR>”;
echo “as = ” . intval(session.auto_start) . “<BR>”;
echo “s name = ” . session_name() . “<BR>”;
}

// if not returning from display page, then read the form values into the session values
if (! $_SESSION["returning"]) {
$_SESSION["g_server"] = $_POST['posted_server'];
$_SESSION["g_user"] = $_POST['posted_user'];
$_SESSION["g_pass"] = $_POST['posted_pass'];
$_SESSION["g_db"] = $_POST['posted_db'];
$_SESSION["g_table"] = $_POST['posted_table'];
$_SESSION["returning"] = false;
}

if ($_SESSION["debug"] || $local_debug) {
echo $_SESSION["g_server"] . “<BR>\n”;
echo $_SESSION["g_user"] . “<BR>\n”;
echo $_SESSION["g_pass"] . “<BR>\n”;
echo $_SESSION["g_db"] . “<BR>\n”;
echo $_SESSION["g_table"] . “<BR>\n”;
}

// make our database connection and select the table
$myconn = mysql_connect($_SESSION["g_server"], $_SESSION["g_user"], $_SESSION["g_pass"]);
@mysql_select_db($_SESSION["g_db"]) or die ( “Unable to select database”);

if ($_SESSION["debug"] || $local_debug) { echo “db selected <BR>\n”; }

// read in the table column names
$result = mysql_query(“SHOW COLUMNS FROM ” . $_SESSION['g_table'],$myconn);

// display any errors
if (mysql_errno($myconn)) {
$message .= mysql_errno($myconn) . “: ” . mysql_error($myconn) . “<br>\n”;
die($message);
}

if ($_SESSION["debug"] || $local_debug) { echo “query ran”; echo “<BR>\n”; }

// build the ADD input form table
$f_string = “<!–Debugging output for Form –>\n\n”;
$f_string .= “<form method=’post’ action=’generic_display_table.php’>\n”;
$f_string .= “<table>\n”;

// build the table value entry
$count = 0;
while ($row=mysql_fetch_row($result)){
$cnt = 0;
foreach ($row as $item){
if ($cnt == 0){
$f_string .= “<tr>\n”;
$f_string .= “\t<td><input type=’radio’ name=’generic_sort’ value=’” . $item . “‘></td>\n”;
$f_string .= “\t<td>” . $item . “</td>\n”;
$f_string .= “\t<td> <input type=’text’ name=’” . $item . “‘></td>\n”;
$f_string .= “</tr>\n”;
$cnt++;
$count++;
}
}
}

$f_string .= “</table>\n”;
$f_string .= “<input type=’hidden’ name=’query_column_count’ value=’” . $count . “‘>\n”;
$f_string .= “<input type=’submit’ name=’QUERY’ value=’QUERY’>\n”;
$f_string .= “</Form>\n”;
$f_string .= “<!–End debug from SQL query–>\n\n”;

echo $f_string;
echo “<BR>\n”;

$_SESSION["returning"]=true;

?>

</body>
</html>

 

The next page is the display page, this displays the entire table (or selected query data) as a HTML table,
and allows the clicking on a row to open an edit page for the selected row.

File: generic_display_table.php

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01//EN"
"http://www.w3.org/TR/html4/strict.dtd">
<html>
<body>

<?php
session_start();
$local_debug = false;

// Function for displaying the results in table format
function _mysql_result_all($result, $tableFeatures=”") {
$table .= “<!–Debugging output for SQL query–>\n\n”;
$table .= “<table $tableFeatures>\n\n”;
$noFields = mysql_num_fields($result);
$table .= “<tr>\n”;
for ($i = 0; $i < $noFields; $i++) {
$field = mysql_field_name($result, $i);
$table .= “\t<th>$field</th>\n”;
$column_names[$i]=$field;
} // end noFields loop

$table .= “</tr>\n”;
while ($r = mysql_fetch_row($result)) {
$table_row_data = “”;
$colm=0;
unset($row_data);
foreach ($r as $table_column) {
$table_row_data .= “\t<td>$table_column&nbsp</td>\n”;
$row_data[$colm]=$table_column;
$colm++;
} // end foreach column
$parms = “”;
for ($j = 0; $j < $noFields; $j++) {
if ($j != 0) { $parms .= ‘&’; }
$parms .= $column_names[$j] . “=” . str_replace(” “,”%20″,$row_data[$j]);
}
$parms .= ‘&_gdt_numfields=’ . $noFields;
$t_link = ‘”./generic_get_row.php?’;
$table .= ‘<tr border rules=all onClick=window.open(‘ . $t_link . $parms . ‘”,target=”_self”);>’ . “\n”;
$table .= $table_row_data;
$table .= “</tr>\n”;
} // end while $r

$table .= “</table>\n\n”;
$table .= “<!–End debug from SQL query–>\n\n”;
return $table;
} // end of function

if ($_SESSION["debug"] || $local_debug) {
echo “SID = ” . session_id() . “<BR>”;
Print_r ($_SESSION);
echo “<BR><BR>”;
Print_r ($_POST);
echo “<BR><BR>”;
}

// if not returning from display page, then read the form values into the session values
if (! $_SESSION["returning"]) {
$_SESSION["g_server"] = $_POST['posted_server'];
$_SESSION["g_user"] = $_POST['posted_user'];
$_SESSION["g_pass"] = $_POST['posted_pass'];
$_SESSION["g_db"] = $_POST['posted_db'];
$_SESSION["g_table"] = $_POST['posted_table'];
$_SESSION["returning"]=false;
}

if ($_SESSION["debug"] || $local_debug) { Print_r ($_SESSION); }

if ($_SESSION["debug"] || $local_debug) {
echo “server=” . $_SESSION["g_server"] . “<BR>\n”;
echo “user=” . $_SESSION["g_user"] . “<BR>\n”;
echo $_SESSION["g_pass"] . “<BR>\n”;
echo “db=” . $_SESSION["g_db"] . “<BR>\n”;
echo “table=” . $_SESSION["g_table"] . “<BR>\n”;
}

$myconn = mysql_connect($_SESSION["g_server"], $_SESSION["g_user"], $_SESSION["g_pass"]);

@mysql_select_db($_SESSION["g_db"]) or die ( “Unable to select database”);

if ($_SESSION["debug"] || $local_debug) { echo “db selected <BR>\n”; }

// fill with the previous query selections
$qry_where = $_SESSION["qry_where"];
$qry_sort= $_SESSION["qry_sort"];
$qry_sel = “SELECT * FROM ” . $_SESSION['g_table'];

// build the query if we came from the query page
if ($_POST["QUERY"]==’QUERY’) {
reset($_POST);
$q_val_cnt=0;
$cols=$_POST["query_column_count"];
for ($i = 0; $i < $cols; $i++) {
if ($_SESSION["debug"] || $local_debug) { echo $i . “<BR>\n”; }
if (key($_POST) == ‘generic_sort’) {
$qry_sort = ” ORDER BY ” . current($_POST);
if ($_SESSION["debug"] || $local_debug) { echo $qry_sort . “<BR>\n”;}
next($_POST);
}
if (current($_POST) != ”) {
if ($q_val_cnt == 0) {
$qry_where = ” WHERE “;
} else {
$qry_where .= ” and “;
} // end q_val_cnt check

$qry_where .= key($_POST) . ” LIKE ‘” . current($_POST) . “‘”;
$q_val_cnt++;
if ($_SESSION["debug"] || $local_debug) { echo “count=” . $q_val_cnt . ” query = ” . $qry_where . “<BR>\n”;}
}
next($_POST);
}
// store the query selections in case we return here

$_SESSION["qry_where"] = $qry_where;
$_SESSION["qry_sort"] = $qry_sort;
} // end query selected

$qry_cmd = $qry_sel . $qry_where . $qry_sort;
if ($_SESSION["debug"] || $local_debug) { echo $qry_cmd . “<BR>\n”;}

$qresult=mysql_query(“$qry_cmd”,$myconn);

$r_table=_mysql_result_all($qresult,’border rules=”all”‘);

echo $r_table;

?>
</body>

</html>

If someone clicked on a row in the table, we take those values and then pass them using POST method to the edit page.

File: generic_get_row.php

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01//EN"
"http://www.w3.org/TR/html4/strict.dtd">
<html>
<body>

<?php

// this form will auto submit if we are not in debug mode.
if (! $_SESSION["debug"] && ! $local_debug) {
echo “<body onload=’document.f_get_row.submit();’>”;
}

$local_debug = false;

if (! count($_REQUEST)) { $message = “no parameters passed”; die ($message);}

if ($_SESSION["debug"] || $local_debug) {
print_r ($_REQUEST);
echo “<BR>\n”;
}

session_id($_REQUEST["PHPSESSID"]);
session_start();

if ($_SESSION["debug"] || $local_debug) {
echo session_id() . “<BR>\n”;
echo $_SESSION['g_server'] . “<BR>\n”;
echo $_SESSION["g_user"] . “<BR>\n”;
echo $_SESSION["g_pass"] . “<BR>\n”;
echo $_SESSION["g_db"] . “<BR>\n”;
echo $_SESSION["g_table"] . “<BR>\n”;
echo “<BR>\n End SESSION values <br> \n”;
}

// build the form for passing the row info
$f_string = “<!–Debugging output for Form –>\n\n”;
$f_string .= “<form name=’f_get_row’ method=’post’ action=’generic_edit.php’>\n”;
$f_string .= “<table>\n”;

// build the form table value entries
reset($_REQUEST);

for ($i = 0; $i < $_REQUEST["_gdt_numfields"]; $i++) {
$f_string .= “<tr>\n”;
$f_string .= “\t<td>” . key($_REQUEST) . “</td>”;
$f_string .= “\t<td> <input type=’text’ name=’” . key($_REQUEST) . “‘ value=’” . current($_REQUEST) .”‘></td>\n”;
$f_string .= “</tr>\n”;
next($_REQUEST);
} // end for loop

$f_string .= “</table>\n”;
$f_string .= “<input type=’hidden’ name=’upd_table_column_count’ value=’” . $_REQUEST["_gdt_numfields"] . “‘>\n”;
$f_string .= “<input type=’submit’ name=’Submit’ value=’EDIT’>\n”;
$f_string .= “</Form>\n”;
$f_string .= “<!–End debug from SQL query–>\n\n”;

echo $f_string;
echo “<BR>\n”;
$_SESSION["returning"]=true;
?>

</body>
</html>

Now we get to the edit page, it uses the values POSTED from the clicked row info passed to the get_row form. We assume there is only one row that matches the selected row, there shouldn’t be duplicate rows in the tables.

File: generic_edit.php

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01//EN"
"http://www.w3.org/TR/html4/strict.dtd">
<html>
<body>

<?php
// this page should ALWAYS have POSTED values
if (! count($_POST)) { $message = “no parameters passed”; die ($message);}

session_id($_REQUEST["PHPSESSID"]);
session_start();

$local_debug = false;
if ($_SESSION["debug"] || $local_debug) { // debugging
print_r ($_POST);
echo “<BR>\n”;
echo session_id() . “<BR>\n”;
echo $_REQUEST["sid"] . “<BR>\n”;
echo $_SESSION['g_server'] . “<BR>\n”;
echo $_SESSION["g_user"] . “<BR>\n”;
echo $_SESSION["g_pass"] . “<BR>\n”;
echo $_SESSION["g_db"] . “<BR>\n”;
echo $_SESSION["g_table"] . “<BR>\n”;
echo “<BR>\n End SESSION values <br> \n”;
} // end debugging

$myconn = mysql_connect($_SESSION["g_server"], $_SESSION["g_user"], $_SESSION["g_pass"]);

@mysql_select_db($_SESSION["g_db"]) or die ( “Unable to select database”);

if ($_SESSION["debug"] || $local_debug) { echo “db selected <BR>\n”; }

$edit_query = “SELECT * FROM ” . $_SESSION['g_table'] . ” WHERE “;

// if the value is empty, then we need to check for empty as well as Null in the query.
reset($_POST);
for ($i = 0; $i < $_POST["upd_table_column_count"]; $i++) {
$q_table_columns[$i] = key($_POST);
$q_table_values[$i] = current($_POST);
if ($i != 0 ) { $edit_query .= ” and “; }
if ($q_table_values[$i] == ” || is_null($q_table_values[$i])) {
$edit_query .= “(” . $q_table_columns[$i] . “=’” . $q_table_values[$i] . “‘ OR ” . $q_table_columns[$i] . ” IS NULL)”;
} else {
$edit_query .= $q_table_columns[$i] . “=’” . $q_table_values[$i] . “‘”;
}
if ($_SESSION["debug"] || $local_debug) {echo $q_table_columns[$i] . ” = ” . $q_table_values[$i] . “<BR>\n”;}
next($_POST);
} // end fill loop

if ($_SESSION["debug"] || $local_debug) {echo $edit_query . “<BR>\n”;}

$qresult=mysql_query($edit_query . “;”,$myconn);
if (mysql_errno($myconn)) {
$message = mysql_errno($myconn) . “: ” . mysql_error($myconn) . “<br>\n”;
die($message);
}
if ($_SESSION["debug"] || $local_debug) {echo mysql_affected_rows() . “<BR>\n”;}

// Table should only have one row matching with the clicked row
if (mysql_Affected_rows() != 1) { $message = “matches=” . mysql_affected_rows() . ” No matches or more than one row matches results”; die($message);}

$_SESSION["old_column_count"]=$_POST["upd_table_column_count"];
$_SESSION["old_table_columns"]=$q_table_columns;
$_SESSION["old_table_values"]=$q_table_values;

// build the EDIT input form
$f_string = “<!–Debugging output for Form –>\n\n”;
$f_string .= “<form method=’post’ action=’./generic_update_add.php’>\n”;
$f_string .= “<table>\n”;
for ($i = 0; $i < $_POST["upd_table_column_count"]; $i++) {
$f_string .= “<tr>\n”;
$f_string .= “<td>” . $q_table_columns[$i] . “</td>”;
$f_string .= “<td><input type=’text’ name=’” . $q_table_columns[$i] . “‘ value=’” . $q_table_values[$i] . “‘></td>\n”;
} // end fill loop

$f_string .= “</table>\n”;
$f_string .= “<input type=’hidden’ name=’upd_add_table_column_count’ value=’” . $_POST["upd_table_column_count"] . “‘>\n”;
$f_string .= “<input type=’submit’ name=’EDIT’ value=’EDIT’>\n”;
$f_string .= “<input type=’submit’ name=’DELETE’ value=’DELETE’>\n”;
$f_string .= “</Form>\n”;
$f_string .= “<!–End debug from SQL query–>\n\n”;

echo $f_string;
echo “<BR>\n”;
$_SESSION["returning"]=true;

?>

</body>
</html>

Now we have the page that does the actual updating, adding, or deleting of rows from the table.

File: generic_update_add.php

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01//EN"
"http://www.w3.org/TR/html4/strict.dtd">
<html>

<body>

<?php

SESSION_start();

$local_debug = false;
if ($_SESSION["debug"] || $local_debug) { // debugging
Print_r ($_POST);
echo “<BR>\n”;
echo $_SESSION['g_server'] . “<BR>\n”;
echo $_SESSION["g_user"] . “<BR>\n”;
echo $_SESSION["g_pass"] . “<BR>\n”;
echo $_SESSION["g_db"] . “<BR>\n”;
echo $_SESSION["g_table"] . “<BR>\n”;
echo “<BR>\n End SESSION values <br> \n”;
} // end debugging

// this the function that does all updating, adding, deleting
FUNCTION update_add_row($update_or_add, $new_column_count, $new_column_names, $new_column_values, $old_column_count, $old_column_names, $old_column_values) {
// use the existing database connection
GLOBAL $myconn;

if ($_SESSION["debug"] || $local_debug) { echo $update_or_add;echo “<BR>\n”;echo “<BR>\n”; }

$new_q_values = ” SET “;
$set_count=0;
for ($i = 0; $i < $new_column_count; $i++) {
if ($new_column_values[$i] != $old_column_values[$i]) {
if ($set_count > 0) {$new_q_values .= “, “; }
$new_q_values .= $new_column_names[$i] . “=’” . $new_column_values[$i] . “‘”;
$set_count++;
}
} // end column loop

if ($_SESSION["debug"] || $local_debug) { echo $new_q_values; echo “<BR><br>\n”; }

if ($update_or_add == “ADD”) {
$sql_cmd = “INSERT INTO ” . $_SESSION["g_table"] . $new_q_values;
} else {
// update or add uses the old column data

// if the value is empty, then we need to check for empty as well as Null in the query.
$old_q_values = ” WHERE “;
for ($i = 0; $i < $old_column_count; $i++) {
if ($old_column_values[$i] == ” || is_null($old_column_values[$i])) {
$old_q_values .= “(” . $old_column_names[$i] . “=” OR ” . $old_column_names[$i] . ” IS NULL)”;
} else {
$old_q_values .= $old_column_names[$i] . “=’” . $old_column_values[$i] . “‘”;
}

if ($i < $old_column_count – 1) { $old_q_values .= ” and “; }

} // end column loop

if ($update_or_add == “DELETE”) {
if ($_SESSION["debug"] || $local_debug) { echo $old_q_values; echo “<BR><br>\n”; }
$sql_cmd = “DELETE FROM ” . $_SESSION["g_table"] . $old_q_values;
} else {
// if we aren’t adding or deleting, we must be updating

if ($_SESSION["debug"] || $local_debug) { echo $old_q_values; echo “<BR><br>\n”; }
$sql_cmd = “UPDATE ” . $_SESSION["g_table"] . $new_q_values . $old_q_values;
}
}
if ($_SESSION["debug"] || $local_debug) { echo $sql_cmd;echo “<BR>\n”;}

return $sql_cmd;
} // end of update_row function

$myconn = mysql_connect($_SESSION["g_server"], $_SESSION["g_user"], $_SESSION["g_pass"]);

@mysql_select_db($_SESSION["g_db"]) or die ( “Unable to select database”);

echo “db selected”;
echo “<BR>\n”;

// fill our new column name and value arrays;
reset($_POST);
for ($i = 0; $i < $_POST['upd_add_table_column_count']; $i++) {
$new_table_columns[$i] = key($_POST);
$new_table_values[$i] = current($_POST);
if ($_SESSION["debug"] || $local_debug) {echo $new_table_columns[$i] . ” = ” . $new_table_values[$i] . “<BR>”;}
next($_POST);
} // end fill loop

if ($_SESSION["debug"] || $local_debug) { echo “done loop<br>\n”;}

if (isset($_POST["ADD"])) {
if ($_SESSION["debug"] || $local_debug) { echo “ADD<br>\n”;}

$r_cmd = update_add_row(“ADD”, $_POST['upd_add_table_column_count'], $new_table_columns, $new_table_values, $old_column_count, $old_table_columns, $old_table_values);
if ($_SESSION["debug"] || $local_debug) { echo $cmd; echo “<br>\n”;}
mysql_query(“$r_cmd”,$myconn);
if (mysql_errno($myconn)) {
$message = “Error Adding record into database<br>\n”;
$message .= mysql_errno($myconn) . “: ” . mysql_error($myconn) . “<br>\n”;
die($message);
}
echo mysql_affected_rows() . ” Row Added”;
$_SESSION["returning"] = true;
echo “<a href=’./generic_add.php’>Return to Entry page</a>”;
} else {
if ($_SESSION["debug"] || $local_debug) { echo “Update or Delete<br>\n”;}

// fill our old column name and value arrays from the session variables

$old_column_count = $_SESSION["old_column_count"];
$old_table_columns = $_SESSION["old_table_columns"];
$old_table_values = $_SESSION["old_table_values"];

if (isset($_POST["DELETE"])) {
if ($_SESSION["debug"] || $local_debug) { echo “DELETE<br>\n”;}
$r_cmd = update_add_row(“DELETE”, $_POST['upd_add_table_column_count'], $new_table_columns, $new_table_values, $old_column_count, $old_table_columns, $old_table_values);
if ($_SESSION["debug"] || $local_debug) { echo $cmd; echo “<br>\n”;}
} else {
if ($_SESSION["debug"] || $local_debug) { echo “UPDATE<br>\n”;}
$r_cmd = update_add_row(“UPDATE”, $_POST['upd_add_table_column_count'], $new_table_columns, $new_table_values, $old_column_count, $old_table_columns, $old_table_values);
if ($_SESSION["debug"] || $local_debug) { echo $cmd; echo “<br>\n”;}
}
mysql_query(“$r_cmd”,$myconn);
echo mysql_affected_rows() . ” Row Updated <BR>\n”;
if (mysql_errno($myconn)) {
$message = “Error Adding record into database<br>\n”;
$message .= mysql_errno($myconn) . “: ” . mysql_error($myconn) . “<br>\n”;
die($message);
}
$_SESSION["returning"] = true;
// after showing the update or delete results, link back to the table page

echo “<a href=’./generic_display_table.php’>Return to table</a>”;
}

if ($_SESSION["debug"] || $local_debug) { echo “Bottom<br>\n”;}

?>

</body>

</html>

And finally we have the page for adding new rows to the table

file: generic_add.php

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01//EN"
"http://www.w3.org/TR/html4/strict.dtd">
<html>
<body>

<?php
session_start();

$local_debug = false;

if ($_SESSION["debug"] || $local_debug) {
print_r ($_POST);
echo “<BR>\n”;
echo “SID = ” . session_id() . “<BR>”;
echo “as = ” . intval(session.auto_start) . “<BR>”;
echo “s name = ” . session_name() . “<BR>”;
}

if (! $_SESSION["returning"]) {
$_SESSION["g_server"] = $_POST['posted_server'];
$_SESSION["g_user"] = $_POST['posted_user'];
$_SESSION["g_pass"] = $_POST['posted_pass'];
$_SESSION["g_db"] = $_POST['posted_db'];
$_SESSION["g_table"] = $_POST['posted_table'];
$_SESSION["returning"] = false;
}

if ($_SESSION["debug"] || $local_debug) {
echo $_SESSION["g_server"] . “<BR>\n”;
echo $_SESSION["g_user"] . “<BR>\n”;
echo $_SESSION["g_pass"] . “<BR>\n”;
echo $_SESSION["g_db"] . “<BR>\n”;
echo $_SESSION["g_table"] . “<BR>\n”;
}

$myconn = mysql_connect($_SESSION["g_server"], $_SESSION["g_user"], $_SESSION["g_pass"]);

@mysql_select_db($_SESSION["g_db"]) or die ( “Unable to select database”);

if ($_SESSION["debug"] || $local_debug) { echo “db selected <BR>\n”; }

// $result = mysql_query(“SHOW COLUMNS FROM $g_table”,$myconn);
$result = mysql_query(“SHOW COLUMNS FROM ” . $_SESSION['g_table'],$myconn);

if (mysql_errno($myconn)) {
$message .= mysql_errno($myconn) . “: ” . mysql_error($myconn) . “<br>\n”;
die($message);
}
if ($_SESSION["debug"] || $local_debug) { echo “query ran”; echo “<BR>\n”; }

// build the ADD input form

$f_string = “<!–Debugging output for Form –>\n\n”;
$f_string .= “<form method=’post’ action=’generic_update_add.php’>\n”;
$f_string .= “<table>\n”;
// build the table value entries
$count = 0;
while ($row=mysql_fetch_row($result)){
$cnt = 0;
foreach ($row as $item){
if ($cnt == 0){
$f_string .= “<tr>\n”;
$f_string .= “\t<td>” . $item . “</td>\n” . “\t<td> <input type=’text’ name=’” . $item . “‘></td>\n”;
$f_string .= “</tr>\n”;
$cnt++;
$count++;
} //end cnt = 0
} // foreach row
}

$f_string .= “</table>\n”;
$f_string .= “<input type=’hidden’ name=’upd_add_table_column_count’ value=’” . $count . “‘>\n”;
$f_string .= “<input type=’submit’ name=’ADD’ value=’ADD’>\n”;
$f_string .= “</Form>\n”;
$f_string .= “<!–End debug from SQL query–>\n\n”;

echo $f_string;
echo “<BR>\n”;

?>

</body>
</html>

That is it… all the pages should reside in the same directory on your web server.

If the credentials entered in the initial ‘post’ page do not have the proper access, there will be errors encountered when trying to do unauthorized insert, update, or delete operations.

If your organization has some standards for key fields, you could easily add checks for those and not show them, or not let them be changeable. These pages can also be modified and combined for single task usage (that is what started this project for me)

I would like to offer many thanks for the people who have contributed to the various products used, MySQL, PHP, SUSE Linux, Apache. And particularly to those contributing to the online documentation of those products and others who have posted questions and their solutions in the various forums for the products.

Special thanks to Jamie Cameron, for his WebMin linux management application which was frequently used for quick access to edit and save the various pages created.

VN:F [1.9.22_1171]
Rating: 0.0/5 (0 votes cast)

Tags: ,
Categories: SUSE Linux Enterprise Server, Technical Solutions

Disclaimer: As with everything else at SUSE Conversations, this content is definitely not supported by SUSE (so don't even think of calling Support if you try something and it blows up).  It was contributed by a community member and is published "as is." It seems to have worked for at least one person, and might work for you. But please be sure to test, test, test before you do anything drastic with it.

Comment

RSS