mysq

PHP MySQLi Create Table Example

Example

<?php  
$host = 'localhost:3306';  
$user = '';  
$pass = '';  
$dbname = 'test';  
  
$conn = mysqli_connect($host, $user, $pass,$dbname);  
if(!$conn){  
  die('Could not connect: '.mysqli_connect_error());  
}  
echo 'Connected successfully<br/>';  
  
$sql = "create table emp5(id INT AUTO_INCREMENT,name VARCHAR(20) NOT NULL,  
emp_salary INT NOT NULL,primary key (id))";  
if(mysqli_query($conn, $sql)){  
 echo "Table emp5 created successfully";  
}else{  
echo "Could not create table: ". mysqli_error($conn);  
}  
  
mysqli_close($conn);  
?>

Output:

Connected successfully Table emp5 created successfully

Database Concept

DBMS ER Model

DBMS Relational model

DBMS Normalization

DBMS Basic SQL

DBMS DML Command

DBMS DCL Command

Advanced SQL

A VIEW in SQL is a logical subset of data from one or more tables. View is used to restrict data access.

Syntax for creating a View,

CREATE or REPLACE VIEW view_name 
    AS
    SELECT column_name(s)
    FROM table_name
    WHERE condition

As you may have understood by seeing the above SQL query, a view is created using data fetched from some other table(s). It’s more like a temporary table created with data.

Creating a VIEW

Consider following Sale table,

oid	order_name	previous_balance	customer
11	ord1	2000	Alex
12	ord2	1000	Adam
13	ord3	2000	Abhi
14	ord4	1000	Adam
15	ord5	2000	Alex

SQL Query to Create a View from the above table will be,

CREATE or REPLACE VIEW sale_view 
AS 
SELECT * FROM Sale WHERE customer = 'Alex';

The data fetched from SELECT statement will be stored in another object called sale_view. We can use CREATE and REPLACE seperately too, but using both together works better, as if any view with the specified name exists, this query will replace it with fresh data.

Displaying a VIEW

The syntax for displaying the data in a view is similar to fetching data from a table using a SELECT statement.

SELECT * FROM sale_view;

Force VIEW Creation

FORCE keyword is used while creating a view, forcefully. This keyword is used to create a View even if the table does not exist. After creating a force View if we create the base table and enter values in it, the view will be automatically updated.

Syntax for forced View is,

CREATE or REPLACE FORCE VIEW view_name AS
    SELECT column_name(s)
    FROM table_name
    WHERE condition;

Update a VIEW

UPDATE command for view is same as for tables.

Syntax to Update a View is,

UPDATE view-name SET VALUE
WHERE condition;

NOTE: If we update a view it also updates base table data automatically.

Read-Only VIEW

We can create a view with read-only option to restrict access to the view.

Syntax to create a view with Read-Only Access

CREATE or REPLACE FORCE VIEW view_name AS
    SELECT column_name(s)
    FROM table_name
    WHERE condition WITH read-only;

The above syntax will create view for read-only purpose, we cannot Update or Insert data into read-only view. It will throw an error.

Types of View

There are two types of view,

Simple View
Complex View

Simple View	Complex View
Created from one table	Created from one or more table
Does not contain functions	Contain functions
Does not contain groups of data	Contains groups of data

< prev

Tag: mysq

PHP MySQL Create Table

PHP Tutorial

Control Statement

PHP Functions

PHP Arrays

PHP Strings

PHP Math

PHP Form

PHP Include

State Management

PHP File

Upload Download

PHP OOPs Concepts

PHP MySQLi

PHP Topics

PHP Mail