Implementation of CRUD

Implementation of four basic functions of persistent storage which is Create, Read, Update and Delete. I’ll be using a database named “wild” with a single table named “entry” having 3 columns named “id”,”title” and “content”. Shown below is the configuration.php wherein settings for database connection is established.

<?php
// FILENAME: configuration.php
$db_username="root";
$db_password="";
$db_hostname="localhost";
$db_name="wild"; /**************** Name of Database *************/
$dbconnection=mysql_connect($db_hostname,$db_username,$db_password) or die("Cannot connect to localhost.");
$dbselect=mysql_select_db($db_name,$dbconnection) or die ("Cannot connect to database.");
?>

this is the insertion part for the database. it was named as backend.php but it must be insert.php

Below is the snippet for HTML where users can enter the data they wish to be saved in the database. It is also called a “form”. Let’s name the file insert.php

<?php // FILENAME: insert.php ?>
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Untitled Document</title>
</head>
<body>
<form action="db-insert.php" method="post">
Title<br /><input type="text" name="title" id="title" /><br />
Content<br />
<textarea rows="15" cols="30" id="content" name="content">
</textarea>
<br /><input type="submit" value="Submit" />
</form>
<a href="view.php" style="text-decoration:none"><button>Cancel</button></a>
</body>
</html>

Once submit button is clicked on the insert.php, it will be redirected to db-insert.php

<?php
// FILENAME: db-insert.php

include('configuration.php');
$title=$_POST['title'];
$content=$_POST['content'];

$position=14; // Define how many character you want to display.
$post = substr($content, 0, $position);

// DISPLAY THE DATA TO BE SAVED, FOR SECURITY
echo $post;
echo "...";


// SQL QUERY PHRASE
$sql="insert into entry (title, content) values ('$title', '$content')";

// EXECUTE SQL QUERY ONCE CONNECTED TO DATABASE
// IF CANNOT CONNECT TO DATABASE OR SQL QUERY IS INCORRECT DISPLAY "Cannot perform query"
$sql2=mysql_query($sql,$dbconnection) or die ("Cannot perform query.");


// IF NO ERROR OCCURS
echo "<p>Successfully inserted item!</p>";
?>
<html>
<body>
<a href="view.php" style="text-decoration:none"><button>Back</button></a>
<a href="backend.php" style="text-decoration:none"><button>Add New</button></a>
</body>


</html>

The script below displays all the data inserted or saved in the database

<?php
// FILENAME: view.php


include('configuration.php');
$sql_query1="select * from entry";
$sql_query2=mysql_query($sql_query1,$dbconnection) or die ("Cannot perform query." );
$row="";


echo "<table border=\"1\" cellpadding=\"5\">";
while($row=mysql_fetch_array($sql_query2)){
$id1=$row['id'];
echo "<tr>";
/*************** (array) id is the unique id of entries in database */
/*************** where xx is the values to be passed on the edit or delete page */
$id="<td>".$row['id']."</td><td>".$row['title']."</td><td><a href=\"edit.php?xx=$id1\">Edit</a></td>"."</td><td><a href=\"delete.php?xx=$id1\">Delete</a></td>";
echo $id;
echo "</tr>";
}
echo "</table>";
?>
<html>
<body>
<a href="backend.php" style="text-decoration:none"><button>Add New</button></a>
</body>
</html>

edit.php allows user to edited selected data from the view.php
<?php
// FILENAME: edit.php


include('configuration.php');


$xx=$_GET['xx'];
echo $xx;


$sql_query1="select * from entry where id=$xx";
$sql_query2=mysql_query($sql_query1,$dbconnection) or die ('Cannot edit entry.');


while($row=mysql_fetch_array($sql_query2)){
$title=$row['title'];
$content=$row['content'];
}?>


<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Edit Page</title>
</head>
<body>
<?php
echo "<form action=\"update.php?aa=$xx\" method=\"post\">";
?>
Title<br /><input type="text" name="title" id="title" value="<?php echo $title; ?>"/><br />
Content<br />
<textarea rows="15" cols="30" id="content" name="content">
<?php echo $content; ?>
</textarea>
<br />
<input type="submit" value="Submit" />
<a href="view.php" style="text-decoration:none"><button>Back</button></a>
</form>
</body>
</html>

After clicking the Update button in edit.php,the page will be redirected to update.php

<?php
// FILENAME: update.php


include('configuration.php');


$aa=$_GET['aa'];
$title=$_POST['title'];
$content=$_POST['content'];


$sql_query1="update entry set title='$title', content='$content' where id='$aa'";
$sql_query2=mysql_query($sql_query1,$dbconnection) or die ('Cannot update entry');


echo "Successfully updated entry.<br>";
echo "<a href=\"view.php\">Back</a>";
?>

delete.php allows selected data from view.php to be deleted

<?php
// FILENAME: delete.php


include('configuration.php');


$xx=$_GET['xx'];


$sql_query1="delete from entry where id=$xx";
$sql_query2=mysql_query($sql_query1,$dbconnection) or die ('Cannot delete item.');


echo "Successfuly deleted item.<br> <a href=\"view.php\">Back</a>"


?>

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s