Manage mysql databases using php the CRUD way
i posted this code more than a year ago, in Tek-Tips. It’s buried in old posts now but still generically useful, so I thought i’d give it a new lease of life.
CRUD: Create Retrieve Update and Delete. An acronym for the classic database manipulations.
I find myself almost exclusively developing database and form driven sites and abstracted code such as this is often useful for simple mock-ups. For anything more serious, I either design bespoke or (less frequently nowadays) I design using HTML_QuickForm from the pear repository.
This code is plug and play. Just complete your database access credentials at the top and point your browser at the file. One day i’ll make this into a pretty, encapsulated, class. Until then, here it is:
<? session_start(); $GLOBALS['msg'] = ""; @mysql_connect ("","","") or die (mysql_error()); @mysql_select_db("") or die (mysql_error()); $action = isset($_POST['action']) ? $_POST['action'] : ( isset ($_GET['action']) ? $_GET['action'] : "display_list" ); switch ($action): case "settable": if (isset($_POST['curtable'])): $_SESSION['curtable'] = trim($_POST['curtable']); $result = mysql_query("SHOW INDEX FROM {$_SESSION['curtable']}") or die(mysql_error()); while ($row=mysql_fetch_assoc($result)): if($row['Key_name'] === "PRIMARY"): $_SESSION['curtable_primarykey'] = $row['Column_name']; endif; endwhile; endif; display_select(); display_list(); break; case "add_new": display_select(); add_new(); break; case "save_new": save_new(); display_select(); display_list(); break; case "editrecord": display_select(); edit(); break; case "save_edit": save_edit(); display_select(); display_list(); break; case "deleterecord": delete_record(); display_select(); display_list(); break; case "display_list": default: display_select(); display_list(); endswitch; function display_select() { $field = get_tables(); echo <<<EOL <form method="post" action="{$_SERVER['PHP_SELF']}"> <input type="hidden" name="action" value="settable" /> Select Table to work on: $field <input type="submit" value="Go" name="submit"/> </form> <br/><br/> <hr/> EOL; } function save_new() { $tmp = ""; foreach ($_POST as $key=>$val): $val = mysql_escape_string(trim($val)); if (!in_array($key, array("submit", "action"))): $tmp .= <<<EOL $key = '$val', EOL; endif; endforeach; $query = "Insert into {$_SESSION['curtable']} set " . rtrim($tmp, ","); $result = @mysql_query($query); if ($result === FALSE): $GLOBALS['msg'] = "Record not saved. ".mysql_error(); else: $GLOBALS['msg'] = "Record saved."; endif; } function save_edit() { $tmp = ""; foreach ($_POST as $key=>$val): $val = mysql_escape_string(trim($val)); if (!in_array($key, array("submit", "action"))): $tmp .= <<<EOL $key = '$val', EOL; endif; endforeach; $query = "Replace into {$_SESSION['curtable']} set " . rtrim($tmp, ","); $result = mysql_query($query); if ($result === FALSE): $GLOBALS['msg'] = "Record not saved. ".mysql_error(); else: $GLOBALS['msg'] = "Record saved."; endif; } function edit() { if (empty($_GET['key'])): $GLOBALS['msg'] = "No primary key provided."; exit; else: $key = mysql_escape_string(trim($_GET['key'])); endif; $qry = "Select * from {$_SESSION['curtable']} where {$_SESSION['curtable_primarykey']}='$key'"; echo "query is $qry"; $result = mysql_query($qry) or die (mysql_error()); $form = <<<EOL <form method="post" action="{$_SERVER['PHP_SELF']}"> <input type="hidden" name="action" value="save_edit"> <table width="70%"> @@CONTENTS@@ </table> </form> EOL; $tmp = ""; $row=mysql_fetch_assoc($result); foreach ($row as $key=>$val): if ($key === $_SESSION['curtable_primarykey']): $type="text"; $readonly = "readonly"; else: $type="text"; $readonly = ""; endif; $tmp .= <<<EOL <tr> <td>$key</td> <td><input $readonly type="$type" name="$key" value="$val" /></td> </tr> EOL; endforeach; $tmp .= <<<EOL <tr> <td> </td> <td><input type="submit" value="Save" name="submit" /></td> </tr> EOL; $form = str_replace("@@CONTENTS@@", $tmp, $form); echo $form; } function add_new() { $result = @mysql_query("show columns from ".$_SESSION['curtable']) or die(mysql_error()); $form = <<<EOL <form method="post" action="{$_SERVER['PHP_SELF']}"> <input type="hidden" name="action" value="save_new"> <table width="70%"> @@CONTENTS@@ </table> </form> EOL; $tmp = ""; while ($field = mysql_fetch_assoc($result)): if ($field['Key'] === "PRI"): $type="text"; $default="NULL"; $readonly = "readonly"; else: $type="text"; $default = empty($field['Default']) ? "" : $field['Default'] ; $readonly = ""; endif; $tmp .= <<<EOL <tr> <td>{$field['Field']}</td> <td><input type="$type" $readonly name="{$field['Field']}" value="$default" /></td> </tr> EOL; endwhile; $tmp .= <<<EOL <tr> <td> </td> <td><input type="submit" value="Save" name="submit" /></td> </tr> EOL; $form = str_replace("@@CONTENTS@@", $tmp, $form); echo $form; } function delete_record () { if (!isset($_GET['key'])): //don't do anything $GLOBALS['msg'] = "Nothing to delete"; else: $result = mysql_query ("Delete from ".$_SESSION['curtable']." where ". $_SESSION['curtable_primarykey'] ." = '".mysql_escape_string(trim($_GET['key']))."'"); if ($result === false): $GLOBALS['msg'] = mysql_error(); else: $GLOBALS['msg'] = "Record successfully deleted"; endif; endif; } function get_tables() { $result = mysql_query("show tables") or die (mysql_error()); $options = ""; while ($row = mysql_fetch_array($result)): if (!isset($_SESSION['curtable'])): if (isset($flag)): $selected = ""; else: $selected = "selected"; endif; else: $selected = ($row[0] === $_SESSION['curtable']) ? "selected" : ""; endif; $options .= "\t<option selected value=\"{$row[0]}\">{$row[0]}</option>\r\n"; endwhile; return "<select name=\"curtable\">$options</selected>"; } function display_list() { if (!isset($_SESSION['curtable'])): exit; endif; $result = mysql_query("Select * from ". $_SESSION['curtable']); if ($result === false) {die (mysql_error());} echo "<h2>Data for table {$_SESSION['curtable']}</h2>"; if (!empty($GLOBALS['msg'])): echo "<div class=\"message\">{$GLOBALS['msg']}</div>"; endif; echo "<div class=\"addnew\"><a href=\"{$_SERVER['PHP_SELF']}?action=add_new\">Add New</a></div>"; $table = "<table class=\"displaylist\">\r\n"; while ($row = mysql_fetch_assoc($result)): echo "<tr>"; $k = $row[$_SESSION['curtable_primarykey']]; if (!isset($flag)): $line1=""; $line2=""; foreach ($row as $key=>$val): $line1.="\t<th>$key</th>\r\n"; $line2.="\t<td>$val</td>\r\n"; endforeach; $line1 = "<tr class=\"heading\">\r\n $line1 \r\n \t<td> </td>\r\n \t<td> </td>\r\n </tr>"; $row = "odd"; $line2 = "<tr class=\"$row\">\r\n $line2 \r\n"; $line2.="\t<td><a href=\"".$_SERVER['PHP_SELF']."?action=editrecord&key=$k\">Edit</a></td>\r\n"; $line2.="\t<td><a onClick=\"if (confirm('Please confirm deletion')) {return true;} else {return false;}\" href=\"".$_SERVER['PHP_SELF']."?action=deleterecord&key=$k\">Delete</a></td>\r\n"; $line2.="</tr>"; $table .= $line1 . $line2; $flag=true; else: $line=""; foreach ($row as $key=>$val): $line.="\t<td>$val</td>\r\n"; endforeach; $line.="\t<td><a href=\"".$_SERVER['PHP_SELF']."?action=editrecord&key=$k\">Edit</a></td>\r\n"; $line.="\t<td><a onClick=\"if (confirm('Please confirm deletion')) {return true;} else {return false;}\" href=\"".$_SERVER['PHP_SELF']."?action=deleterecord&key=$k\">Delete</a></td>\r\n"; $row = ($row=="odd")?"even":"odd"; $line = "<tr class=\"$row\">\r\n $line \r\n </tr>"; $table .= $line; endif; endwhile; echo $table . "</table>"; } |
October 2nd, 2008 in
PHP, Sample Classes
This is an excellent utility. For a guru like yourself, it’s perfect, but for someone like myself (a newbie) attempting to learn, it’s kind of intimidating. One thing that would make this perfect is if some detailed comments could be added to explain what’s going on. As it is, I’ll be scouring the PHP manual to figure this out. (Not necessarily a bad thing.) Thanks for posting it.