Skip to main content

A Complete Note Keeping Application

A Complete Note Keeping Application

In this post we are going to expand the Note Keeping Application, using MySQL to create a complete multi-user Application which would be able to register new users as well. It’d be a multi-user system hence many users can register and work on their notes simultaneously. Each user’s data will be private to them. It’d also have the missing features such as editing and deleting of existing notes, hence a complete Note Keeping Application.

In a multi-user application we need to separate each user’s data so that every user is shown only the data belonging to them. As we’ll be using MySQL database to store data, one option is to create a table for each user, it’d be good but when you have like thousands of users, having a table for each is not very efficient.

There is one very efficient technique however, to separate data of different users using relational database structure. For this we’ll only need two tables for the whole application. One will store user-data along with an unique userID (serialized) for each row (or user). The second table will store note data, such as serial number, title, body etc. along with one more column to store the userID (from table 1) to know the user storing the note. The following image will clarify this:

Foreign Key and Primary Key

ID field was a Primary Key in the first table while when it appears in the second table to make relation between the two tables; it is called a ‘foreign key’. Please note that we are not doing anything special to create a foreign key, just using a logical relation.

So with this structure any note getting stored has a valid ID (according to the user who is logged in) in the userID field of the second (notes) table. Thus each row in the second table is logically separated and related with the user who has stored it.

Apart from multi-user functionality, we’re also integrating other features like editing and deleting existing notes, which are done using the following SQL queries:

For editing a note:

UPDATE <table-name> SET <column1=new-data1>,
<column2=new-data2>,

WHERE <column0=some-data>

For deleting a note:

DELETE FROM <table-name>
WHERE <some-column=some-data>

Now we have enough knowledge to see the code:

login.php:

<html>
<head>
<title>My Notes | Login</title>
</head>

<body>
<h1>My Notes</h1>
<h2>Login </h2>
<?php
//if submit button was pressed
//that means form was submitted
if(isset($_POST['submit']))
{
    
//connect to MySQL
    //change '-USER-' and '-PASS-'
    
$db=new mysqli('localhost','-USER-','-PASS-');
    
$db->select_db('one');

    
//fetch other form data
    
$username=trim($_POST['username']);
    
$password=trim($_POST['password']);
    
    
//start a session
    
session_start();
    
$result=$db->query("select pass from user where uname='$username'");
    if(
$result->num_rows>0
    {
        
$pass=$result->fetch_row();
        
$pass=$pass[0];
        if(
$password=$pass)
        {
            
//save session variable with the username
            //which will be unique
            
$_SESSION['user']=$username;
            
//redirect to homepage
            
header("Location: home.php");
        }
    }
    echo 
"<p style=\"color:#ff0000;\">Incorrect Username/Password. Please Try Again.</p>";
    
//close db connection
    
$db->close();
}
else
//requesting the login page
{
    
//if requesting the login page
    //check if already logged in
    //and redirect to homepage if true

    //start session
    
session_start();
    if(isset(
$_SESSION['user']))
    {
        
//redirect to homepage
        //if already logged in
        
header("Location: home.php");
    }
}
//if not logged in show the login page
?>
<form name="form1" id="form1" method="post" action="">
  <table width="30%" border="0" cellspacing="0" cellpadding="0">
    <tr> 
      <td>Username</td>
      <td><input name="username" type="text" id="username" /></td>
    </tr>
    <tr> 
      <td>Password</td>
      <td><input name="password" type="password" id="password" /></td>
    </tr>
    <tr>
      <td>&nbsp;</td>
      <td><input name="submit" type="submit" id="submit" value="Submit" /></td>
    </tr>
  </table>
</form>
<p>New User <a href="user_reg.php">Register Here</a></p>
</body>
</html>

user_reg.php: Please see Designing a User-Registration Script in PHP for this. Since this post has already gotten so long.

home.php:

<html>
<head>
<title>My Notes</title>
</head>

<body>
<h1>My Notes</h1>
<p>Create A New Note</p>
<form name="form1" id="form1" method="post" action="home.php">
  <table width="100%" border="0" cellspacing="0" cellpadding="0">
    <tr> 
      <td width="22%"><font size="2">Title</font></td>
      <td width="78%"><input name="title" type="text" id="title" size="30"  /></td>
    </tr>
    <tr> 
      <td><font size="2">Body</font></td>
      <td><textarea name="body" cols="30" rows="7" id="body"></textarea></td>
    </tr>
    <tr> 
      <td><input name="post" type="submit" id="post" value="Post Note" /></td>
      <td><input type="reset" name="Submit2" value="Reset" /></td>
    </tr>
  </table>
</form>
<?php
//start session again
session_start();
//store the username
//that was stored by the login page
$uname=$_SESSION['user'];
//if someone is requesting this page
//without logging in
if(!isset($uname))
    
//redirect to login page
    
header('Location: login.php');
    
//connect to MySQL 
//provide your 'USERNAME' and 'PASSWORD' 
//change 'localhost' to the MySQL server 
//host, if not using on 'local server' 
$db=new mysqli('localhost','root','26519877');

//if this is the first time
//and database is not craeted
if(!$db->select_db('one'))
    
//create the database
    
$db->query('create database one');

//select the databasw to work with
$db->select_db('one');
            
//if table is not craeted, craete it
if(!$db->query('select * from notes'))
    
$db->query('create table notes(id int auto_increment primary key, title varchar(100), date varchar(50), body varchar(1000), userid int)');
        
//find the 'userid' from the user table
//it is the unique id of the logged in user
//it'd be needed to retrieve and save notes
$result=$db->query("select userid from user where uname='$uname'");
$result=$result->fetch_row();
$userid=$result[0];
    
//if logged in
echo "<p style=\"background: #000; color: #fff;\"><b>Hello: <i>".$_SESSION['user']."</b></i></p>";
echo 
"<a href=\"?action=logout\">Log Out</a></p>";

//----PERFORM ACTION AS PER WHAT WAS CLICKED----
//if 'post' button was pressed
//i.e note was posted
if(isset($_POST['post']))
{
    
$title=strip_tags($_POST['title']);
    
$body=strip_tags($_POST['body']);
    
//convert newlines in the body to <br />
    //so that formatting dont by user remains
    
$body=nl2br($body);
    
//have current date and time
    
$date=date('H:iA jS F Y');
    
    
//escape special characters that
    //can cause probs with MySQL
    
$title=addslashes($title);
    
$body=addslashes($body);
    
$date=addslashes($date);
    
    
//if body or title field was not blank
    
if($body!='' && $title!='')
        
//ready to insert data
        
$db->query("insert into notes (title, date, body, userid) values ('$title', '$date', '$body', '$userid')");
}
//if edit button was clicked on from the edit
//page
if(isset($_POST['edit']))
{
    
$title=strip_tags($_POST['title']);
    
$body=strip_tags($_POST['body']);
    
$date=strip_tags($_POST['date']);
    
//fetch the Note ID sent by the hiddeen form element
    
$id=trim($_POST['id']);
    
    
//convert newlines in the body to <br />
    //so that formatting dont by user remains
    
$body=nl2br($body);
    
//have current date and time
    
    //escape special characters that
    //can cause probs with MySQL
    
$title=addslashes($title);
    
$body=addslashes($body);
    
$date=addslashes($date);
    
    
//if body or title field was not blank
    
if($body!='' && $title!='')
        
//ready to updatedata
        
$db->query("update notes set title='$title', date='$date', body='$body' where id='$id'");
}

//if delete link is clicked w.r.t any note
if($_GET['action']=='del')
{
    
//have the ID of note to be deleted
    
$noteid=$_GET['id'];
    
    
$db->query("delete from notes where id='$noteid'");
}
//if logout was clicked
if($_GET['action']=='logout')
{
    unset(
$_SESSION['user']);
    
//redirect to login page
    
header('Location: login.php');
}
//----/ACTION PERFORMED----
    
//----SHOW THE PREVIOUS NOTES----
echo "<h2 style=\"background: #000; color: #fff;\">Previous Notes</h2>";
//fetch all the notes of the user
//'order by id desc' to have in newest first order
$result=$db->query("select * from notes where userid='$userid' order by id desc");
$num_rows=$result->num_rows;
for(
$i=0;$i<$num_rows;$i++)
{
    
$row=$result->fetch_row();
    
$id=$row[0];
    
$title=$row[1];
    
$date=$row[2];
    
$body=$row[3];
    
    
//we'd escaped special chars
    //so de-escaping those
    
$title=stripslashes($title);
    
$body=stripslashes($body);
    
$date=stripslashes($date);
    
    echo 
"<h3>$title</h3>
          <p><i>$date</i></p>
          <p>$body<br />"
;
    
//echo deleting option
    
echo "<a href=\"?action=del&id=$id\">Delete</a> | <a href=\"edit.php?id=$id\">Edit</a></p>";
}
//----/NOTES SHOWN----
$db->close();
?>
</body>
</html>

edit.php:

<?php
//fetch the unique note id sent
$id=(int)$_GET['id'];

//start session again
session_start();
//store the username
//that was stored by the login page
$uname=$_SESSION['user'];
//if someone is requesting this page
//without logging in
if(!isset($uname))
    
//redirect to login page
    
header('Location: login.php');
    
//connect to MySQL 
//provide your 'USERNAME' and 'PASSWORD' 
//change 'localhost' if required 
$db=new mysqli('localhost','root','26519877');

//select the databasw to work with
$db->select_db('one');

//query MySQL for the specific note
$result=$db->query("select * from notes where id='$id'");
//there will only be one note with an id
//fetch the row and its data
$result=$result->fetch_row();
$title=$result[1];
$date=$result[2];
$body=$result[3];

$db->close();
?>
<html>
<head>
<title>My Notes | Edit</title>
</head>

<body>
<h1>My Notes</h1>
<p>Edit Note</p>
<form name="form1" id="form1" method="post" action="home.php">
  <table width="100%" border="0" cellspacing="0" cellpadding="0">
    <tr> 
      <td width="22%"><font size="2">Title</font></td>
      <td width="78%"><input name="title" type="text" id="title" value="<?php echo $title?>" size="30"  /></td>
    </tr>
       
      <td>Date</td>
      <td><input name="date" type="text" id="date" value="<?php echo $date?>" size="30">
      <!-- hidden field is used for sending some information that user doesn't need to edit -->
      <!--here we are using it to send the ID of the note, it is needed to UPDATE the row -->
        <input name="id" type="hidden" id="id" value="<?php echo $id?>"></td>
    <tr> 
      <td><font size="2">Body</font></td>
      <td><textarea name="body" cols="30" rows="7" id="body"><?php echo $body?></textarea></td>
    </tr>
    <tr> 
      <td><input name="edit" type="submit" id="edit" value="Save Changes" /></td>
      <td><input type="reset" name="Submit2" value="Reset" /></td>
    </tr>
    <tr>
    </tr>
  </table>
</form>

Previous Articles:

Popular posts from this blog

Fix For Toshiba Satellite "RTC Battery is Low" Error (with Pictures)

RTC Battery is Low Error on a Toshiba Satellite laptop "RTC Battery is Low..." An error message flashing while you try to boot your laptop is enough to panic many people. But worry not! "RTC Battery" stands for Real-Time Clock battery which almost all laptops and PCs have on their motherboard to power the clock and sometimes to also keep the CMOS settings from getting erased while the system is switched off.  It is not uncommon for these batteries to last for years before requiring a replacement as the clock consumes very less power. And contrary to what some people tell you - they are not rechargeable or getting charged while your computer or laptop is running. In this article, we'll learn everything about RTC batteries and how to fix the error on your Toshiba Satellite laptop. What is an RTC Battery? RTC or CMOS batteries are small coin-shaped lithium batteries with a 3-volts output. Most laptops use

The Best Way(s) to Comment out PHP/HTML Code

PHP supports various styles of comments. Please check the following example: <?php // Single line comment code (); # Single line Comment code2 (); /* Multi Line comment code(); The code inside doesn't run */ // /* This doesn NOT start a multi-line comment block /* Multi line comment block The following line still ends the multi-line comment block //*/ The " # " comment style, though, is rarely used. Do note, in the example, that anything (even a multi-block comment /* ) after a " // " or " # " is a comment, and /* */ around any single-line comment overrides it. This information will come in handy when we learn about some neat tricks next. Comment out PHP Code Blocks Check the following code <?php //* Toggle line if ( 1 ) {      // } else {      // } //*/ //* Toggle line if ( 2 ) {      // } else {      // } //*/ Now see how easy it is to toggle a part of PHP code by just removing or adding a single " / " from th

How to Remove Bullet Points in CSS

This will be a short guide on how to remove bullet points from ordered/unordered lists with CSS, you only need two lines of CSS for this. The first removes the actual bullet points and the second one removes the space to the left, as evident from the following video: We'll also be doing some bullet beautification in the last section if that's what you are looking to do. How to Remove Bullet Points in HTML/CSS Now that we know what CSS properties actually accomplish what we want let's see how we can implement this in our HTML code. Using Inline "style" Tag (The quick and dirty way) As the title suggests, this is the quickest way to remove bullet points in which you wouldn't have to edit any CSS files (for example, for Blogger or WordPress). This method would be useful for a one-off case - just add the following " style " attribute to the list you want to remove the bullet points from: <h1>Ordered List</h1> <ol  style="list-styl