There are certain tasks I end up doing for every project, like creating database inserts, selects, and updates. I hate the tedium and repetitive nature of copy-pasting, or find and replace operations. The more work I do for clients, the more I realize I’m doing similar work for each client. This is my first step towards automating repetitive tasks so I can focus more time on the fun parts of development, like UI design and problem-solving.
I created a simple script that generates PHP PDO code for you with just the table name, id field name, and a comma-separated list of non-automated (i.e. columns that the database doesn’t generate for you, like timestamps and auto-increment IDs) DB columns. It generates INSERT, UPDATE, and SELECT functions with basic formatting from your inputs.
The code is simple, and I’ve left out any styling or flair so could be adapted easily to your own style of code. Alternatively, you can use my form below to generate what you need for PHP using PDO to work with a MySQL database, and just alter or remove code that doesn’t work for you.
Want to try the Code Generator? I’ve hosted it, so you can try it live: PHP PDO Code Generator
Code note: Uncomment the “pre” tags in the code to make it work!
bindparam(":' . $column .'", $' . $column . '); ' . "\n";
}
//Create static string variables for text with reserved characters
$ds_str = "$"; //Dollar sign for use in the result
//-------------------------------------------------------------------------------------Display Insert Function
$html = <<Insert Function for Table {$table_name}
public function insert{$uc_table_name}({$var_list})
{
global {$ds_str}db;
try
{
{$ds_str}stmt = {$ds_str}this->db->prepare("INSERT INTO {$table_name}({$col_list}) VALUES({$param_list})");
{$bind_list}
{$ds_str}stmt->execute();
{$ds_str}{$table_name}_id = {$ds_str}this->db->lastInsertId();
return {$ds_str}{$table_name}_id;
}
catch(PDOException {$ds_str}e)
{
echo {$ds_str}e->getMessage();
}
}
EOD;
//-------------------------------------------------------------------------------------Display Update Function
$html .= <<Update Function for Table {$table_name}
public function update{$uc_table_name}({$ds_str}id,{$var_list})
{
global {$ds_str}db;
try
{
{$ds_str}stmt = {$ds_str}this->db->prepare("UPDATE {$table_name} SET {$update_list} WHERE `{$id_column}`=:{$id_column}");
{$bind_list}
{$ds_str}stmt->bindparam(":{$id_column}", {$ds_str}_id);
{$ds_str}stmt->execute();
{$ds_str}{$table_name}_id = {$ds_str}this->db->lastInsertId();
return {$ds_str}{$table_name}_id;
}
catch(PDOException {$ds_str}e)
{
echo {$ds_str}e->getMessage();
}
}
EOD;
//-------------------------------------------------------------------------------------Display Select Function
$html .= <<Select Function for Table {$table_name}
public function get{$uc_table_name}({$ds_str}id)
{
global {$ds_str}db;
try
{
{$ds_str}stmt = {$ds_str}this->db->prepare("SELECT `{$id_column}`,{$col_list} FROM {$table_name} WHERE `{$id_column}`=:{$id_column} LIMIT 1");
{$ds_str}stmt->bindparam(":{$id_column}", {$ds_str}_id);
{$ds_str}stmt->execute();
{$ds_str}count = {$ds_str}stmt->rowCount();
if({$ds_str}count>0){
{$ds_str}rows = {$ds_str}stmt->fetchAll(PDO::FETCH_ASSOC);
return {$ds_str}rows[0];
}else return false;
}
catch(PDOException {$ds_str}e)
{
echo {$ds_str}e->getMessage();
}
}
EOD;
echo $html;
}else{?>
MYSQL Project Scripter
I hope this saves you time and helps you focus on the more fun elements of development!