Google Ads

Easy and Simple Edit/Update MySQL Table PHP Script


Easy and Simple Edit/Update MySQL Table PHP Script

This tutorial will show and give you knowledge on how to edit/update MySQL table using PHP. Is this tutorial, I’m gonna show you how to edit using 3 methods namely MySQLi Object-oriented, MySQLi Procedural and PDO.
Creating our Database

First, we’re going to create a database that contains our data.
1. Open phpMyAdmin.
2. Click databases, create a database and name it as “edit”.
3. After creating a database, click the SQL and paste the below code. See image below for detailed instruction.

CREATE TABLE `user` (
`userid` INT(11) NOT NULL AUTO_INCREMENT,
`firstname` VARCHAR(30) NOT NULL,
`lastname` VARCHAR(30) NOT NULL,
`edit_via` VARCHAR(60) NOT NULL,
PRIMARY KEY (`userid`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

 

 

Inserting Data into our Table

Next step is to insert data into our table. This will serve as our reference in editing our rows.
1. Click our database “edit”.
2. Click SQL and paste the below code.

 

INSERT INTO `user` (`firstname`, `lastname`) VALUES
('neovic', 'devierte'),
('lee', 'ann');

 

Creating Our Table

Next is to create our table. This table will give us information about the data in our database. We name this table as “index.php”. To create the table, open your HTML code editor and paste the code below after the tag.

 

<!DOCTYPE html>
<html>
<head>
<title>Editing MySQL Data</title>
</head>
<body>
<h2>User Table</h2>
<div>
<table border="1">
<thead>
<th>UserID</th>
<th>Firstname</th>
<th>Lastname</th>
<th>Updated via</th>
<th></th>
</thead>
<tbody>
<?php
$con = mysqli_connect("localhost","root","","edit");

// Check connection
if (mysqli_connect_errno())
{
echo "Failed to connect to MySQL: " . mysqli_connect_error();
}

$query=mysqli_query($con,"select * from `user`");
while($row=mysqli_fetch_array($query)){
?>
<tr>
<td><?php echo $row['userid']; ?></td>
<td><?php echo $row['firstname']; ?></td>
<td><?php echo $row['lastname']; ?></td>
<td><?php echo $row['edit_via']; ?></td>
<td><a href="edit.php?id=<?php echo $row['userid']; ?>">Edit</a></td>
</tr>
<?php
}

?>
</tbody>
</table>
</div>
</body>
</html>

 

Creating our Edit Form and Script

Last step is to create our edit form and our edit script. This will update our selected row in our MySQL Table using the 3 methods. We name this page as “edit.php”. To create the page, open your HTML code editor and paste the code below after the tag.

 

<?php
$id=$_GET['id'];
?>
<!DOCTYPE html>
<html>
<head>
<title>Editing MySQL Data</title>
</head>
<body>
<h2>Edit Form</h2>
<?php
$conn = mysqli_connect("localhost","root","","edit");

// Check connection
if (mysqli_connect_errno())
{
echo "Failed to connect to MySQL: " . mysqli_connect_error();
}

$query=mysqli_query($conn,"select * from `user` where userid='$id'");
$row=mysqli_fetch_assoc($query);
?>
<form method="POST">
<label>Firstname: <input type="text" value="<?php echo $row['firstname']; ?>" name="firstname"></label>
<label>Lastname: <input type="text" value="<?php echo $row['lastname']; ?>" name="lastname"></label>
</br></br>
<input type="submit" value="MySQLi Object-oriented" name="mysqli_oop">
<input type="submit" value="MySQLi Procedural" name="mysqli_procedural">
<input type="submit" value="PDO" name="pdo">
</form>
<br>
<a href="index.php">Back</a>

<?php

if (isset($_POST['mysqli_oop'])){

$firstname=$_POST['firstname'];
$lastname=$_POST['lastname'];

// Create connection
$conn = new mysqli("localhost", "root", "", "edit");
// Check connection
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}

$sql = "UPDATE `user` SET firstname='$firstname', lastname='$lastname', edit_via='MySQLi Object-oriented' WHERE userid='$id'";

if ($conn->query($sql) === TRUE) {
echo "Record updated successfully";
} else {
echo "Error updating record: " . $conn->error;
}

$conn->close();
}
elseif (isset($_POST['mysqli_procedural'])){

$firstname=$_POST['firstname'];
$lastname=$_POST['lastname'];

// Create connection
$conn = mysqli_connect("localhost", "root", "", "edit");
// Check connection
if (!$conn) {
die("Connection failed: " . mysqli_connect_error());
}

$sql = "UPDATE `user` SET firstname='$firstname', lastname='$lastname', edit_via='MySQLi Procedural' WHERE userid='$id'";

if (mysqli_query($conn, $sql)) {
echo "Record updated successfully";
} else {
echo "Error updating record: " . mysqli_error($conn);
}

mysqli_close($conn);
}
elseif (isset($_POST['pdo'])){
$servername = "localhost";
$username = "root";
$password = "";
$dbname = "edit";

try {
$conn = new PDO("mysql:host=$servername;dbname=$dbname", $username, $password);
// set the PDO error mode to exception
$conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

$firstname=$_POST['firstname'];
$lastname=$_POST['lastname'];

$sql = "UPDATE `user` SET firstname='$firstname', lastname='$lastname', edit_via='PDO' WHERE userid='$id'";

// Prepare statement
$stmt = $conn->prepare($sql);

// execute the query
$stmt->execute();

// echo a message to say the UPDATE succeeded
echo $stmt->rowCount() . " records UPDATED successfully";
}
catch(PDOException $e)
{
echo $sql . "<br>" . $e->getMessage();
}

$conn = null;
}

?>
</body>
</html>

 

 


Related Post

Web Based Binary MLM System PHP Script Download Web Based Binary MLM SystemWeb Based Binary MLM SystemWe are provide a powerfull binary mlm script for you, we develop this application using the late...
CodeIgniter and jQuery/Ajax – CRUD Select, I... CodeIgniter and jQuery/Ajax - CRUD Select, Insert, Update, and Delete Record PHP Script How to create application with CodeIginter 3 and jQuery/Aja...
Human Resource Support System PHP Script Human Resource Support System PHP Script This is a full system for human resource support system. It is an easy to use system. The following are th...
MegaNavbar (v 2.2.0). Advanced Mega Menu for Boots... MegaNavbar (v 2.2.0). Advanced Mega Menu for Bootstrap 3.0+MegaNavbar (v 2.2.0). Advanced Mega Menu for Bootstrap 3.0+MegaNavbar is pure HTML5/CSS3 na...
Selecting Rows from MySQL Table using checkbox in ... Selecting Rows from MySQL Table using checkbox in PHP/MySQL PHP Script In this tutorial, I'm going to show you how to select rows from MySQL Table ...

Have any Question or Comment?

Leave a Reply

Your email address will not be published. Required fields are marked *