Using facebook registration plug-in users can easily sign up on your website using facebook data. With one simple facebook login the form will be filled with user appropriate data. This plug-in is a simple iframe you can place it anywhere on your webpage. You can also add a custom field if facebook doesn’t have.
Showing posts with label Database. Show all posts
Showing posts with label Database. Show all posts
Database Searching Techniques with SQL
Search box most important element on web pages specially contented management sites. In this post I want to explain very basic searching techniques and Unicode data searching using SQL LIKE statement. I hope you like this. Thanks
Stored Procedure Lesson
Are you writing Stored Procedures if not please take a look at this post. Stored procedures can help to improve web application performance and reduce database access traffic. In this post I want to explain how to create and call the stored procedures from database server.
Join Tables Relationships with SQL
My previous post Database Design Create Tables and Relationships with SQL. This post is sequel how to join these tables and displaying proper data. I had used these SQL statements at labs.9lessons.info.
Display Cross Domain Data with Jquery JSON Callback.
Some days back I had posted a tutorial about parsing JSON data with jQuery .getJSON(). In this post I want to explain about 'How to use JSON callback and to display cross domain data with jQuery'. It's simple and useful. Take a look at this live demo
Backup your Web Projects Database using MySQL Administrator.
Some days back I had posted an article about MySQL WorkBench Usage. So in this post I want to explain how to take backup your database using MySQL Administrator. It's a part of MySQL GUI Tools.
It's very easy to use and using this we can take backup and restore web projects database.
Visual Database Design with MySQL Workbench
Few days back I received one request about Mysql WorkBench Usage. So in this post I want to explain with screen shots to create a visual database design, follow these steps.
I prepared this tutorial to improve your Database design Skills in Visual style.
Download Mysql Workbench
For example if we want to project like Twitter web site updating user profile, our database will have these entities:
1 - Users( User_name, password, email, some registration data....)
2 - Updates( Updates/Messages added by the Users)
Step 1 -> Add EER Diagram
Step 2 -> Place a New Table
Ok now... edit the table just right click.
You have to add Column and fix the Data types.
Set the table Primary Key in Column details
Same way you have to create Update table also.
Step 3->Link the both table with Relationship tools
Link the Updates table to Users table automatically generate Foreign key column.
Finally export project SQL script.
We can export ERR Diagram image *.png formate also.
If you feel free post a Comment..
Delicious Database Design: create tables and relationships with SQL
In the previous lesson we have seen how to design the relationship-entities model for a database to be used in a del.icio.us-like web site project. Our R-E model is:
Now we implement the database using SQL and phpMyAdmin. We crate a new database on phpMyAdmin and select the "SQL" tab. Copy and paste this SQL code into the form and click on execute button:
Create Relationships
To create relationships between database's table (for example between SHARE table and the other tables) you have to use the SQL code below:
where attribute_name_1 is the foreign key (generally, a field of type INTEGER)a and attribute_name_2 the primary key of the table of destination.
To force the referencial integrity between the data of database, you have to add this code:
Our database is now ready and we can implement it using JSP, PHP and MySQL
Now we implement the database using SQL and phpMyAdmin. We crate a new database on phpMyAdmin and select the "SQL" tab. Copy and paste this SQL code into the form and click on execute button:
CREATE TABLE USER (
user_id_pk INT NOT NULL AUTO_INCREMENT,
user_name VARCHAR(40),
email VARCHAR(40),
password VARCHAR(20),
user_date DATE,
PRIMARY KEY (user_id_pk)
) TYPE=INNODB;
CREATE TABLE SITE (
site_id_pk INT NOT NULL AUTO_INCREMENT,
url VARCHAR(250),
description LONGTEXT,
share_data DATA,
PRIMARY KEY
) TYPE=INNODB;
CREATE TABLE SHARE (
share_id_pk INT NOT NULL AUTO_INCREMENT,
user_id INT NOT NULL,
site_id INT NOT NULL,
submitted_by INT NOT NULL DEFAULT 0,
PRIMARY KEY (share_id_pk),
FOREIGN KEY (user_id) REFERENCES USER(user_id_pk) ON UPDATE CASCADE ON DELETE CASCADE,
FOREIGN KEY (site_id) REFERENCES SITE(site_id_pk) ON UPDATE CASCADE ON DELETE CASCADE
) TYPE=INNODB;
user_id_pk INT NOT NULL AUTO_INCREMENT,
user_name VARCHAR(40),
email VARCHAR(40),
password VARCHAR(20),
user_date DATE,
PRIMARY KEY (user_id_pk)
) TYPE=INNODB;
CREATE TABLE SITE (
site_id_pk INT NOT NULL AUTO_INCREMENT,
url VARCHAR(250),
description LONGTEXT,
share_data DATA,
PRIMARY KEY
) TYPE=INNODB;
CREATE TABLE SHARE (
share_id_pk INT NOT NULL AUTO_INCREMENT,
user_id INT NOT NULL,
site_id INT NOT NULL,
submitted_by INT NOT NULL DEFAULT 0,
PRIMARY KEY (share_id_pk),
FOREIGN KEY (user_id) REFERENCES USER(user_id_pk) ON UPDATE CASCADE ON DELETE CASCADE,
FOREIGN KEY (site_id) REFERENCES SITE(site_id_pk) ON UPDATE CASCADE ON DELETE CASCADE
) TYPE=INNODB;
Create Relationships
To create relationships between database's table (for example between SHARE table and the other tables) you have to use the SQL code below:
FOREIGN KEY (attribute_name_1) REFERENCES tableOfReference(attribute_name_2)
where attribute_name_1 is the foreign key (generally, a field of type INTEGER)a and attribute_name_2 the primary key of the table of destination.
To force the referencial integrity between the data of database, you have to add this code:
ON UPDATE CASCADE ON DELETE CASCADE
Our database is now ready and we can implement it using JSP, PHP and MySQL
Delicious Database Design: relationships
This lesson explains how to project a typical relationships-entities model for a database to be used in our web projects. My approach is:
1. Define database entities (tables)
2. Identify attributes for tables
3. Define relationships and cardinality between the instances (records) of tables.
Step 1: define database entities
The first step when you project a database is to identify all entities (tables). For example if we want to project a simplified del.icio.us-like web site, our database will have these entities:
1. - USER (to store data about users, email, password, nickname,...)
2. - SITE (to store data about the sites added by the users)
These are only the main entities required from our project but, take a mind, that we will add other tables to store data about relationships between istances (records) of these tables in case of cardinality (M:M), many to many (see Step 3).
Step 2: define attributes
The next step is to define attributes for the tables USER and SITE. In this semplified example we will have something like this:
Step 3: define database relationships
Our simple application del.icio.us-like works in this way: an user add a site that can be shared by other users. The relationship's cardinality between USER table and SITE table is:
In this case ( cardinality M:M) we have to add a new table (SHARE) that contains all possible combination between all instances of USER table and SITE table . In this new table, SHARE, to identify an user that share a site added by another user or by itself, we will add two Foreign Key:
Implement your database using SQL
Now, our database is ready to be implement with a DBMS (for example using MySQL). The next lesson will explains how to implement this database using SQL language.
Next Lessons:Create Tables and Relationships with SQL
1. Define database entities (tables)
2. Identify attributes for tables
3. Define relationships and cardinality between the instances (records) of tables.
Step 1: define database entities
The first step when you project a database is to identify all entities (tables). For example if we want to project a simplified del.icio.us-like web site, our database will have these entities:
1. - USER (to store data about users, email, password, nickname,...)
2. - SITE (to store data about the sites added by the users)
These are only the main entities required from our project but, take a mind, that we will add other tables to store data about relationships between istances (records) of these tables in case of cardinality (M:M), many to many (see Step 3).
Step 2: define attributes
The next step is to define attributes for the tables USER and SITE. In this semplified example we will have something like this:
USER
-----------
user_id_pk (Primary Key)
user_name
email
password
user_data (user signup date)
SITE
-----------
site_id_pk (Primary Key)
url
description
share_user (total number of users that share a site)
-----------
user_id_pk (Primary Key)
user_name
password
user_data (user signup date)
SITE
-----------
site_id_pk (Primary Key)
url
description
share_user (total number of users that share a site)
Step 3: define database relationships
Our simple application del.icio.us-like works in this way: an user add a site that can be shared by other users. The relationship's cardinality between USER table and SITE table is:
USER > SITE (M:M) - Many to Many (an user can add many sites).
SITE > USER (M:M) - Many to Many (a site can be shared by many users).
SITE > USER (M:M) - Many to Many (a site can be shared by many users).
In this case ( cardinality M:M) we have to add a new table (SHARE) that contains all possible combination between all instances of USER table and SITE table . In this new table, SHARE, to identify an user that share a site added by another user or by itself, we will add two Foreign Key:
SHARE
-----------
share_id_pk (Primary Key)
user_id (Foreign Key > USER)
site_id (Foreign Key >SITE)
submitted_by (boolean: flag only if the current user has submitted the site)
-----------
share_id_pk (Primary Key)
user_id (Foreign Key > USER)
site_id (Foreign Key >SITE)
submitted_by (boolean: flag only if the current user has submitted the site)
Implement your database using SQL
Now, our database is ready to be implement with a DBMS (for example using MySQL). The next lesson will explains how to implement this database using SQL language.
Next Lessons:Create Tables and Relationships with SQL