TcTalk Beginners PHP Tutorial – adding/editing/deleting/listing data from MySQL

You are here: Home / PHP Talk / TcTalk Beginners PHP Tutorial – adding/editing/deleting/listing data from MySQL

TcTalk Beginners PHP Tutorial – adding/editing/deleting/listing data from MySQL

Posted on Oct 21, 2010 | Leave a Comment

In this tutorial we’ll learn some basic operations of PHP with MySQL. In this tutorial we assume that you have basic understanding and working knowledge of PHP. All you need is to setup the PHP environment with MySQL enabled in your development machine. You’ll find lots of tutorial on internet on how to configure PHP-MySQL in Linux or Windows platform.

We have an actor table which stores actor data like firstname, lastname and career start date. Each actor row is identified with the actor id which is primary key. This primary key is also auto incremented so that each time a row is inserted it increases the actor id to make it unique. Below is the SQL to create the table in your MySQL. You can either use phpMyAdmin or MySQL console to execute the sql to create the table. I’ve named the database as ‘testdb’ which has the ‘actors’ table.

CREATE TABLE `actors` (
  `actorid` int(4) NOT NULL AUTO_INCREMENT,
  `firstname` varchar(255) collate latin1_general_ci NOT NULL,
  `lastname` varchar(255) collate latin1_general_ci NOT NULL,
  `careerstart` date DEFAULT NULL,
  PRIMARY KEY  (`actorid`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci;
 
--
-- Dumping data for table `actors`
--
 
INSERT INTO `actors` (`actorid`, `firstname`, `lastname`, `careerstart`) VALUES
(1, 'Arun', 'Das', '1995-06-06'),
(2, 'Barun', 'Dutta', '2000-04-04'),
(3, 'Robert', 'Nero', '1987-08-11'),
(4, 'Juliya', 'Roberts', '1988-12-05'),
(5, 'Peter', 'Patel', '1980-07-02');

Listing All Actors from database-

listactor.php is the file which lists all actors from database. This script selects all data from actors table and lists them in tabular format. In operation column it shows Edit and Delete link so that a row data can either be edited or deleted from table. In the Edit link it shows link to editactor.php to which actor id id passed. In the delete link a javascript is shown which will be described later. In the below section a link to add an actor is shown which links to addactor.php page.

<HTML>
<HEAD><TITLE>Listing all actors</TITLE></HEAD>
<SCRIPT>
function confirmDel(actorid) {
  if(confirm('Are you sure you want to delete?')){
     window.location.href='delactor.php?actorId='+actorid;
  }
}
</SCRIPT>
<BODY>
<TABLE style="border:1px solid #000000">
<TR><TH>Actor Id</TH><TH>Name</TH><TH>Career Start</TH><TH>Operation</TH></TR>
<?
/*************** Lists all actors after querying the database ***************/
include "dbinfo.php";
 
//select all the actors from database and list here. Show all actors with Edit/Delete link beside.
//Clicking Edit will open the editactor.php. Add link will add actor
$sqlList = "SELECT * FROM `actors` ORDER BY `actorid`";
$resList = mysql_query($sqlList) or die("Error in listing actors...".mysql_error());
 
while($rowList = mysql_fetch_array($resList))
{
  $actorId = $rowList[actorid];
  $actorFname = $rowList[firstname];
  $actorLname = $rowList[lastname];
  $actorCareerDt = $rowList[careerstart];
 
  echo '<TR><TD align=middle>'.$actorId.'</TD>
            <TD>'.$actorFname.' '.$actorLname.'</TD>
            <TD>'.$actorCareerDt.'</TD>
            <TD align=middle><A HREF="editactor.php?actorId='.$actorId.'">Edit</A> | <A HREF="javascript: confirmDel('.$actorId.')">Delete</A></TD></TR>';
}
?>
<TR><TD COLSPAN=4>&nbsp;</TD></TR>
<TR><TD COLSPAN=4 align=middle><A HREF="addactor.php">Add Actor</A></TD></TR>
<TR><TD COLSPAN=4>&nbsp;</TD></TR>
</TABLE>
</BODY>
</HTML>

Adding an Actor in table-

addactor.php script is invoked when user clicks the `Add Actor` link. It shows the fields to take input of firstname, lastname, career start date etc. After filling up the information when user hits submit button the same script is invoked because we’ve put the addactor.php in the action attribute of the tag. We check if $_REQUEST[add] is populated or not. When user hits submit the submit button with name ‘add’ is also posted. So we check for this variable value and based on that we took the decision that the form has been submitted. Remember when a form is submitted all the form fields are populated in the $_REQUEST[] array. If all the fields are filled up by the user then we insert the data in database and redirects the control to listing page (listactor.php).

<?
/*************** Add an actor into the database ***************/
include "dbinfo.php";
$err= 0; // Variable flag to check for error
if($_REQUEST[add])
{
  if($_REQUEST[fname] !="" && $_REQUEST[lname] !="" && $_REQUEST[cdate] !="") {
      $sqlAdd = "INSERT INTO actors(actorid,firstname,lastname,careerstart) VALUES('','$_REQUEST[fname]','$_REQUEST[lname]','$_REQUEST[cdate]')";
      mysql_query($sqlAdd) or die("Error in adding actor...".mysql_error());
      header("Location: listactor.php");
  }else {
    $err= 1;
  }
}
?>
<HTML>
<HEAD><TITLE>Add New Actor</TITLE></HEAD>
<BODY>
<TABLE style="border:1px solid #000000">
<FORM NAME=f1 ACTION="addactor.php">
<?
   if($err==1) echo '<TR><TD colspan=2><FONT COLOR=RED>Either of the below field is missing data...</FONT></TD></TR>';
?>
<TR><TD>First Name: </TD><TD><INPUT TYPE=TEXT NAME=fname style="border:1px solid #000000" /></TD></TR>
<TR><TD>Last Name: </TD><TD><INPUT TYPE=TEXT NAME=lname style="border:1px solid #000000" /></TD></TR>
<TR><TD>Career Start Date: </TD><TD><INPUT TYPE=TEXT NAME=cdate style="border:1px solid #000000" /> (YYYY-MM-DD)</TD></TR>
<TR><TD colspan=2><INPUT TYPE=SUBMIT NAME=add style="border:1px solid #000000" VALUE="Add Actor"/></TD></TR>
</FORM>
</TABLE>
</BODY>
</HTML>

Editing an Actor-

For edit we follow the same approach like the Add actor phase. However we pass the actor id to the script when it is invoked. In the start of the script we fetch the actor details from database based on the actor id and populate the fields on the edit page. We use a hidden variable to hold the value of the actor id of which we have fetched the details from database. Once user does some modification and hits submit we update the database with the updated data. Here to update the specific actor we use the actor id posted through the hidden variable.

<?
/*************** Edit an actor stored in the database ***************/
include "dbinfo.php";
//select all the actors from database and list here. Show all actors with Edit/Delete link beside.
//Clicking Edit will open the editactor.php. Add link will add actor
$sqlList = "SELECT * FROM `actors` WHERE `actorid`='$_REQUEST[actorId]'";
$resList = mysql_query($sqlList) or die("Error in listing actors...".mysql_error());
 
while($rowList = mysql_fetch_array($resList))
{
  $actorId = $rowList[actorid];
  $actorFname = $rowList[firstname];
  $actorLname = $rowList[lastname];
  $actorCareerDt = $rowList[careerstart];
}
 
if($_REQUEST[edit])
{
  $sqlEdit = "UPDATE actors SET `firstname`='$_REQUEST[fname]',
                                `lastname`='$_REQUEST[lname]',
                                `careerstart`='$_REQUEST[cdate]' 
                                WHERE `actorid`='$_REQUEST[actorId]'";
  mysql_query($sqlEdit) or die("Error in adding actor...".mysql_error());
  header("Location: listactor.php");
}
?>
<HTML>
<HEAD><TITLE>Add New Actor</TITLE></HEAD>
<BODY>
<TABLE style="border:1px solid #000000">
<FORM NAME=f1 ACTION="editactor.php">
<INPUT TYPE=HIDDEN NAME=actorId VALUE="<?=$actorId?>" />
<TR><TD>First Name: </TD><TD><INPUT TYPE=TEXT NAME=fname VALUE="<?=$actorFname?>" style="border:1px solid #000000" /></TD></TR>
<TR><TD>Last Name: </TD><TD><INPUT TYPE=TEXT NAME=lname VALUE="<?=$actorLname?>" style="border:1px solid #000000" /></TD></TR>
<TR><TD>Career Start Date: </TD><TD><INPUT TYPE=TEXT NAME=cdate VALUE="<?=$actorCareerDt?>" style="border:1px solid #000000" /> (YYYY-MM-DD)</TD></TR>
<TR><TD colspan=2><INPUT TYPE=SUBMIT NAME=edit style="border:1px solid #000000" VALUE="Edit Actor"/></TD></TR>
</FORM>
</TABLE>
</BODY>
</HTML>

Delete Actor-

Delete is pretty straight forward. We use the javascript function confirmDel(actorid) to ensure user really wants to delete the actor. If user confirms that then we pass the actorid to the delactor.php script which deletes this actor from database.

<?
/*************** Delete an actor from the database ***************/
include "dbinfo.php";
//select all the actors from database and list here. Show all actors with Edit/Delete link beside.
//Clicking Edit will open the editactor.php. Add link will add actor
$sqlDel = "DELETE FROM `actors` WHERE `actorid`='$_REQUEST[actorId]'";
$resDel = mysql_query($sqlDel) or die("Error in deleting actor...".mysql_error());
header("Location: listactor.php");
?>

Tutorial on how to store images in Mysql BLOB field [caption id="attachment_78" align="alignleft" width="300" caption="mysql"][/caption] Sometimes in our web application...
How to generate drop down of US states with PHP MySQL In this article I’ll show you how to create a...
Useful List of PHP MySQL Tutorial and Resources PHP is emerging as a leader in the server side...