Home » MySQL, PHP8 December 2007

CRUD Create, Read, Update, Delete in PHP & MySQL with Login & Register

In another article, I have written about Very Simple Add, Edit, Delete, View in PHP & MySQL. That article contains a basic CRUD (Create, Read, Update, Delete) system. It doesn’t have the feature of login and register.

In this article, I will be presenting a complete CRUD system containing login and register feature. User should register himself first. And then he can add data after logging in.

Here is the step-by-step guide on creating such CRUD system:

First of all, we need to create database and tables. Let the database name be ‘test2‘.

create database test2;

There are two tables in the system: login and products.

login: This table contains user’s information for login. It contains user’s name, email, username and password. User registration data is saved into this table.

products: This table contains data added by logged in users. This table contains product information like name, quantity, and price.

use test2;

CREATE TABLE login (
id int(9) NOT NULL auto_increment,
name varchar(100) NOT NULL,
email varchar(100) NOT NULL,
username varchar(100) NOT NULL,
password varchar(100) NOT NULL,
PRIMARY KEY (id)
) ENGINE=InnoDB;

CREATE TABLE products (
id int(11) NOT NULL auto_increment,
name varchar(100) NOT NULL,
qty int(5) NOT NULL,
price decimal(10,2) NOT NULL,
login_id int(11) NOT NULL,
PRIMARY KEY (id),
CONSTRAINT FK_products_1
FOREIGN KEY (login_id) REFERENCES login(id)
ON UPDATE CASCADE ON DELETE CASCADE
) ENGINE=InnoDB;

Note: Cascading is done in products table making login_id as foreign key to login table. This means that all the products entered by a user is automatically deleted from products table whenever that particular user is deleted from login table.

Now, we will create a connection.php file which contains database connection code. This code connects to the MySQL database. This file is included in all PHP pages where database connection is necessary.

connection.php

In below code, the database host name is localhost where username=root and password=root. The database test2 has been selected.

index.php is our homepage. In this page, if the user is not logged in then login and register links are displayed. If the user is logged in then the user is greeted and a link is displayed to view & add products.

Note: session_start() function should be written at the beginning of every php file where session handling is done. You can see the same in index.php as well.

index.php

index.php uses a little bit of Cascading StyleSheet (CSS). Here is the CSS file code used in index.php.

style.css

Registration page asks for user’s name, email, username and password. The registration data is saved in login table.

register.php

After successful registration, user needs to login in order to add products in the system.

When the login is successful, I have set three SESSION variables. You can see it in below login.php code.

SESSION[‘valid’] = This variable contains user’s username
SESSION[‘name’] = This variable contains user’s full name
SESSION[‘id’] = This variable contains user’s id

login.php

After successful login, user is redirected to homepage (index.php). There is a link to view and add new products.

To add product data into database, we need an html form.

add.html

Form action on add.html is add.php. It means that the submitted form data will go to add.php. In add.php, we do a simple validation of checking if the entered name, quantity & price are empty or not. If they are all filled then the data will be inserted into database table.

add.php

Products data is fetched from database and displayed in view.php file. This file also contains a link to add data. On every row of displayed data, there is also a link to edit and delete data. Below is a sample image of view.php:

CRUD PHP MySQL

At the beginning of view.php file below and add.php file above, you must have noticed session_start() and isset() function.

session_start() function should be written at the beginning of every page where SESSION handling is done.

isset() function checks if the SESSION variable is set or not. If not, the user will be redirected to login page (login.php).

view.php

Each row of data can be edited separately. Row ID is passed in the URL of edit.php. ID uniquely identifies the data entry.

While adding data, we had two files: add.html and add.php. While editing data, I have kept the entire thing in a single edit.php file. Edit form in HTML and database update code in PHP are present in the same file.

In the code below, at first a single row entry of data is fetched based on the id. The fetched data is displayed in the edit form. When user edits the data and submits the form, then some simple validation is done for empty data. When everything is correct, then that particular entry of data is updated in database.

edit.php

Each row of data can be deleted separately. Row ID is passed in the URL of delete.php. ID uniquely identifies the data entry. After deletion, the user is redirected to view page (view.php).

delete.php

User can finally logout. User is redirected to homepage (index.php) after logout.

logout.php

Download Full Source Code: Complete Create, Read, Update, Delete in PHP & MySQL

Hope this helps.
Thanks.

From Mukesh Chapagain's Blog, post CRUD Create, Read, Update, Delete in PHP & MySQL with Login & Register

php magento mukesh chapagain

Get New Post by Email

Find me on

Facebook Twitter Google+ LinkedIn RSS Feed
  • Pingback:   Very simple add,edit,delete,display in PHP — Chapagain.com.np :: Blog()

  • Pingback:   Very simple add, edit, delete, display in PHP — Chapagain.com.np :: Blog()

  • 345gfh

    good luck chap !

  • lokoHAN

    could you help me with my file system??..
    please’..

    makulet_jerome12@yahoo.com

  • Kevin Parula090

    *_WEAK_*

  • Nanerme69

    very simple.. yet… very effective.. thank you very much! 

  • Geloalfaro

    tnx a lot, hehehehe, big help,

  • Pratikpate

    how can i edit record which are already there in the admin panel

  • pyxis

    may I ask where is the edit function?

  • unknown king

    thanks lottt………….

  • nikita

    hello, I did my view, edit and delete php but its not working.. Can you help me ?? :(

  • nikita

    hai, I’m having problem in edit.php and delete.php… some errors in that code and I really dunno where is the mistake… Can you help me..??

  • http://blog.chapagain.com.np/ Mukesh

    That has been fixed. Edit, delete feature was missing. Now there are two versions of the code. The full version is available now with add, edit, delete features.

  • http://blog.chapagain.com.np/ Mukesh

    That has been fixed. Edit, delete feature was missing. Now there are two versions of the code. The full version is available now with add, edit, delete features. Please download it again.

  • AkseliP

    Hmm, some installation instructions? I get error: Could not execute the select query. plz help!

  • hjhjhk

    ghjgjukj

  • Nikhil

    hi
    im beginner to php so will u please hlp how this are arrange to get a perfect form. eill u say r arrange

  • punit

    thanks v much

  • sai

    nice blog

  • jenelyn zepeda

    hi..im new to php and i have the error saying Access denied for user ‘root’@’localhost’ in config.php when i hit the the login button.i’ve donwloaded your full code in login,register,add,edit,delete.how i can fix this error?thank you!keep sharing :D

  • Safoku

    The reason you can access localhost because there is a password.Most localhost don’t have a password. Try removing it. ~Safoku@gmail.com for more questions. :D

  • Safoku

    I meant can’t.

  • safoku

    Try removing the password

  • VAruNA

    Thnx BRO

  • rae

    hi, 1st off thanks alot, but i dnt c the file database.sql

  • http://blog.chapagain.com.np/ Mukesh

    Sorry for the typo. The database file is login.sql.

  • creative network solution

    in Full PHP Code with all features: Login, Register, Add, Edit & Delete

    when we create data base then error shows

    SQL query:

    CREATE TABLE login (

    id
    int( 9 ) NOT NULL AUTO_INCREMENT ,
    username
    varchar( 20 ) COLLATE latin1_general_ci default NULL ,
    password
    varchar( 32 ) COLLATE latin1_general_ci default NULL ,
    dtime
    varchar( 50 ) COLLATE latin1_general_ci default NULL ,
    PRIMARY KEY ( id )

    ) CREATE TABLE users (

    id
    int( 11 ) NOT NULL AUTO_INCREMENT ,
    name
    varchar( 100 ) NOT NULL ,
    age
    int( 3 ) NOT NULL ,
    email
    varchar( 100 ) NOT NULL ,
    PRIMARY KEY ( id )

    )

    MySQL said:

    #1064 – You have
    an error in your SQL syntax; check the manual that corresponds to your MySQL
    server version for the right syntax to use near ‘CREATE TABLE users
    (
    id int(11) NOT NULL auto_increment,
    name varcha’ at line 9

    how we solve this problem

  • bhavin

    thank you… very simple & effective work sir..!