Dynamic CMS - Is This Possible? 3

[EvilAsh]

I have developed a website based on 10 tables in a MYSQL database & I wish to create a CMS that allows the customer to add/edit/remove records.

I have created pages in PHP that achieve this before but it is a long laborious process to churn out 3 pages per table (this site would require 30 such pages) and I am looking to simplify the process.

Here is the concept:

What I would like to build is a CMS based on five template pages Menu, Table, Add, Edit, Delete. Each page would populate dynamically according to the table they are associated with.

Menu page would list all of the tables and each would link to a table page template. The link would carry the table name as a variable ($table). This one is easy enough.

The Table page would list the contents of the table selected, each row linking to the Edit and to the Delete page, with a link to the Add page.

The subsequent pages would need to display a form containing a textfield for each table field where the table =$table (the Edit link would also carry the variable $id).

For the Add page the form would need to extract the data entered in the textfields and put them into an insert query.

For the Edit page the form would need to extract the data entered in the textfields and put them into an update query.

The delete page would be a simple delete where id = $id situation (all table rows have a unique id).

My dilemma surrounds:

- The Insert/Update queries – how would I structure this to accept varying values and varying destination fields? Do I convert the values into arrays?

I am sure other questions may arise but should be grateful for a nudge in the right direction. Thanks.

(PS I have considered an off the shelf CMS solution but am anxious to learn.)

 

[jpadie]

what you are looking for is not so much CMS but a CRUD system (Create, Retrieve, Update, Delete) from first glance.

below is a very simplistic CRUD that i knocked up to show you how these things work. do not use this in a non-trusted environment as there is no validation on the data inputs. to stengthen this i would at least:

1. add a mechanism to stop forms from being resubmitted and the data being overwritten through inadvertent refreshes etc.
2. store the primary key data in a session key rather than a form field.
3. create the sql query for the save functions by looking up the table definition (see the add_new method) and building the query from the column names rather than the form field names
4. generally add lots of data validation

think also about using something well supported like PEAR's data objects.

the code below only works when there is a primary key defined on the table and it is an autoincrement. if you use any other form of primary key (say a random number generator) you would need to make appropriate changes in the add_new function.

of course, the best CRUD out there is phpmyadmin!

anyway, for what it's worth (remember to insert your own details in the mysql_connect and mysql_select_db statements)

<?
session_name("CRUD_system");
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']}  WHERE key_name = 'PRIMARY'") or die(mysql_error());
			$_SESSION['curtable_primarykey'] = mysql_result($result,0,4);
		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>&nbsp;</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>&nbsp;</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>";
		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>&nbsp;</td>\r\n \t<td>&nbsp;</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=".$row[$_SESSION['curtable_primarykey']]."\">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=".$row[$_SESSION['curtable_primarykey']]."\">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=".$row[$_SESSION['curtable_primarykey']]."\">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=".$row[$_SESSION['curtable_primarykey']]."\">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;
}

 

[EvilAsh]

Fantastic! Thank you. That is exactly what I am looking for.

Without wishing to bite the hand that feeds me, the script does throw a slight bug when using it. When selecting a table I get the message:

You have an error in your SQL syntax near 'WHERE key_name = 'PRIMARY'' at line 1

how is key_name defined?

 

[jpadie]

it's a defined column in the recordset passed back by mysql in the show index syntax.

to debug:

can you replace this line

$result = mysql_query("SHOW INDEX FROM {$_SESSION['curtable']}  WHERE key_name = 'PRIMARY'") or die(mysql_error());

with this

$query = "SHOW INDEX FROM {$_SESSION['curtable']}  WHERE key_name = 'PRIMARY'";
echo "query is $query";
$result = mysql_query($query) or die(mysql_error());

and can you let me know which version of mysql you are using?

 

[EvilAsh]

Hi there. Thanks for coming back so quickly.
The message now reads:

query is SHOW INDEX FROM news WHERE key_name = 'PRIMARY'You have an error in your SQL syntax near 'WHERE key_name = 'PRIMARY'' at line 1

The version of mysql is 3.23.47.

 

[jpadie]

i found adding a criterion (where clause) to the show index syntax in mysql 5.1 worked but was undocumented. clearly it does not work in mysql 3.

here is replacement code with a workaround. i've also fixed another couple of typos i found.

<?
session_start();
$GLOBALS['msg'] = "";
@mysql_connect ("localhost","root","root") or die (mysql_error());
@mysql_select_db("adieandco") 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>&nbsp;</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>&nbsp;</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>&nbsp;</td>\r\n \t<td>&nbsp;</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>";
}

 

[EvilAsh]

Fantastic - it works perfectly! Thanks ever so much. I am really grateful.

I am just having a dabble with the code now and willlet you know how I get on.

 

[jpadie]

no worries.

 

[EvilAsh]

Pushing my luck here!!!

How might I change the textfields for the record edit to textarea fields?

I have tried changing the code on line 145

<input $readonly type="$type" name="$key" value="$val" />

to

<textarea $readonly type="$type" name="$key" value="$val"></textarea>

But it then fails to populate the fields with the record data. Am I editing the wrong bit or is my Form protocol rusty!!

Thanks.

 

[jpadie]

it would do.

the html syntax for a text input is like this

<input name="textbox" type="text" value="somevalue" />

the html syntax for a textarea is like this

<textarea name="textbox">
somevalue
</textarea>

note that the value goes between the tags for a textarea whereas it is defined inline for a textbox. a text box does not support the type attribute either.

if you want to change all textboxes to textareas the code would be

<textarea $readonly name="$key">$val</textarea>

and

<td><textarea $readonly name="{$field['Field']}" >$default </textarea></td>

you should think about adding a rows attribute to the text area too.

if you want to make things more granular you will need to invoke a switch board of form elements and decide which you want either based on a separate database table of db->formfield matches or based on the column type of each table.

as i said in the first post - when you start involving this kind of functionality you should start looking at prebuilt packages. particularly have a look in the PEAR repository. There is a heavyweight package called DB_DataObject and bind it with FormBuilder (which in turn uses HTML_Quickform and HTML_Common2).