add/edit/delete with PHP and MySQL
In this tutorial we learn add, view, and update data Using PHP and MySQL. The programmers called it CRUD. CRUD means CREATE, READ, UPDATE and DELETE.
Now we will focus on how to create new database record with PHP and MySQL.
It is much better if you’ll create a separate file for connecting PHP to MySQL Database and just include it to any of your PHP files that needs it.
For example, I will create a file named “config.php” (Configuration and then opening of the database) in our phpcrud folder and inside this file are the following codes:
<?php
$dbhost = "localhost";
$dbuser = "root";
$dbpass = "";
$dbname = "phpcrud";
$conn = mysqli_connect($dbhost, $dbuser, $dbpass,$dbname) or die ('Error connecting to mysql');
?>
Just to explain the above code…
$dbhost = “localhost”;
-$dbhost is the MySQL server that you are using. If you’re using your computer as your testing server, its value is usually “localhost”.
$dbuser = “root”;
$dbpass = “”;
-These are the MySQL username and password that you have created or configured. The “root” and blank (“”) password are the usual settings when you haven’t configured it in your localhost. But you can changed them. For example, you are using PHPMyAdmin to manage your database, just find the privileges link.
$dbname = “phpcrud “;
-It is the name of your database.
$conn = mysqli_connect($dbhost, $dbuser, $dbpass,$dbname) or die (‘Error connecting to mysql’);
-It is the syntax for connecting to MySQL database. mysqli_connect($dbhost, $dbuser, $dbpass,$dbname) will do but you may add the or die (‘Error connecting to mysqli’) for debugging purposes. The $conn variable is used for starting and closing the database connection later.
Sample table and data, you can run this script in your phpcrud database:
CREATE TABLE IF NOT EXISTS `users` ( `id` int(11) NOT NULL AUTO_INCREMENT, `firstname` varchar(32) NOT NULL, `lastname` varchar(32) NOT NULL, `username` varchar(32) NOT NULL, `password` varchar(32) NOT NULL, `modified` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=52 ; INSERT INTO `users` (`id`, `firstname`, `lastname`, `username`, `password`, `modified`) VALUES (28, 'John', 'Dalisay', 'john', 'john123', '2011-09-23 14:36:19'), (39, 'aaa', 'aaa', 'aaa', 'aaa', '2011-09-23 15:44:04'), (40, 'bbb', 'bbb', 'bbb', 'bbb', '2011-09-23 15:44:13'), (41, 'ccc', 'ccc', 'ccc', 'ccc', '2011-09-23 15:44:32'), (46, 'XXXDDD', '', '', '', '2011-09-23 17:13:15'), (47, 'www', 'www', 'www', 'www', '2011-09-23 17:21:38'), (48, 'HEHEHEHEHE', '', '', '', '2011-09-23 18:50:10'), (49, 'www', 'www', 'www', 'www', '2011-09-23 19:28:24'), (50, 'EEEE', 'EEEE', 'EEEE', 'EEEE', '2011-09-24 05:01:36'), (51, 'asdf', 'asdf', 'asdf', 'asdf', '2011-10-04 18:44:19');
Our insert.php will have the following code:
<html>
<head>
<title>Create - PHP CRUD</title>
</head>
<body>
<?php
if(isset($_POST['save'])){
//include database configuration
include 'config.php';
extract($_REQUEST);
//sql insert statement
$query=mysqli_query($conn,"insert into users SET firstname='$firstname', lastname='$lastname', username='$username', password='$password'") or die(mysqli_error($conn));
//insert query to the database
if($query){
//if successful query
echo "New record was saved.";
}
}
?>
<!--we have our html form here where user information will be entered-->
<form action='#' method='post' border='0'>
<table>
<tr>
<td>Firstname</td>
<td><input type='text' name='firstname' /></td>
</tr>
<tr>
<td>Lastname</td>
<td><input type='text' name='lastname' /></td>
</tr>
<tr>
<td>Username</td>
<td><input type='text' name='username' /></td>
</tr>
<tr>
<td>Password</td>
<td><input type='password' name='password' /></td>
<tr>
<td></td>
<td>
<input type='submit' value='Save' name="save" />
</td>
</tr>
</table>
</form>
</body>
</html>
When the form is filled up and submitted, it will look like this:
Now we will simply display data from MySQL database to HTML table with the help of PHP.
Our show_data.php will have the following code:
<?php
//include database configuration
include 'config.php';
//selecting records
$sql="select firstname, lastname, username from users";
//query the database
$rs=mysqli_query($conn,$sql) or die($sql.">>".mysqli_error($conn));
//count how many records found
$num=mysqli_num_rows($rs);
if($num>0){ //check if more than 0 record found
?>
<table border='1'>
<tr>
<th>Firstname</th>
<th>Lastname</th>
<th>Username</th>
</tr>
<?php
//retrieve our table contents
while($row=mysqli_fetch_array($rs)){
//extract row
//this will make $row['firstname'] to
//just $firstname only
extract($row);
//creating new table row per record
?>
<tr>
<td><?php echo $firstname; ?></td>
<td><?php echo $lastname; ?></td>
<td><?php echo $username; ?></td>
</tr>
<?php
}
?>
</table>
<?php
}else{ //if no records found
echo "No records found.";
}
?>
Our output will look like:
Now we’re going to do a code that update a record from our MySQL database using PHP.
First, we have to display our data to a table and put a new column called “action”. On the action column we’re gonna have the edit link for each record. Our update.php will have the following code:
<?php
//include database configuration
include 'config.php';
//selecting records
$query=mysqli_query($conn,"select id, firstname, lastname, username from users");
//count how many records found
$num=mysqli_num_rows($query);
if($num>0){ //check if more than 0 record found
?>
<table border='1'>
<tr>
<th>Firstname</th>
<th>Lastname</th>
<th>Username</th>
<th>Action</th>
</tr>
<?php
//retrieve our table contents
while($row=mysqli_fetch_array($query)){
//extract row
//this will make $row['firstname'] to
//just $firstname only
extract($row);
//creating new table row per record
?>
<tr>
<td><?php echo $firstname; ?></td>
<td><?php echo $lastname; ?></td>
<td><?php echo $username; ?></td>
<!--we will have the edit link here-->
<td>
<a href="edit.php?id=<?php echo $id; ?>">Edit</a>
</td>
</tr>
<?php
}
?>
</table>
<?php
}else{ //if no records found
echo "No records found.";
}
?>
The code above should look like this on our browser:
Then once the user clicked an edit link for a record, the user will be redirected to the edit screen which is our edit.php, we’ll have the following code:
<?php
if(isset($_REQUEST['id'])){
include('config.php');
if(isset($_REQUEST['edit'])){
extract($_REQUEST);
//update the record if the form was submitted
$query=mysqli_query($conn,"update users set firstname='$firstname', lastname='$lastname', username='$username', password='$password' where id='$id'") or die(mysql_error());
if($query){
//this will be displayed when the query was successful
echo "<div>Record was edited.</div>";
}
}
$id=$_REQUEST['id'];
//this query will select the user data which is to be used to fill up the form
$query=mysqli_query($conn,"select * from users where id='$id'") or die(mysqli_error($conn));
$num=mysqli_num_rows($query);
//just a little validation, if a record was found, the form will be shown
//it means that there's an information to be edited
if($num>0){
$row=mysqli_fetch_assoc($query);
extract($row);
?>
<!--we have our html form here where new user information will be entered-->
<form action='' method='post' border='0'>
<table>
<tr>
<td>Firstname</td>
<td><input type='text' name='firstname' value='<?php echo $firstname; ?>' /></td>
</tr>
<tr>
<td>Lastname</td>
<td><input type='text' name='lastname' value='<?php echo $lastname; ?>' /></td>
</tr>
<tr>
<td>Username</td>
<td><input type='text' name='username' value='<?php echo $username; ?>' /></td>
</tr>
<tr>
<td>Password</td>
<td><input type='password' name='password' value='<?php echo $password; ?>' /></td>
<tr>
<td></td>
<td>
<!-- so that we could identify what record is to be updated -->
<input type='hidden' name='id' value='<?php echo $id ?>' />
<!-- we will set the action to edit -->
<input type='submit' value='Edit' name="edit" />
</td>
</tr>
</table>
</form>
<?php
}else{
echo "<div>User with this id is not found.</div>";
}
}
else{
echo "<div> You are not authorized to view this page";
}
echo "<a href='index.php'>Back To List</a>";
?>
The code above should look like this:
Now we’re gonna do a code that can delete record from your database.
This code uses JavaScript as prompt to the user if he really want to delete certain record. A pop up will appear once the user clicked the delete link. On our index.php, we’ll have the following code:
<html>
<head>
<title>Delete Record</title>
</head>
<body>
<?php
//include database configuration
include 'config.php';
//check if an action was set, we use GET this time since we get the action data from the url
isset($_GET['action']) ? $action=$_GET['action'] : $action="";
if($action=='delete'){ //if the user clicked ok, run our delete query
$id=$_REQUEST['id'];
$query = mysqli_query($conn,"DELETE FROM users WHERE id='$id'") or die(mysqli_error($conn));
if($query){
//this will be displayed when the query was successful
echo "<div>Record was deleted.</div>";
}
}
//selecting records
$query2=mysqli_query("select * from users") or die(mysqli_error($conn));
//count how many records found
$num=mysqli_num_rows($query2);
if($num>0){ //check if more than 0 record found
echo "<table border='1'>";//start table
//creating our table heading
echo "<tr>";
echo "<th>Firstname</th>";
echo "<th>Lastname</th>";
echo "<th>Username</th>";
echo "<th>Action</th>"; //we're gonna add this column for delete action
echo "</tr>";
//retrieve our table contents
while($row=mysqli_fetch_array($query2)){
//extract row
//this will make $row['firstname'] to
//just $firstname only
extract($row);
//creating new table row per record
echo "<tr>";
echo "<td>{$firstname}</td>";
echo "<td>{$lastname}</td>";
echo "<td>{$username}</td>";
//we will have the delete link here, you can also put your edit link here, but for this tutorial we will just include the delete link
echo "<td>";
echo "<a href='#' onclick='delete_user( {$id} );'>Delete</a>";
echo "</td>";
echo "</tr>";
}
echo "</table>";//end table
}else{ //if no records found
echo "No records found.";
}
?>
<script type='text/javascript'>
function delete_user( id ){
//this script helps us to
var answer = confirm('Are you sure?');
if ( answer ){ //if user clicked ok
//redirect to url with action as delete and id to the record to be deleted
window.location = 'index.php?action=delete&id=' + id;
}
}
</script>
</body>
</html>
Our code should look like this on the browser:
0 comments:
Post a Comment