PHP MySQL User Management System Example

From w3cyberlearnings

In this tutorial,

Requirement

You need to have the MySQL database, PHP, and Apache web server up and running before you start this tutorial. This tutorial demonstrates you how to use PHP and MySQL together to create the web based application system. After finish this tutorial, you will learn how to insert, delete, update, and display record from the MySQL database table from the web browser.

Goal

You want to allow student to enter his or her name, and along with his or her address. At this point, you also allow students to enter more than one address for his or her account. In addition, you want to be able to add, delete, edit, and display information related to student. This tutorial will show you on how to make PHP and MySQL work together.

Create Table

student table

CREATE TABLE student(
   student_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
   first_name VARCHAR(200) NOT NULL,
   last_name VARCHAR(200) NOT NULL
);

student_address table

CREATE TABLE student_address(
  student_address_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  address_id INT NOT NULL,
  student_id INT NOT NULL
);

address Table

CREATE TABLE address(
  address_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  street1 VARCHAR(50) NOT NULL,
  street2 VARCHAR(50) NOT NULL,
  city VARCHAR(50) NOT NULL,
  state VARCHAR(20) NOT NULL,
  zip VARCHAR(10) NOT NULL
  
);

Insert Student Records

We insert two students into the student table.

INSERT INTO student(student_id,first_name,last_name) 
VALUES(null,'Sokha','Vol'),(null,'Chang','Kim');

Create Main Page

Code (main.php)

<html>
    <head>
        <title>Main Page</title>
        <style type="text/css">
            body {
                margin:0;
                padding:0;
            }
            #container {
                width:980px;
                margin:0 auto;

            }
        </style>
    </head>
    <body>
        <div id="container">
      <?php

 /*
 * @copyright  2011 by Sophal Chao & w3cyberlearnings.com in respect to modifications of standard topics format.
 * @link       http://www.w3cyberlearnings.com
 * @license    http://creativecommons.org/licenses/by-sa/3.0/ Creative Commons Attribution-ShareAlike 3.0 Unported (CC BY-SA 3.0)
 * You are at your own risks by using this code. 
 */

      // connect to mysql parameters
      $host ='localhost';
      $username = 'root';
      $password= 'caojiang';

      // connect to mysql server
      $connection= mysql_connect ($host, $username, $password)
              or die("Unable to connect to the MySQL Server!");
      // select database school
      mysql_select_db('school',$connection) or die(mysql_error($connection));

      // select student
      $sql_query = 'SELECT student_id, CONCAT(first_name,"  ",last_name)  as fullName FROM student';
      $result = mysql_query($sql_query, $connection) or die(mysql_error($connection));

      echo '<table border="1">';
      echo '<tr><th>Full Name</th><th>Operators</th></tr>';

      while($row = mysql_fetch_assoc($result)){
          echo '<tr>';
          echo '<td><a href="detail.php?id='.$row['student_id']  .'">'. $row['fullName']. '</a></td><td>' ;
          echo '<a href="add.php?id='       .$row['student_id']  .'">[Add]</a>';
          echo '<a href="edit.php?id='      .$row['student_id']  .'">[Edit]</a>';
          echo '<a href="delete.php?id='    .$row['student_id']  .'">[Delete]</a>';
          echo '</td></tr>' ;
      }
      echo '</table>';
      // close connection
      mysql_close($connection);
     ?>
        </div>
    </body>
</html>

Display Result

The full username is a link that and it displays the user detail information. In addition, the add link uses for adding a new record (this include: user address or create a new user account), the edit link uses for update the current user name. Finally, the delete link uses for delete the current user account.

Full Name	Operators
Sokha Vol	[Add][Edit][Delete]
Chang Kim	[Add][Edit][Delete]

Add Page

When you click the [Add] link in the main page it directs you to the add.php page. In the add.php page, you have the option to add the user address or to create a new user account.

Code (add.php)

In this add.php page includes two PHP functions. The display_address_form($id), and the display_new_user_form($id) respectively. The page will display the user select option according.

<?php
 /*
 * @copyright  2011 by Sophal Chao & w3cyberlearnings.com in respect to modifications of standard topics format.
 * @link       http://www.w3cyberlearnings.com
 * @license    http://creativecommons.org/licenses/by-sa/3.0/ Creative Commons Attribution-ShareAlike 3.0 Unported (CC BY-SA 3.0)
 * You are at your own risks by using this code. 
 */

    $id= $_REQUEST['id'];
    $option = $_REQUEST['option'];

    echo 'Select, which one you want to add? '.
            '<a href="add.php?option=address&id='. $id . '">User Address</a>' . ' or '.
            '<a href="add.php?option=new_user&id='. $id . '">New User Account</a>' ;

    switch($option) {
        case 'address':
            display_address_form($id);
            break;
        case 'new_user':
            display_new_user_form($id);
            break;
    }

    function display_address_form($id) {
        echo "<br/>Adding Address<br/>";
        echo '<form action="commit.php?id='.$id.'"'.'method="post">';
        echo '<table>';
        echo '<tr><td>street1:</td><td><input type="text" name="street1" size="20"/></td></tr>';
        echo '<tr><td>street2:</td><td><input type="text" name="street2" size="20"/></td></tr>';
        echo '<tr><td>city:</td><td><input type="text" name="city" size="20"/></td></tr>';
        echo '<tr><td>state:</td><td><input type="text" name="state" size="20"/></td></tr>';
        echo '<tr><td>zip:</td><td><input type="text" name="zip" size="20"/></td></tr>';
        echo '<tr><td colspan="2"><input type="submit" name="submit" value="Add Address"/></td></tr>';
        echo '</table>';
        
        echo '<input type="hidden" name="submit_opt" value="address"/>';
        echo '</form>';
        echo '<a href="main.php">Back to home</a>';
    }

    function display_new_user_form($id)  {
        echo "<br/>Adding A New User<br/>";
        echo '<form action="commit.php?id='.$id.'"'.'method="post">';
        echo '<table>';
        echo '<tr><td>First Name:</td><td><input type="text" name="firstName" size="20"/></td></tr>';
        echo '<tr><td>Last Name:</td><td><input type="text" name="lastName" size="20"/></td></tr>';
        echo '<tr><td colspan="2"><input type="submit" name="submit" value="Add A New User"/></td></tr>';
        echo '</table>';
        
        echo '<input type="hidden" name="submit_opt" value="account"/>';
        echo '</form>';

        echo '<a href="main.php">Back to home</a>';
    }
?>

Display Result Step 1

Select, which one you want to add? User Address or New User Account

Display Result Step 2

In this section, we add address for Sokha Vol.

Display Result Step 3

We add a new user.

Center Access Point

This is the most important part of this system that control the access functions in this system.

Code (commit.php)

The $id is for the student id, and the $submit_option is for user submit options. The user options include address,account,edit,delete,and deladdress.

<?php
 /*
 * @copyright  2011 by Sophal Chao & w3cyberlearnings.com in respect to modifications of standard topics format.
 * @link       http://www.w3cyberlearnings.com
 * @license    http://creativecommons.org/licenses/by-sa/3.0/ Creative Commons Attribution-ShareAlike 3.0 Unported (CC BY-SA 3.0)
 * You are at your own risks by using this code. 
 */


// connect to mysql parameters
$host ='localhost';
$username = 'root';
$password= 'caojiang';

// connect to mysql server
$connection= mysql_connect ($host, $username, $password)
      or die("Unable to connect to the MySQL Server!");
// select database school
mysql_select_db('school',$connection) or die(mysql_error($connection));

$id = $_REQUEST['id'];
$submit_option = $_REQUEST['submit_opt'];

switch($submit_option) {
    case 'address':
        if(!empty($_POST['street1']) && !empty($_POST['street2'])
                && !empty($_POST['city']) && !empty($_POST['state'])
                && !empty($_POST['zip']))
        {
            $query = 'INSERT INTO address (street1,street2,city,state,zip)
                    VALUES("'.
                             $_POST['street1']. '","'.
                             $_POST['street2']. '","'.
                             $_POST['city']. '","'.
                             $_POST['state']. '","'.
                             $_POST['zip'].
                    '")' ;

            mysql_query($query, $connection) or die(mysql_error());
            $address_id = mysql_insert_id($connection);
            $query = 'INSERT INTO student_address(student_id, address_id) VALUES('.
                        $id.','. $address_id .')';
            mysql_query($query, $connection) or die(mysql_error());
            echo '<b>Done!</b><br/>';
            echo '<a href="detail.php?id='. $id . '">View User Detail</a><br/>';
        }
        else {
            echo "Plese enter your address correctly! <br/>";
        }
        break;
    case 'account':
        if(!empty($_POST[firstName]) && !empty($_POST['lastName'])) {
            $query ='INSERT INTO student (first_name, last_name)
                        VALUES("' . $_POST['firstName'].'","'. $_POST['lastName'] .'")';
            // insert to student table
            mysql_query($query, $connection) or die(mysql_error($connection));
            $address_id = mysql_insert_id($connection);
            echo '<b>Done!</b>';
        }
        else {
            echo "Please enter first and last name! <br/>";
        }
        break;
    case 'edit':
        if(!empty($_POST['firstName']) && !empty($_POST['lastName'])) {
            $query = 'UPDATE student SET first_name="'.
                        $_POST['firstName']. '", last_name="'.
                        $_POST['lastName']. '"'.
                        ' WHERE student_id='. $id;
            // update student
            mysql_query($query, $connection) or die(mysql_error($connection));
            $address_id = mysql_insert_id($connection);
            echo '<b>Done!</b><br/>';
            echo '<a href="detail.php?id='. $id . '">View User Detail</a><br/>';
        }
        else {
            echo "Please enter first and last name in order to update <br/>";
        }
        break;
    case 'delete':
        // get user information and address information
        $query = 'SELECT address_id, student_id FROM student_address WHERE student_id='. $id . ' LIMIT 1';
        $result = mysql_query($query, $connection) or die(mysql_error($connection));
        $record = mysql_fetch_assoc($result);
        
        if($record) {
            $query = 'DELETE FROM student_address WHERE student_id='. $record['student_id'];
            mysql_query($query, $connection) or die(mysql_error($connection));

            $query = 'DELETE FROM address WHERE address_id='. $record['address_id'];
            mysql_query($query, $connection) or die(mysql_error($connection));
            
            $query = 'DELETE FROM student WHERE student_id='. $record['student_id'];
            mysql_query($query, $connection) or die(mysql_error($connection));
            
            echo '<b>Done</b>';
        }
        else {
            $query = 'DELETE FROM student WHERE student_id='. $id;
            mysql_query($query, $connection) or die(mysql_error($connection));
            echo '<b>Done</b>';
        }
        break;
      case 'deladdress':
            $query = 'DELETE FROM student_address WHERE address_id='.$_GET['address_id'];
            mysql_query($query, $connection) or die(mysql_error($connection));

            $query = 'DELETE FROM address WHERE address_id='. $_GET['address_id'];
            mysql_query($query, $connection) or die(mysql_error($connection));

            echo '<b>Done</b>';
          break;
}

echo '<br/>';
echo '<a href="main.php">Back to home</a><br/>';

?>

Delete Page

This page uses the commit.php to process the delete action. We submit the commit.php with submit_opt=delete and the user id. Firstly, we try to get record from the student_address table by using the student_id. If there are any records on the student_address table, we delete the record on the student,addresss, and student_address tables. If we can not find any records on the student_address table, we will only delete the student table.

Code (delete.php)

<html>
  <head>
    <title></title>
    <meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
  </head>
  <body>
      <b>It will delete the user and addresses</b><br/>
      <b>Are you sure you want to delete?</b>
      <a href="commit.php?submit_opt=delete&id=<?php echo $_GET['id'];?>">Yes</a>
      <a href="main.php">No</a>
  </body>
</html>

Display Result

User Detail Information

This page does not use the commit.php, and this page is by itself. By the way, this page uses to generate detail information about the student, and student address. If a user has more than one address, all the address will be listed here. However, when user want to delete his or her address, we call the commit.php with the ?submit_opt=deladdress&address_id=address_id. You can get more detail from this option by visiting the commit.php.

Code (detail.php)

<?php
 /*
 * @copyright  2011 by Sophal Chao & w3cyberlearnings.com in respect to modifications of standard topics format.
 * @link       http://www.w3cyberlearnings.com
 * @license    http://creativecommons.org/licenses/by-sa/3.0/ Creative Commons Attribution-ShareAlike 3.0 Unported (CC BY-SA 3.0)
 * You are at your own risks by using this code. 
 */

    // connect to mysql parameters
    $host ='localhost';
    $username = 'root';
    $password= 'caojiang';

    // connect to mysql server
    $connection= mysql_connect ($host, $username, $password)
          or die("Unable to connect to the MySQL Server!");
    mysql_select_db('school',$connection);

    // assign $id variable only it is a integer, we get only the integer
    // this make the query a little safer,
    // it will return 0 when user try to put character or unknown value to the query
    
    $id = (isset($_GET['id']) && is_int($_GET['id'])) ? $_GET['id'] : intval($_GET['id']);
    if($id) {
        $query = 'SELECT st.student_id, ad.address_id,
                   CONCAT(st.first_name," " ,st.last_name) AS "name",
                   CONCAT("street1: ", ad.street1, ", street2:", ad.street2, ", city:", ad.city, ", state:", ad.state, ", zip:",ad.zip) 
                AS "address"
                FROM student st,
                     address ad,
                     student_address sd
                WHERE st.student_id = sd.student_id
                    AND ad.address_id = sd.address_id
                    AND st.student_id='. addslashes($id);
    }

    if(isset($query) && !empty($query)) {

        if(!$result = mysql_query($query, $connection))
        {
            die(mysql_error($connection));
        }
        
        $number_rows = mysql_num_rows($result);
        // if the return result is zero, it is no address in the record
        if($number_rows == 0 ) {
            echo " No address is provided! <br/>";
        }
        // loop through the result
        $count_add = 0;
        while($rows = mysql_fetch_assoc($result)) {
            $count_add++;
            echo "<br/>";
            echo "Address: $count_add" . "<br/>";
            echo 'Student Id: '. $rows['student_id']  . "<br/>";
            echo 'Name: '. $rows['name'] . "<br/>";
            echo 'Address: '. $rows['address'] . "<br/>";
            echo '<a href="commit.php?submit_opt=deladdress&address_id='. $rows['address_id']. '">[Delete]</a><br/>';
        }
        echo "<a href=\"main.php\">Back to home</a>";
    }
?>

Display Result

Edit Page

Firstly, this page get the user full name, and finally display as a default value for user to do the update. After user updates his or her name, and submit the form. It will call the commit.php to update the user record on the table. In this page, the submit_opt value is edit.

Code (edit.php)

<?php
 /*
 * @copyright  2011 by Sophal Chao & w3cyberlearnings.com in respect to modifications of standard topics format.
 * @link       http://www.w3cyberlearnings.com
 * @license    http://creativecommons.org/licenses/by-sa/3.0/ Creative Commons Attribution-ShareAlike 3.0 Unported (CC BY-SA 3.0)
 * You are at your own risks by using this code. 
 */

    // connect to mysql parameters
    $host ='localhost';
    $username = 'root';
    $password= 'caojiang';

    // connect to mysql server
    $connection= mysql_connect ($host, $username, $password)
          or die("Unable to connect to the MySQL Server!");

    // select database school
    mysql_select_db('school',$connection) or die(mysql_error($connection));

    $id = $_REQUEST['id'];
    
    if(isset($id) && !empty($id)) {
        $query = 'SELECT first_name, last_name FROM student WHERE student_id='.
                    mysql_escape_string($id);
        $result = mysql_query($query,$connection) or die(mysql_error());
        extract(mysql_fetch_assoc($result));
    }
?>
<html>
    <head>
        <title>Edit Account</title>
    </head>
    <body>
        <form method="post" action="commit.php?id=<?php echo $id;?>">
            <p>First Name:<input type="text" name="firstName" value="<?php echo $first_name; ?>"/></p>
            <p>Last Name:<input type="text" name="lastName" value="<?php echo $last_name; ?>"/></p>
            <input type="submit" name="submit" value="Update"/>
            <input type="hidden" name="submit_opt" value="edit"/>
        </form>
    </body>
</html>

Display Result

Summary

Finally, you have finished this tutorial and hopefully you get some ideas on how to use MySQL and PHP together. This is a simple web based application that you can easily duplicated for your school assignments or any projects.

Search

PHP MySQL User Management System Example

Contents

Requirement

Goal

Create Table

student table

student_address table

address Table

Insert Student Records

Create Main Page

Code (main.php)

Display Result

Add Page

Code (add.php)

Display Result Step 1

Display Result Step 2

Display Result Step 3

Center Access Point

Code (commit.php)

Delete Page

Code (delete.php)

Display Result

User Detail Information

Code (detail.php)

Display Result

Edit Page

Code (edit.php)

Display Result

Summary

Navigation

Web

SQL

MISC

References

Personal tools

Namespaces

Variants

Views

Actions