Lab 12 - CRUD - PHP, MySQL programming

Due Dec 9

php-crud.jpg

Overview

This lab deals with creating a CRUD PHP program to manage a database table. What if CRUD?

  • C - Create new records
  • R - Read, or list, existing data in table
  • U - Update existing records
  • D - Delete records from table

Requirements

Please note - most of this last lab is ONLY required for students who have completed Software development. Other students may do it for extra credit.

The grading will be as follows:

Objective Software Development CS II CS I
Get existing sample code moved to your directory and working 20 70 100
Change the code to include user email address 40 40 40
Change code to include Address, City, State, Zip 40 40 40
Chance code to have a general search. A form is presented with all fields. The user can enter data in any field(s). Only records matching are shown. 40 40 40

In this tutorial we learn create, read, and update data Using PHP and MySQL . The programmers called it CRUD. CRUD means CREATE, READ, UPDATE and DELETE.

The code can be found on cs.mvnu.edu at "/var/www/classes/csc3031/crudex/"

You may try it out at: http://cs.mvnu.edu/classes/csc3031/crudex/menu.html

Now we will focus on how to create new database record with PHP and MySQL .

Opening the database

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 = "demo";
$dbpass = "demo";
$dbname = "demophpcrud";

$conn = mysqli_connect($dbhost, $dbuser, $dbpass,$dbname) or die ('Error connec\
ting 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 = "demo"; $dbpass = "demo";

  • 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.

Create the Database Table

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_TIME
STAMP,
  PRIMARY KEY (`id`)
 ) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=52 ;

 INSERT INTO `users` (`id`, `firstname`, `lastname`, `username`, `password`, `mo
dified`) VALUES
 (28, 'James', 'Skon', 'skon', '123abc', '2013-11-23 14:36:19'),
 (39, 'aaa', 'aaa', 'aaa', 'aaa', '2013-09-23 15:44:04'),
 (40, 'bbb', 'bbb', 'bbb', 'bbb', '2012-09-23 15:44:13'),
 (41, 'ccc', 'ccc', 'ccc', 'ccc', '2013-09-23 15:44:32'),
 (46, 'XXXDDD', '', '', '', '2013-09-23 17:13:15'),
 (47, 'www', 'www', 'www', 'www', '2013-09-23 17:21:38'),
 (48, 'HEHEHEHEHE', '', '', '', '2013-09-23 18:50:10'),
 (49, 'www', 'www', 'www', 'www', '2012-09-23 19:28:24'),
 (50, 'EEEE', 'EEEE', 'EEEE', 'EEEE', '2013-09-24 05:01:36'),
 (51, 'asdf', 'asdf', 'asdf', 'asdf', '2012-10-04 18:44:19');

Inserting Data into the table

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', l
astname='$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.";
      }
   }
?>
<h2>User Add</h2>
<!--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>
<form>
<input type="button" value="Main Menu" onClick="parent.location='menu.html'">
</form>
</body>
</html>

When the form is filled up and submitted, it will look like this:

  • UserAdd.png

Viewing the data in the table

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.";
   }
?>
<form>
<input type="button" value="Main Menu" onClick="parent.location='menu.html'">
</form>

Our output will look like:

ListUsers.png

Updating information in the table

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 user
s");
   //count how many records found
   $num=mysqli_num_rows($query);
   if($num>0){ //check if more than 0 record found
?>
<h2>User Edit</h2>
<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.";
   }
?>
<form>
<input type="button" value="Main Menu" onClick="parent.location='menu.html'">
</form>

The code above should look like this on our browser:

UserEdit.png

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', las
tname='$lastname', username='$username', password='$password' where id='$id'") o
r 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(mys
qli_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;  ?>' /></t
d>
</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='update.php'>Back To List</a>";
?>

The code above should look like this:

EditUserPage.png

Deleting records from the Database

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 delete.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 da
ta 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(mys
qli_error($conn));
      if($query){
         //this will be displayed when the query was successful
         echo "<div>Record was deleted.</div>";
      }
   }
   //selecting records
   $query2=mysqli_query($conn,"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 "<h2>User delete</h2>";
      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 he
re, 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 dele
ted
         window.location = 'index.php?action=delete&id=' + id;
      }
   }
</script>
<form>
<input type="button" value="Main Menu" onClick="parent.location='menu.html'">
</form>
</body>
</html>

Our code should look like this on the browser:

UserDelete.png

If the "Delete" link is pressed, the system should ask you to confirm:

DeleteConfirm.png

Setting up a main menu

Finally we have code to tie the system together with a main menu, menu.html.

<html>
<head>
<title>User Management Menu</title>
</head>
<body>
<h2>User Management Menu</h2>
<ul>
<li><a href="insert.php">Add New Users</a></li>
<li><a href="show_data.php">Show Current Users</a></li>
<li><a href="update.php">Change User Information</a></li>
<li><a href="delete.php">Delete Users</a></li>
</ul>
</body>
</html>

Result:

MainMenu.png

Turn in

On Moodle turn in alink to your work

  • php-crud.jpg:
Topic attachments
I Attachment Action Size Date Who Comment
Pngpng DeleteConfirm.png manage 14.9 K 2013-12-06 - 14:01 JimSkon Delete User Confirm
Pngpng EditUserPage.png manage 11.5 K 2013-12-06 - 14:01 JimSkon Edit User
Pngpng ListUsers.png manage 16.1 K 2013-12-06 - 13:59 JimSkon List Users
Pngpng MainMenu.png manage 8.9 K 2013-12-06 - 14:02 JimSkon Main Menu
Pngpng UserAdd.png manage 7.9 K 2013-12-06 - 13:59 JimSkon User Add
Pngpng UserDelete.png manage 20.6 K 2013-12-06 - 14:01 JimSkon Delete User List
Pngpng UserEdit.png manage 27.0 K 2013-12-06 - 14:00 JimSkon User Edit List
Jpgjpg php-crud.jpg manage 20.6 K 2013-12-06 - 15:50 JimSkon  
Topic revision: r2 - 2013-12-06 - JimSkon
 
This site is powered by the TWiki collaboration platformCopyright &© by the contributing authors. All material on this collaboration platform is the property of the contributing authors.
Ideas, requests, problems regarding TWiki? Send feedback