CRUD

DATABASE : myusers

TABEL : members

CREATE TABLE IF NOT EXISTS `members` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `firstname` varchar(32) CHARACTER SET utf8 NOT NULL,
  `lastname` varchar(32) CHARACTER SET utf8 NOT NULL,
  `email` varchar(32) CHARACTER SET utf8 NOT NULL,
  `password` varchar(32) CHARACTER SET utf8 NOT NULL,
  PRIMARY KEY (`id`);


SCRIPT :

config.php

<?php
 $server = 'localhost';
 $user = '';
 $pass = '';
 $db = 'myusers';

 // Connect to Database
 $connection = mysql_connect("localhost", "root", "")
 or die ("Could not connect to server ... \n" . mysql_error ());
 mysql_select_db("myusers")
 or die ("Could not connect to database ... \n" . mysql_error ());
?>


delete.php

<?php
 // connect to the database
 include('config.php');

 // check if the 'id' variable is set in URL, and check that it is valid
 if (isset($_GET['id']) && is_numeric($_GET['id']))
 {
 // get id value
 $id = $_GET['id'];

 // delete the entry
 $result = mysql_query("DELETE FROM members WHERE id=$id")
 or die(mysql_error());

 // redirect back to the view page
 header("Location: view.php");
 }
 else
 // if id isn't set, or isn't valid, redirect back to view page
 {
 header("Location: view.php");
 }
?>


edit.php

<?php
/*
 EDIT.PHP
 Allows user to edit specific entry in database
*/

 // creates the edit record form
 // since this form is used multiple times in this file, I have made it a function that is easily reusable
 function renderForm($id, $firstname, $lastname, $error)
 {
 ?>
 <html>
 <head>
 <title>Edit</title>
 </head>
 <body>
 <?php
 // if there are any errors, display them
 if ($error != '')
 {
 echo '<div style="padding:4px; border:1px solid red; color:red;">'.$error.'</div>';
 }
 ?>
 <form action="" method="post">
 <input type="hidden" name="id" value="<?php echo $id; ?>"/>
 <div>
 <p><strong>ID:</strong> <?php echo $id; ?></p>
 <strong>First Name: *</strong> <input type="text" name="firstname" value="<?php echo  $firstname; ?>"/><br/>
 <strong>Last Name: *</strong> <input type="text" name="lastname" value="<?php echo $lastname; ?>"/><br/>
 <p>* Required</p>
 <input type="submit" name="submit" value="Submit">
 </div>
 </form>
 </body>
 </html>
 <?php
 }
 // connect to the database
 include('config.php');

 // check if the form has been submitted. If it has, process the form and save it to the database
 if (isset($_POST['submit']))
 {
 // confirm that the 'id' value is a valid integer before getting the form data
 if (is_numeric($_POST['id']))
 {
 // get form data, making sure it is valid
 $id = $_POST['id'];
 $firstname = mysql_real_escape_string(htmlspecialchars($_POST['firstname']));
 $lastname = mysql_real_escape_string(htmlspecialchars($_POST['lastname']));

 // check that firstname/lastname fields are both filled in
 if ($firstname == '' || $lastname == '')
 {
 // generate error message
 $error = 'ERROR: Please fill in all required fields!';

 //error, display form
 renderForm($id, $firstname, $lastname, $error);
 }
 else
 {
 // save the data to the database
 mysql_query("UPDATE members SET firstname='$firstname', lastname='$lastname' WHERE id='$id'")
 or die(mysql_error());

 // once saved, redirect back to the view page
 header("Location: view.php");
 }
 }
 else
 {
 // if the 'id' isn't valid, display an error
 echo 'Error!';
 }
 }
 else
 // if the form hasn't been submitted, get the data from the db and display the form
 {

 // get the 'id' value from the URL (if it exists), making sure that it is valid (checing that it is numeric/larger than 0)
 if (isset($_GET['id']) && is_numeric($_GET['id']) && $_GET['id'] > 0)
 {
 // query db
 $id = $_GET['id'];
 $result = mysql_query("SELECT * FROM members WHERE id=$id")
 or die(mysql_error());
 $row = mysql_fetch_array($result);

 // check that the 'id' matches up with a row in the databse
 if($row)
 {
 // get data from db
 $firstname = $row['firstname'];
 $lastname = $row['lastname'];

 // show form
 renderForm($id, $firstname, $lastname, '');
 }
 else
 // if no match, display result
 {
 echo "No results!";
 }
 }
 else
 // if the 'id' in the URL isn't valid, or if there is no 'id' value, display an error
 {
 echo 'Error!';
 }
 }
?>


update.php

<html>
 <head>
 <title>update</title>
 </head>
 <body>
 <form action="" method="post">
 <div>
 <strong>First Name: </strong> <input type="text" name="firstname" value="" /><br/>
 <strong>Last Name: </strong> <input type="text" name="lastname" value="" /><br/>
 <strong>Email: </strong> <input type="text" name="email" value="" /><br/>
 <strong>Password: </strong> <input type="text" name="password" value="" /><br/>
 <p>* required</p>
 <input type="submit" name="submit" value="Submit">
 </div>
 </form>
<?php
 // connect to the database
 include('config.php');

 // check if the form has been submitted. If it has, start to process the form and save it to the database
 if (isset($_POST['submit']))
 {
 // get form data, making sure it is valid
 $firstname = mysql_real_escape_string(htmlspecialchars($_POST['firstname']));

 $lastname = mysql_real_escape_string(htmlspecialchars($_POST['lastname']));

$email = mysql_real_escape_string(htmlspecialchars($_POST['email']));
$password = mysql_real_escape_string(htmlspecialchars($_POST['password']));

 // check to make sure both fields are entered
 if ($firstname=='' || $lastname=='' || $email == '' || $password == '')
 {
 // generate error message
 $error = 'ERROR: Please fill in all required fields!';

 // if either field is blank, display the form again
 renderForm($firstname, $lastname, $email, $password, $error);
 }
 else
 {
 // save the data to the database
 mysql_query("INSERT members SET firstname='$firstname', lastname='$lastname', email='$email', password='$password'")
 or die(mysql_error());

 // once saved, redirect back to the view page
 header("Location: view.php");
 }
 }
?>
 </body>
 </html>


view.php

<html>
<head>
  <title>view</title>
</head>

<body>
<?php
        // connect to the database
        include('config.php');

        // get results from database
        $result = mysql_query("SELECT*FROM members")
                or die(mysql_error());

        // display data in table
        echo "<p><b>View All</b> | <a href='viewpage.php?page=1'>View Paginated</a></p>";

        echo "<table border='1' cellpadding='10'>";
        echo "<tr> <th>ID</th> <th>First Name</th> <th>Last Name</th> <th>email</th> <th>password</th></tr>";

        // loop through results of database query, displaying them in the table
        while($row = mysql_fetch_array( $result )) {

                // echo out the contents of each row into a table
                echo "<tr>";
                echo '<td>' . $row['id'] . '</td>';
                echo '<td>' . $row['firstname'] . '</td>';
                echo '<td>' . $row['lastname'] . '</td>';
                echo '<td>' . $row['email'] . '</td>';
                echo '<td>' . $row['password'] . '</td>';
                echo '<td><a href="edit.php?id=' . $row['id'] . '">Edit</a></td>';
                echo '<td><a href="delete.php?id=' . $row['id'] . '">Delete</a></td>';
                echo "</tr>";
        }

        // close table>
        echo "</table>";
?>
<p><a href="update.php">Add a new record</a></p>

</body>
</html>


viewpage.php

<html>

<head>
  <title>viewpage</title>
</head>

<body>

<?php

       // connect to the database
        include('config.php');

        // number of results to show per page
        $per_page = 3;

        // figure out the total pages in the database
        $result = mysql_query("SELECT * FROM members");
        $total_results = mysql_num_rows($result);
        $total_pages = ceil($total_results / $per_page);

        // check if the 'page' variable is set in the URL (ex: view-paginated.php?page=1)
        if (isset($_GET['page']) && is_numeric($_GET['page']))
        {
                $show_page = $_GET['page'];

                // make sure the $show_page value is valid
                if ($show_page > 0 && $show_page <= $total_pages)
                {
                        $start = ($show_page -1) * $per_page;
                        $end = $start + $per_page;
                }
                else
                {
                        // error - show first set of results
                        $start = 0;
                        $end = $per_page;
                }
        }
        else
        {
                // if page isn't set, show first set of results
                $start = 0;
                $end = $per_page;
        }

        // display pagination

        echo "<p><a href='view.php'>View All</a> | <b>View Page:</b> ";
        for ($i = 1; $i <= $total_pages; $i++)
        {
                echo "<a href='viewpage.php?page=$i'>$i</a> ";
        }
        echo "</p>";

        // display data in table
        echo "<table border='1' cellpadding='10'>";
        echo "<tr> <th>ID</th> <th>First Name</th> <th>Last Name</th> <th>Email</th> <th>Password</th></tr>";

        // loop through results of database query, displaying them in the table
        for ($i = $start; $i < $end; $i++)
        {
                // make sure that PHP doesn't try to show results that don't exist
                if ($i == $total_results) { break; }

                // echo out the contents of each row into a table
                echo "<tr>";
                echo '<td>' . mysql_result($result, $i, 'id') . '</td>';
                echo '<td>' . mysql_result($result, $i, 'firstname') . '</td>';
                echo '<td>' . mysql_result($result, $i, 'lastname') . '</td>';

                echo '<td>' . mysql_result($result, $i, 'email') . '</td>';

                echo '<td>' . mysql_result($result, $i, 'password') . '</td>';


                echo '<td><a href="edit.php?id=' . mysql_result($result, $i, 'id') . '">Edit</a></td>';
                echo '<td><a href="delete.php?id=' . mysql_result($result, $i, 'id') . '">Delete</a></td>';
                echo "</tr>";
        }
        // close table>
        echo "</table>";

        // pagination

?>
<p><a href="update.php">Add a new record</a></p>
</body>
</html>