connection = NULL; $this->old_values = NULL; $this->error = NULL; ini_set("memory_limit",PHPWriter::mem_max_meg . "M"); // print lots of error messages if(PHPWriter::debug) { error_reporting(E_ALL|E_STRICT); ini_set('display_errors', true); ini_set('html_errors', true); } $_POST['server'] = PHPWriter::fetch_value_if_exists($_GET,'server',PHPWriter::default_server); $_POST['user'] = PHPWriter::fetch_value_if_exists($_GET,'user',PHPWriter::default_user); $_POST['database'] = PHPWriter::fetch_value_if_exists($_GET,'database',PHPWriter::default_db); $_POST['table'] = PHPWriter::fetch_value_if_exists($_GET,'table',PHPWriter::default_table); $_POST['query'] = PHPWriter::fetch_value_if_exists($_GET,'query',PHPWriter::default_query); } function preserve_error() { if(mysql_errno() != 0) { $this->error .= mysql_error() . "
\n"; } } function dump_array($array) { $output = "" . time() . "
\n"; if($array) { $keys = array_keys($array); for($i = 0;$i < count($keys);$i++) { $output .= $keys[$i] . " = [" . $array[$keys[$i]] . "]
\n"; } } return $output; } function wrap_tag($data,$tag,$extra = "") { return "<$tag $extra>$data\n"; } function fetch_value_strict($base,$name,$default = '') { $result = NULL; if($base && array_key_exists($name,$base) && strlen($base[$name]) > 0) { $result = $base[$name]; } else if ($default && strlen($default) > 0) { $result = $default; } return $result; } function fetch_value_if_exists($base,$name,$default = '') { $result = NULL; if($base && array_key_exists($name,$base) && sizeof($base[$name]) > 0) { $result = $base[$name]; } else if ($default && strlen($default) > 0) { $result = $default; } else if($this->old_values && array_key_exists($name,$this->old_values) && strlen($this->old_values[$name]) > 0) { $result = $this->old_values[$name]; } return $result; } function get_assoc_values($base) { return array( $this->fetch_value_if_exists($base,'server'), $this->fetch_value_if_exists($base,'user'), $this->fetch_value_if_exists($base,'password'), $this->fetch_value_if_exists($base,'database'), $this->fetch_value_if_exists($base,'table'), $this->fetch_value_if_exists($base,'query'), $this->fetch_value_if_exists($base,'record') ); } function set_values($names,$values) { $n = 0; $base = array(); foreach($names as $name) { $base[$name] = $values[$n]; $n++; } return $base; } function make_text_field($type,$title,$name,$value,$tip, $width = PHPWriter::textfield_width) { $btitle = $this->wrap_tag($title . ":","b"); $result = $this->wrap_tag(" " . $btitle,"td"); $input = "\n"; $result .= $this->wrap_tag($input,"td"); return $result; } function make_dropdown_list($title,$name,$value,$val_array,$tip) { $btitle = $this->wrap_tag($title . ":","b"); $result = $this->wrap_tag(" " . $btitle,"td"); $list = ""; if($val_array) { foreach ($val_array as $item) { $sel = ($item == $value)?"selected":""; $list .= $this->wrap_tag($item,"option",$sel); } } $substr = "name = \"$name\" title=\"$tip\" onChange=\"submit();\""; $list = $this->wrap_tag($list,"select",$substr); $result .= $this->wrap_tag($list,"td"); return $result; } function create_table_header() { $row = ""; mysql_data_seek($this->header_result,0); while($field = mysql_fetch_row($this->header_result)) { $row .= $this->wrap_tag($field[0],"td","class=\"cellTitle\""); } return $this->wrap_tag($row,"tr"); } function create_table_rows() { $result = NULL; if($this->query_result) { $result = array(); $record = $this->fetch_value_if_exists($_POST,'record'); $n = 0; mysql_data_seek($this->query_result,0); while($rec = mysql_fetch_assoc($this->query_result)) { $selected = ($record == $n); $to_mark = ($record == $n+PHPWriter::record_list_offset); $row = ""; mysql_data_seek($this->header_result,0); while($field = mysql_fetch_row($this->header_result)) { $fdata = $rec[$field[0]]; $fdata = htmlspecialchars($fdata); $fdata = preg_replace("/\n/m","
\n",$fdata); if($to_mark) { $fdata .= ""; } $row .= $this->wrap_tag($fdata,"td"); } $class = ($selected)?"selectcell":"cell" . ($n % 2); $result[] = $this->wrap_tag($row,"tr","class=\"$class\" onClick=\"choose_row(" . $n . ");\""); $n++; } } return $result; } function print_data_table() { list($server,$user,$password,$database,$table) = $this->get_assoc_values($_POST); if($server && $user && $database && $table) { if($this->header_result) { $table = ""; if($header = $this->create_table_header()) { $table .= $header; if($table_rows = $this->create_table_rows()) { $table .= join($table_rows,"\n"); $table = $this->wrap_tag($table,"table","width=100% title=\"Click a record to edit it below\""); $wraptable = $this->fetch_value_if_exists($_POST,'Wraptable'); if(!$wraptable[0]) { print "\n"; } print $table; } else { // no matching records note print "
No matching records.
\n"; } } } } } function create_data_form_rows($del_name,$newEmptyFlag) { $result = array(); if($this->query_result) { $lines = $this->get_field_lines(); $record = $this->fetch_value_if_exists($_POST,'record',0); $record = $this->set_record_range($this->record_count,$record); $_POST['record'] = $record; mysql_data_seek($this->query_result,$record); $rdata = mysql_fetch_assoc($this->query_result); mysql_data_seek($this->header_result,0); while($field = mysql_fetch_row($this->header_result)) { $name = $field[0]; $value = NULL; // automagically create a date or time // for fields with those names if($newEmptyFlag) { if(preg_match("/^date$/i",$name)) { $value = date("Y-m-d"); } if(preg_match("/^time$/i",$name)) { $value = date("H:i:s"); } } else if (isset($rdata[$name])) { $value = $rdata[$name]; } $title = $this->wrap_tag($name .":","b"); $row = $this->wrap_tag($title,"td","width=1%"); $format_val = htmlspecialchars($value,ENT_QUOTES); // delete possible primary key if($name == $del_name) { $format_val = ""; } if($lines < 2) { $entry_field = ""; } else { $entry_field = ""; } $row .= $this->wrap_tag($entry_field,"td"); $result[] = $this->wrap_tag($row,"tr"); } //$_SESSION['record'] = $record; } return $result; } function get_field_names($server,$user,$password,$database,$table) { $this->preserve_error(); mysql_select_db($database); return mysql_query("show columns from " . $table,$this->connection); } function build_query($table,$query) { $sql = ""; if($query && strlen($query) > 0) { // data from query field if ($query == "*") { $query = ".*"; } $queryhtml = htmlspecialchars($query,ENT_QUOTES); $_POST['query'] = $queryhtml; $sql = "select * from " . $table . " where "; $equery = mysql_real_escape_string($query); mysql_data_seek($this->header_result,0); $args = array(); while ($row = mysql_fetch_row($this->header_result)) { $name = $row[0]; $args[] = " `" . $name . "` regexp '" . $equery . "' "; } $sql .= join($args," or "); } else { $sql = "select * from $table"; } return $sql; } function perform_standard_query($server,$user,$password,$database,$table,$query) { $this->connection = mysql_connect($server,$user,$password); $this->preserve_error(); $this->header_result = $this->get_field_names($server,$user,$password,$database,$table); $arg = $this->build_query($table,$query); mysql_select_db($database); if($this->query_result = mysql_query($arg,$this->connection)) { $this->record_count = mysql_num_rows($this->query_result); } } function set_test_flags() { $this->insertRecordFlag = false; $this->changedFlag = (array_key_exists('changed',$_POST) && $_POST['changed'] == 'true'); $this->acceptRecordFlag = array_key_exists('AcceptRecord',$_POST); $this->newCopyFlag = array_key_exists('NewCopy',$_POST); $this->newEmptyFlag = array_key_exists('NewEmpty',$_POST); $this->deleteRecordFlag = array_key_exists('DeleteRecord',$_POST); $this->execDelete = array_key_exists('DeleteRecordConfirm',$_POST); } function create_data_form() { $dtable = NULL; list($server,$user,$password,$database,$table,$query,$record) = $this->get_assoc_values($_POST); if($server && $user && $database && $table) { $this->set_test_flags(); $del_name = NULL; // look for primary key if($this->newCopyFlag) { $del_name = $this->test_fields_for_key($server,$user,$password,$database,$table); } $table_rows = $this->create_data_form_rows($del_name,$this->newEmptyFlag); $dtable = join($table_rows,"\n"); $dtable = $this->wrap_tag($dtable,"table"); } $also = ""; if($this->newCopyFlag || $this->newEmptyFlag) { // carry this forward $spec = ($this->newCopyFlag)?"Copy":"Empty"; $also .= "

\n"; $also .= "

New $spec record: enter record data, then press \"Accept\" to save your new entry.
\n"; $also .= "Use \"Tab\", not \"Enter\", to move between fields.
\n"; } else if($this->deleteRecordFlag) { // also carry forward $also .= "

Delete: press "; $also .= " "; $also .= " to delete record " . ($record+1) . ", or  "; $also .= ""; $also .= "\n"; // this prevents multiple deletes if the user // reloads the page after a delete $_SESSION['arm_delete'] = true; } else { $lines = $this->get_field_lines(); $note = ($lines == 1)?"or \"Enter\" ":""; $item = "Press \"Accept\" $note to commit changes to the database."; $also .= "

$item
"; } if($this->error) { $also .= "SQL Error: $this->error
\n"; } if($this->acceptRecordFlag && !$this->changedFlag) { $also .= "NOTE: No changes to commit.
\n"; } return $dtable . $also; } function get_database_list() { $result = NULL; list($server,$user,$password,$database) = $this->get_assoc_values($_POST); if($server && $user) { $this->connection = mysql_connect($server,$user,$password); $this->preserve_error(); if($this->connection) { $result = array(); $db_list = mysql_list_dbs($this->connection); while ($row = mysql_fetch_row($db_list)) { $result[] = $row[0]; } } } return $result; } function get_table_list() { $result = NULL; list($server,$user,$password,$database,$table) = $this->get_assoc_values($_POST); if($server && $user && $database) { $this->connection = mysql_connect($server,$user,$password); $this->preserve_error(); if($this->connection) { $result = array(); $table_list = mysql_query("show tables from " . $database,$this->connection); while ($row = mysql_fetch_row($table_list)) { $result[] = $row[0]; } mysql_free_result($table_list); } } return $result; } function get_data_table($force = false) { list($server,$user,$password,$database,$table,$query,$record) = $this->get_assoc_values($_POST); list($oserver,$ouser,$opassword,$odatabase,$otable,$oquery,$orecord) = $this->get_assoc_values($this->old_values); // preconditions for creating a new data table if($server && $user && $database && $table) { $no_table = $this->query_result == NULL; if($force || $no_table || ($database != $odatabase || $table != $otable || $query != $oquery)) { $this->perform_standard_query($server,$user,$password,$database,$table,$query); if($this->query_result) { // new table, test record value $record = $this->set_record_range($this->record_count,$record); $_POST['record'] = $record; } } } } function get_field_lines() { return $this->fetch_value_if_exists($_POST,'fieldlines',1); } function build_field_lines_control() { $lines = $this->get_field_lines(); $output=""; $n = 1; while($n < 32) { $ch = ($n == $lines)?"selected":""; $output .= "\n"; $n <<= 1; } $title = $this->wrap_tag("Field rows:","td"); $output = $this->wrap_tag($output,"select","name=\"fieldlines\" title=\"Select number of entry rows\" onChange=\"submit();\""); return $title . $this->wrap_tag($output,"td"); } function make_control_tables($add_this) { $tables = ""; $output = $add_this; $query = $this->fetch_value_if_exists($_POST,'query','.*'); if(strlen($query) < 1) { $query = '.*'; } $output .= $this->make_text_field("text","Query","query",$query,"Enter a search query (applied to all fields)",18); $record = $this->fetch_value_if_exists($_POST,'record'); $output .= "\n"; $output .= "\n"; $output .= "\n"; $row = $this->wrap_tag($output,"tr"); $tables .= $this->wrap_tag($row,"table","align=\"center\""); $output = ""; $output .= ""; $output .= ""; $output .= ""; $output .= " Record " . ($record+1) . " of " . $this->record_count . " "; $output .= ""; $output .= ""; $output .= "\n"; $output .= $this->build_field_lines_control(); $wraptable = $this->fetch_value_if_exists($_POST,'Wraptable'); $checked = ($wraptable[0])?"checked":""; $output .= "Wrap:\n"; $output .= "\n"; $output .= "\n"; $row = $this->wrap_tag($output,"tr"); $tables .= $this->wrap_tag($row,"table","align=\"center\""); return $tables; } // deletes any primary key indices // so new record can be created function test_fields_for_key() { $name = NULL; mysql_data_seek($this->header_result,0); $i = 0; while($field = mysql_fetch_row($this->header_result)) { $name = $field[0]; $field_flags = mysql_field_flags($this->query_result,$i); if(preg_match("/primary_key/",$field_flags)) { break; } } return $name; } function set_record_range($max,$record) { $record = ($record < 0)?0:$record; $record = ($record >= $max)?$max-1:$record; return $record; } function read_write_db() { list($server,$user,$password,$database,$table,$query,$record) = $this->get_assoc_values($_POST); list($oserver,$ouser,$opassword,$odatabase,$otable,$oquery,$orecord) = $this->get_assoc_values($this->old_values); if($server && $user && $database && $table) { $this->set_test_flags(); if(($this->changedFlag && $this->acceptRecordFlag) || $this->execDelete) { $record = $_POST['record']; mysql_data_seek($this->query_result,$record); $rdata = mysql_fetch_assoc($this->query_result); if($this->newCopyFlag || $this->newEmptyFlag) { $writesql = "INSERT "; // position new record past end mysql_data_seek($this->query_result,$this->record_count); $record = $this->record_count; $_POST['record'] = $record; } else if ($this->execDelete) { // this prevents a double delete if the user // reloads the page immediately after a delete if($_SESSION['arm_delete']) { $writesql = "DELETE FROM "; $_SESSION['arm_delete'] = false; $this->cancel_record_transaction(); } else { // abort on invalid delete return; } } else { $writesql = "UPDATE "; } $writesql .= $database . "." . $table; if($this->acceptRecordFlag || $this->insertRecordFlag) { $writesql .= " SET "; mysql_data_seek($this->header_result,0); $i = 0; $args = array(); while($row = mysql_fetch_row($this->header_result)) { $name = $row[0]; $post_name = str_replace(" ","_",$name); $data = $_POST[$post_name]; $data = mysql_real_escape_string($data); $args[] = "`" . $name . "` = '" . $data . "'"; } $writesql .= join($args,","); $this->cancel_record_transaction(); } if(!($this->newCopyFlag || $this->newEmptyFlag)) { $writesql .= " WHERE "; mysql_data_seek($this->header_result,0); $args = array(); while($row = mysql_fetch_row($this->header_result)) { $name = $row[0]; $data = addslashes($rdata[$name]); $args[] = "`" . $name . "` = '" . $data . "'"; } $writesql .= join($args," AND "); } mysql_query($writesql,$this->connection); $this->preserve_error(); // must update changed table $this->get_data_table(true); } } } function cancel_record_transaction() { unset($_POST['AcceptRecord']); unset($_POST['NewCopy']); unset($_POST['NewEmpty']); unset($_POST['DeleteRecord']); unset($_POST['DeleteRecordConfirm']); } function process_inputs() { $found = false; $record = $_POST['record']; if(array_key_exists("Cancel",$_POST)) { $found = true; } else if(array_key_exists("ToBegin",$_POST)) { $record = 0; $found = true; } else if(array_key_exists("ToEnd",$_POST)) { $record = $this->record_count-1; $found = true; } else if(array_key_exists("Prior",$_POST)) { $record--; $found = true; } else if(array_key_exists("Next",$_POST)) { $record++; $found = true; } else if(array_key_exists("Prior10",$_POST)) { $record -= 10; $found = true; } else if(array_key_exists("Next10",$_POST)) { $record += 10; $found = true; } if($found) { $this->cancel_record_transaction(); $record = $this->set_record_range($this->record_count,$record); $_POST['record'] = $record; } else { $this->read_write_db(); } } function create_validation_fields() { if(!array_key_exists('record',$_POST)) { $_POST['record'] = 0; $_POST['Wraptable'] = array(PHPWriter::default_wrap); } $ptable = ""; list($server,$user,$password,$database,$table,$query,$record) = $this->get_assoc_values($_POST); // now check for command-line arguments $server = $this->fetch_value_strict($_GET,'server',$server); $user = $this->fetch_value_strict($_GET,'user',$user); $database = $this->fetch_value_strict($_GET,'database',$database); $table = $this->fetch_value_strict($_GET,'table',$table); $query = $this->fetch_value_strict($_GET,'query',$query); list($oserver,$ouser,$opassword,$odatabase,$otable,$oquery,$orecord) = $this->get_assoc_values($this->old_values); if($database != $odatabase || $table != $otable || $query != $oquery) { $this->cancel_record_transaction(); } $row = ""; $row .= $this->make_text_field("text","Server","server",$server,"Enter the MySQL server machine name"); $row .= $this->make_text_field("text","User","user",$user,"Enter the valid user for the database"); $row .= $this->make_text_field("password","Password","password",$password,"Enter password if required"); $db_list = $this->get_database_list(); if($db_list) { if($database == NULL) { if(!$data = $this->fetch_value_if_exists($_GET,'database')) { $database = $db_list[0]; } } $_POST['database'] = $database; } $db_list = $this->make_dropdown_list("Database","database",$database,$db_list,"Select a database"); $table_list = $this->get_table_list(); if($table_list) { if($table && ($database != $odatabase)) { $table = $this->fetch_value_if_exists($_GET,'table',$table_list[0]); } elseif ($table == NULL) { if(!$table = $this->fetch_value_if_exists($_GET,'table')) { $table = $table_list[0]; } } $_POST['table'] = $table; } $this->get_data_table(true); $this->process_inputs(); $table_dropdown = $this->make_dropdown_list("Table","table",$table,$table_list,"Select a table"); $row .= $db_list; $row = $this->wrap_tag($row,"tr"); $ptable .= $this->wrap_tag($row,"table","align=\"center\""); $control_tables = $this->make_control_tables($table_dropdown); $ptable .= $control_tables; $this->old_values = $this->set_values(array('server','user','password','database','table','query','record'),array($server,$user,$password,$database,$table,$query,$record)); return $ptable; } function set_focus_to_password() { if($this->error) { if(preg_match("/password/i",$this->error)) { print ""; print "SQL Error: $this->error
\n"; } $this->error = NULL; } } } // class PHPWriter // create persistent class instance if (array_key_exists('class_instance',$_SESSION)) { $writer = $_SESSION['class_instance']; } else { // create new instance $writer = new PHPWriter; $_SESSION['class_instance'] = $writer; } // this is a hack to force my HTML beautifier // to ignore all the HTML embedded in the above PHP block //

?> <?php print PHPWriter::program_name . " " . PHPWriter::program_version . "\n"; ?> "); ?>
create_validation_fields(); if($ptable) { $dtable = $writer->create_data_form(); $writer->print_data_table(); } ?>
\n"; ?> \n"); $writer->set_focus_to_password(); ?>