View Cetain records, Edit And Delete Records Help

[cnagra]

i have created my database, and can add to it using php, and also view all data in html.

can someone please help me with viewing jus one record, by inputting the id of that record, and editing that record and deleteing that record. i found one tutorial but dont understand it.

thanks

 

[jpadie]

try this code for a complete CRUD system. note that it contains some pretty dodgy coding practices (notably in the db write bits) but it should give you a semantic overview anyway. note also that there are some variables you need to set at the start of the code to make it work.

<?
$table = ""; //table name
$host = ""; //hostname
$user =""; //db username
$pwd=""; //db password
$db = ""; //db name

if (isset($_GET['id'])):
  $id = mysql_escape_string(trim($_GET['id']));
else:
  $id=NULL;
endif;

if (isset($_GET['action'])):
  $action = $_GET['action'];
else:
  $action = "list";
endif;


$self = $_SERVER['PHP_SELF'];

mysql_connect($host, $user, $pwd) or die (mysql_error());
mysql_select_db($db) or die (mysql_error());

switch ($action):
	case "view":
		if (!is_null($id)):
			$result = mysql_query("Select * from $table where id ='$id'") or die(mysql_error());
			$row = mysql_fetch_assoc($result);
			echo "<table>";
			foreach ($row as $fieldname=>$value):
			  echo "<tr><td>$fieldname</td><td>$value</td></tr>";
			endforeach;
			echo "</table><br/><a href=\"$self?action=list\">Back to List</a>";
		endif;
	break;
	
	case "delete":
		if (!is_null($id)):
			$result = mysql_query("Delete from $table where id='$id'");
			if ($result):
			  echo "Record deleted ok <br/><a href=\"$self?action=list\">Back to List</a>";
			else:
			  echo "Problem deleting record. ".mysql_error();
			endif;
		endif;
	break;
	
	case "edit":
		if (isset($_POST['submit'])):
			$query="";
			foreach ($_POST as $fieldname => $val):
				$val = mysql_escape_string(trim($val));
				if ($fieldname !== "submit"):
 					$query .= "`$fieldname` = '$val',";
				endif;
			endforeach;
			$query = "replace into $table set " . rtrim($query, ",");
			$result = mysql_query ($query);
			if ($result):
				echo "Data saved to the database";
				echo "<br/><a href=\"$self?action=list\">Back to List</a>";
			else:
				echo "Data NOT saved to the database " . mysql_error() ." query was $query";
				echo "<br/><a href=\"$self?action=list\">Back to List</a>";
			endif;
			
		else:
			if (!is_null($id)):
				$result = mysql_query("Select * from $table where id ='$id'") or die(mysql_error());
				$row = mysql_fetch_assoc($result);
				echo "<form method=\"post\" action=\"$self?action=edit\"><table>";
				foreach ($row as $fieldname=>$value):
				  echo "<tr><td>$fieldname</td><td><input type=\"text\" name=\"$fieldname\" value=\"$value\" /></td></tr>";
				endforeach;
				echo "<tr><td></td><td><input type=\"submit\" name=\"submit\" value=\"save\" /></td></tr>";
				echo "</table></form><br/><a href=\"$self?action=list\">Back to List</a>";
			endif;
		endif;
	break;
	
	case "new":
		if (isset($_POST['submit'])):
			$query="";
			foreach ($_POST as $fieldname => $val):
				$val = mysql_escape_string(trim($val));
				if ($fieldname === "id") $val=NULL; 
				if ($fieldname !== "submit") :
					$query .= "`$fieldname` = '$val',";
				endif;
			endforeach;
			$query = "Insert into $table set " . rtrim($query, ",");
			$result = mysql_query ($query);
			if ($result):
				echo "Data saved to the database";
				echo "<br/><a href=\"$self?action=list\">Back to List</a>";
			else:
				echo "Data NOT saved to the database " . mysql_error();
				echo "<br/><a href=\"$self?action=list\">Back to List</a>";
			endif;
			
		else:
			$result = mysql_query("Select * from $table") or die(mysql_error());
			$row = mysql_fetch_assoc($result);
			echo "<form method=\"post\" action=\"$self?action=new\"><table>";
			foreach ($row as $fieldname=>$value):
			  echo "<tr><td>$fieldname</td><td><input type=\"text\" name=\"$fieldname\" value=\"\" /></td></tr>";
			endforeach;
			echo "<tr><td></td><td><input type=\"submit\" name=\"submit\" value=\"save\" /></td></tr>";
			echo "</table></form><br/><a href=\"$self?action=list\">Back to List</a>";
		endif;
	break;
	
	case "list":
		$result = mysql_query("select * from $table");
		echo "<a href=\"$self?action=new\">New Record</a>";
		echo "<table>";
		while ($row = mysql_fetch_assoc($result)):
			$buttons = "<td><a href=\"{$self}?id={$row['id']}&action=delete\">Delete</a></td><td><a href=\"{$self}?id={$row['id']}&action=view\">View</a></td><td><a href=\"{$self}?id={$row['id']}&action=edit\">Edit</a></td></tr>";
			if ($cnt === 1):
				echo "<tr>";
				foreach ($row as $fieldname=>$value):
					echo "<th>$fieldname</th>";
					$content .= "<td>$value</td>";
				endforeach;
				echo "<th colspan='3'>&nbsp;</th></tr>";  
				$cnt =0;
			else:
				$content = "";
				foreach ($row as $fieldname=>$value):
					$content .= "<td>$value</td>";
				endforeach;
			endif;
			echo "<tr>$content $buttons</tr>";
		endwhile;
		echo "</table><br/><a href=\"$self?action=list\">Back to List</a>";
	break;
	default:
	 //do nothing
endswitch;
?>