Updating and Deleting data from a MySQL database using PHP
In my previous article, i explained how to create and insert data into MySQL using php, you can check it out here!. Today, i'm going to show you how to read, update and delete data stored in this same db.
Reading Data
First, We create a database.php file. This file would set up the connection parameters required to connect us to our db.<?php
//database.php
//setting connection parameters
CONST DBHOST = "localhost";
CONST DBUSER = "root";
CONST PASS = "";
CONST DBNAME = "learning_insert";
//setting up connection
$conn = mysqli_connect(DBHOST, DBUSER, PASS, DBNAME);
//echo error message if connection fails
if(mysqli_connect_errno()){
die('Failed to connect to db'.mysqli_connect_error().'('.mysqli_connect_errno().')');
}else {
echo "success";
}
?>
Next, let us create a users.php file that displays all our users' data. Let's add the php codes below to that file
<?php
//users.php
require "database.php"; //this connects us to our db through database.php
$sql = "SELECT * FROM `user_info`";
$res = $conn->query($sql);
$users = $res->fetch_all(MYSQLI_ASSOC);
print_r ($users);
?>
When you open the page on the browser, you'd see a nice looking array of all our users and their data.
We can beautify this array using some html <li> tags and a little php.
<?php
//user.php
require "database.php"; //this connects us to our db through database.php
$sql = "SELECT * FROM `user_info`"; //select everything in the table called user_info
$res = $conn->query($sql); //perform the query
$users = $res->fetch_all(MYSQLI_ASSOC);
//print_r ($users); commented out so the array of users won't show
?>
<!DOCTYPE html>
<html>
<head>
<meta charset="utf-8">
<title>Our users</title>
</head>
<body>
<h1>This is our list of users</h1>
<ul>
<?php
foreach ($users as $user) {
$userf = $user['firstname'];
$userl = $user['lastname'];
echo "<li>".$userf." ".$userl."</li>";
}
?>
</ul>
</body>
</html>
$user['firstname'] = timo
$user['lastname'] = onyuike
$user['phonenumber'] = 9876543
Next we create two variables $userf and $userl and assign $user['firstname'] and $user['lastname'] respectively to these variables. It is these variables that would be displayed on the users page.
Updating Data
Let's say we want to change a certain user's name from "timo" to "timoty", This is quite a trivial process. Let's get started!
Create an update.php file and require database.php and create a $new_name variable as shown below:
?php
//update.php
require "database.php";
$new_name = "Timothy";
?>
Next, we add the sql query required to update the data. The syntax is usually like this:
UPDATE table_name SET parameters_to_change = new_value WHERE conditions_to_identify_targets.
Note that parameters_to_change and conditions_to_identify_targets can be more than one.
Now, we add a query to our db file
<?php
require "database.php";
$new_name = "Timothy";
$sql = "UPDATE `user_info` SET firstname = '{$new_name}' WHERE firstname = 'timo' AND lastname = 'onyuike'";
//query the db
if($update = mysqli_query($conn, $sql)) {
//if succesful
echo "profile updated successfully";
header("location:users.php"); //redirect to users.php
}else{
echo mysqli_error($conn);
}
?>
Deleting Data
Finally, let's delete some data from our db. Let's say we want to delete "Janet Grumpy" from our db, we'd create a delete.php file and add the following codes to it.
<?php
require 'database.php';
$sql = "DELETE FROM `user_info` WHERE firstname = 'Janet' AND lastname = 'Grumpy'";
if($update = $conn ->query($sql)){
header("location: users.php ");
}else{
echo "Deleting failed";
}
?>
We have successfully read, updated and deleted data in a MySQL database using PHP.
No comments:
Post a Comment