Databases

CRUD

Create, Read, Update, Delete

 

Read: SQL SELECT

SELECT * FROM table

WHERE colum1 = ‘some_text’

ORDER BY column, column2 ASC;

 

WRITE: SQL INSERT

INSERT INTO table (column1, colum2, column3)

VALUES (val1, val2, val3);

 

UPDATE: SQL UPDATE

UPDATE table

SET column1 = ‘some_text’

WHERE id = 1;

 

DELETE: DQL DELETE

 

DELETE FROM table

WHERE id=1;

 

Creating a database, table and table fields

 

CREATE DATABASE widget_corp;

USE widget_corp;

CREATE TABLE subjects (

id int(11) NOT NULL auto_increment,

menu_name varchar(30) NOT NULL,

position int(3) NOT NULL,

visible tinyint(1) NOT NULL,

PRIMARY KEY (id)

);

 

Adding a row into the table

 

INSERT INTO subjects ( menu_name, position, visible)

VALUES( 'About Widget Corp', 1, 1);

 

INSERT INTO subjects ( menu_name, position, visible)

VALUES( 'Products', 2 , 1);

Reading from the table

 

SELECT * FROM subjects;

 

Create Blueprint for Application

Create Database: phpMyAdmin

 

Database: “widget_corp”

Type: ISAM

 

subjects

Name	Type	Collation	Null	AI	Primary
id	int(11)			x	x
menu_name	varchar(30)	utf8_general_ci
position	int(3)
visible	tinyint(1)

 

pages

Name	Type	Collation	Null	AI	Primary
id	int(11)			x	x
subject_id	int(11)
menu_name	varchar(30)	utf8_general_ci
position	int(3)
visible	tinyint(1)
content	text

 

Users

Name	Type	Collation	Null	AI	Primary
id	int(11)			x	x
user_name	varchar(50)	utf8_general_ci
hashed_password	varchar(40)	utf8_general_ci

 

Relational Database

 

 

 

Project Setup

 

Database Connection

CRUD

Create

Read

Update

Delete

 

Menu Generation

 

Query, Result, Result_set, Item loop

 

Query ORDER BY ….ASC