How To Insert Update Delete And Search Data From MySQL Database In PHP Using PDO
In this Php Tutorial we will see How To Add Edit Remove Find Data From MySQL Database Table In Php Using PDO .
I Use In This Tutorial:
- NetBeans IDE .
- XAMPP .
- PhpMyAdmin .
-MySQL Database .
*INFO : Learn Php And Build Cms Project (Course)
Part 1
Part 2
Part 3
Php Source Code:
<?php
$dsn = 'mysql:host=localhost;dbname=test_db';
$username = 'root';
$password = '';
try{
// Connect To MySQL Database
$con = new PDO($dsn,$username,$password);
$con->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
} catch (Exception $ex) {
echo 'Not Connected '.$ex->getMessage();
}
$id = '';
$fname = '';
$lname = '';
$age = '';
function getPosts()
{
$posts = array();
$posts[0] = $_POST['id'];
$posts[1] = $_POST['fname'];
$posts[2] = $_POST['lname'];
$posts[3] = $_POST['age'];
return $posts;
}
//Search And Display Data
if(isset($_POST['search']))
{
$data = getPosts();
if(empty($data[0]))
{
echo 'Enter The User Id To Search';
} else {
$searchStmt = $con->prepare('SELECT * FROM users WHERE id = :id');
$searchStmt->execute(array(
':id'=> $data[0]
));
if($searchStmt)
{
$user = $searchStmt->fetch();
if(empty($user))
{
echo 'No Data For This Id';
}
$id = $user[0];
$fname = $user[1];
$lname = $user[2];
$age = $user[3];
}
}
}
// Insert Data
if(isset($_POST['insert']))
{
$data = getPosts();
if(empty($data[1]) || empty($data[2]) || empty($data[3]))
{
echo 'Enter The User Data To Insert';
} else {
$insertStmt = $con->prepare('INSERT INTO users(fname,lname,age) VALUES(:fname,:lname,:age)');
$insertStmt->execute(array(
':fname'=> $data[1],
':lname'=> $data[2],
':age' => $data[3],
));
if($insertStmt)
{
echo 'Data Inserted';
}
}
}
//Update Data
if(isset($_POST['update']))
{
$data = getPosts();
if(empty($data[0]) || empty($data[1]) || empty($data[2]) || empty($data[3]))
{
echo 'Enter The User Data To Update';
} else {
$updateStmt = $con->prepare('UPDATE users SET fname = :fname, lname = :lname, age = :age WHERE id = :id');
$updateStmt->execute(array(
':id'=> $data[0],
':fname'=> $data[1],
':lname'=> $data[2],
':age' => $data[3],
));
if($updateStmt)
{
echo 'Data Updated';
}
}
}
// Delete Data
if(isset($_POST['delete']))
{
$data = getPosts();
if(empty($data[0]))
{
echo 'Enter The User ID To Delete';
} else {
$deleteStmt = $con->prepare('DELETE FROM users WHERE id = :id');
$deleteStmt->execute(array(
':id'=> $data[0]
));
if($deleteStmt)
{
echo 'User Deleted';
}
}
}
?>
<!DOCTYPE html>
<html>
<head>
<title>PHP (MySQL PDO): Insert, Update, Delete, Search</title>
</head>
<body>
<form action="php_mysql_insert_update_delete_search.php" method="POST">
<input type="text" name="id" placeholder="id" value="<?php echo $id;?>"><br><br>
<input type="text" name="fname" placeholder="First Name" value="<?php echo $fname;?>"><br><br>
<input type="text" name="lname" placeholder="Last Name" value="<?php echo $lname;?>"><br><br>
<input type="number" min="10" max="100" name="age" placeholder="Age" value="<?php echo $age;?>"><br><br>
<input type="submit" name="insert" value="Insert">
<input type="submit" name="update" value="Update">
<input type="submit" name="delete" value="Delete">
<input type="submit" name="search" value="Search">
</form>
</body>
</html>
OUTPUT:
 |
| Php MySQL PDO Insert Update Delete Search |