30 Aug 2014

Day 11: Data Manipulation With PHP

Posted in MySQL, PHP, PHP step by step tutorials By bestonetechnologies On August 30, 2014

day11(2)
day11

We have done database connectivity in Day 9, now we will manipulate data in database (i.e. insert, edit, update and delete) with PHP.

To manipulate the data I have created more than 1 PHP files, So take a separate folder (I am taking Day11) to put all those below mentioned files.

Note : In this assignment, I am taking the same database (mydb) and table (tbemp) that we was created in Day9.

Program 38: Complete data manipulation with PHP.

File 1. connect.php : Used to define connection and select database.

We will include this file where database connection will required, so after this we don’t have to write connection code again and again, and also we can easily change our server and database (if required) from one file only.

<?php
$con = mysql_connect(“localhost”,”root”,””);
mysql_select_db(“mydb”,$con);
?>

File 2. menu.php : Used for navigation.

<a href=”insert.php”>Add</a> |
<a href=”display.php”>Display</a>
<hr>

File 3. insert.php : Used to save the records in database.

<?php
require_once “connect.php”;
$msg = “”;
if(isset($_REQUEST[‘btnSubmit’])) {
$name = $_REQUEST[‘txtName’];
$salary = $_REQUEST[‘txtSalary’];
$city = $_REQUEST[‘txtCity’];
$query = “insert into tbemp (name,salary,city) values (‘$name’,’$salary’,’$city’)”;
if(mysql_query($query)){
$msg = “Record Saved!”;
} else {
$msg = “Unable to Save!”;
}
}
?>
<html>
<?php include “menu.php”; ?>
<body>
<form method=”POST”>
Name : <input type=”text” name=”txtName”/> <br/>
Salary : <input type=”text” name=”txtSalary”/> <br/>
City : <input type=”text” name=”txtCity”/> <br/>
<input type=”submit” name=”btnSubmit” value=”Save”/> <br/>
<?php echo $msg; ?>
</form>
</body>
</html>

File 4. display.php : Used to display the records from database.

Note : Query string is used to send “id” to file “edit.php” and “delete.php” to edit and delete the records. In query string we can send only string values to the next page.

<?php
require_once “connect.php”;
$query = “select * from tbemp”;
$data = mysql_query($query);
?>
<html>
<?php include “menu.php”; ?>
<body>
<table border=”1″ cellpadding=”5″>
<tr>
<th>Name</th> <th>Salary</th> <th>City</th> <th colspan=”2″>Action</th>
</tr>
<?php while($rec = mysql_fetch_array($data)) { ?>
<tr>
<td> <?php echo $rec[‘name’]; ?> </td>
<td> <?php echo $rec[‘salary’]; ?> </td>
<td> <?php echo $rec[‘city’]; ?> </td>
<td> <a href=”edit.php?id=<?php echo $rec[‘id’]; ?>”>edit</a> </td>
<td> <a onClick=”return confirm(‘Sure to delete!’)” href=”delete.php?id=<?php echo $rec[‘id’]; ?>”>delete</a> </td>
</tr>
<?php } ?>
</table>
</body>
</html>

File 5. edit.php : Used to edit and update the records.

<?php
require_once “connect.php”;
$msg = “”;
$id = isset($_REQUEST[‘id’]) ? $_REQUEST[‘id’] : “0”;
if(isset($_REQUEST[‘btnSubmit’])) {
$name = $_REQUEST[‘txtName’];
$salary = $_REQUEST[‘txtSalary’];
$city = $_REQUEST[‘txtCity’];
$query1 = “update tbemp set name=’$name’,salary=’$salary’,city=’$city’ where id = “.$id;
if(mysql_query($query1)){
$msg = “Record Updated!”;
} else {
$msg = “Unable to Update!”;
}
}
$query = “select * from tbemp where id=”.$id;
$data = mysql_query($query);
$rec = mysql_fetch_array($data);
?>
<html>
<body>
<form method=”POST”>
Name : <input type=”text” value=”<?php echo $rec[‘name’]; ?>” name=”txtName”/> <br/>
Salary : <input type=”text” value=”<?php echo $rec[‘salary’]; ?>” name=”txtSalary”/> <br/>
City : <input type=”text” value=”<?php echo $rec[‘city’]; ?>” name=”txtCity”/> <br/>
<input type=”submit” name=”btnSubmit” value=”Update”/> <br/>
<?php echo $msg; ?>
</form>
</body>
</html>

File 5. delete.php : Used to delete the records.

<?php
require_once “connect.php”;
$msg = “”;
$id = isset($_REQUEST[‘id’]) ? $_REQUEST[‘id’] : “0”;
$query = “delete from tbemp where id=”.$id;
if(mysql_query($query)) {
header(“location:display.php”);
} else {
echo “unable to delete!”;
}
?>

❮ Previous

Next ❯