Database:

MySQL in PHP
v3.0

1
 Agenda

1. Changing and deleting data in tables

2. Table modification
3. Joining tables
4. Relations between tables
5. Advanced SQL

2
 Changing and
deleting data in
tables

3
 Changing the data values

Replace data from a table using the UPDATE UPDATE is usually used with WHERE clause
query: (otherwise all data from the table will be changed).
UPDATE table_name
SET
col_name_1 = value1,
col_name_2 = value2,
...
WHERE col_name_3 = some_value;

4
 Changing the data values
PHP
UPDATE users $sql = "UPDATE users
SET user_name="Gregory" SET user_name='Gregory'
WHERE user_id=2; WHERE user_id=2";
SELECT * FROM users; try {
+---------+-----------+ $result = $conn->query($sql);
| user_id | user_name | echo "The entry was updated.";
+---------+-----------+ } catch (PDOException $e) {
| 1 | William | echo "Error; . $e->getMessage();
| 2 | Gregory | }
| ... |
+---------+-----------+
4 rows in set (0.00 sec)

5
 Changing the data values - prepared statement
PHP PHP - data from a form
$sql = "UPDATE users $userName = $_POST['userName'];
SET user_name='Gregory' $userId = $_POST['userId'];
WHERE user_id=2"; $sql = "UPDATE users
SET user_name=:userName
try { WHERE user_id=:userId";
$result = $conn->query($sql); try {
echo "The entry was updated."; $stmt = $conn->prepare($sql);
} catch (PDOException $e) { $stmt->execute([
echo "Error: " . $e->getMessage(); 'userName' => $userName,
} 'userId' => $userId
]);
echo "The entry was updated.";
} catch (PDOException $e) {
echo "Error: " . $e->getMessage();
}

6
 Deleting data from table

We delete data from a table using the DELETE DELETE is usually used with WHERE clause
query: (otherwise all data from the table will be changed).
DELETE FROM table_name
WHERE some_column = some_value;

7
 Deleting data from table

DELETE FROM users $sql = "DELETE FROM users

WHERE user_name="Gregory"; WHERE user_name='Gregory'";
try {
SELECT * FROM users; $result = $conn->query($sql);
+---------+-----------+ echo "There were " .
| user_id | user_name | $result->rowCount() .
+---------+-----------+ " entries removed";
| 1 | William | echo "The entry was removed.";
| 3 | Paul | } catch (PDOException $e) {
| 4 | John | echo "Error: " . $e->getMessage();
+---------+-----------+ }
3 rows in set (0.00 sec)

8
 Deleting data from table - prepared statement

$sql = "DELETE FROM users $userName = $_POST['userName'];

WHERE user_name='Gregory'"; $sql = "DELETE FROM users
try { WHERE user_name=:userName";
$result = $conn->query($sql); try {
echo "There were " . $stmt = $conn->prepare($sql);
$result->rowCount() . $stmt->execute([
" entries removed"; 'userName' => $userName
echo "The entry was removed."; ]);
} catch (PDOException $e) { echo "There were " .
echo "Error: " . $e->getMessage(); $stmt->rowCount() .
} " entries removed";
echo "The entry was removed.";
} catch (PDOException $e) {
echo "Error: " . $e->getMessage();
}

9
 Exercises

Exercise time
Day 2
Changing and deleting data

10
 Table
modification

11
 Table modification

The appearance of the table (number of columns, data stored in them) can be changed using the ALTER
TABLE command:
#Adding a new column:
ALTER TABLE table_name ADD column_name datatype;

#Removing the table:
ALTER TABLE table_name DROP COLUMN column_name;

#Changing the type of data kept in the column:
ALTER TABLE table_name MODIFY COLUMN column_name new_datatype;

12
 Table modification

mysql> DESCRIBE users;

+---------+------------+-----+-----+---------+----------------+
|Field |Type | Null| Key | Default | Extra |
+---------+------------+-----+-----+---------+----------------+
|user_id |int(11) | NO | PRI | NULL | auto_increment |
|user_name|varchar(255)| YES | | NULL | |
+---------+------------+-----+-----+---------+----------------+
2 rows in set (0.00 sec)

13
 Table modification

mysql> ALTER TABLE users MODIFY COLUMN user_name varchar(30);

mysql> ALTER TABLE users ADD email varchar(30);
mysql> DESCRIBE users;
+---------+------------+-----+-----+---------+----------------+
|Field |Type | Null| Key | Default | Extra |
+---------+------------+-----+-----+---------+----------------+
|user_id |int(11) | NO | PRI | NULL | auto_increment |
|user_name|varchar(30) | YES | | NULL | |
|email |varchar(30) | YES | | NULL | |
+---------+------------+-----+-----+---------+----------------+
3 rows in set (0.00 sec)

14
 Deleting table

The table can be removed from our database by We can destroy the entire database using the
using the DROP TABLE query: DROP DATABASE query ;
DROP TABLE table_name; DROP DATABASE db_name;

15
 Exercises

Exercise time
Day 2
Table modification

16
Joining tables

17
 Joining tables

SELECT column_name(s)
The results of two (or more) tables at once can be
FROM table1
obtained by using the construction JOIN ... ON ...
JOIN table2
These are the four possibilities to join tables: ON table1.col_name=table2.col_name;
INNER,
LEFT,
RIGHT,
FULL.

18
 Joining tables
CREATE TABLE customers(
customer_id int NOT NULL AUTO_INCREMENT,
name varchar(255) NOT NULL,
PRIMARY KEY(customer_id)
);

CREATE TABLE addresses(
address_id int NOT NULL AUTO_INCREMENT,
customer_id int,
street varchar(255),
PRIMARY KEY(address_id)
);

19
 INNER JOIN

INNER JOIN (or usual JOIN) is a main type of

joining tables.
As a result, it gives the combined records from
both tables, which have a common element
indicated in the query after the ON command.
Records without a common element will not be
returned.

20
 INNER JOIN

Let's assume that we have tables with the following data:

SELECT * FROM customers; SELECT * FROM addresses;

+-------------+-------+ +----------+-----------+---------------+
| customer_id | name | |address_id|customer_id|street |
+-------------+-------+ +----------+-----------+---------------+
| 1 | Jack | | 1| 1|Jack's address |
| 3 | Paul | | 2| 3|Paul's address |
| 4 | Jacob | | 3| 10|Wrong address |
+-------------+-------+ +----------+-----------+---------------+

21
 INNER JOIN
SELECT * FROM customers
JOIN addresses
ON customers.customer_id = addresses.customer_id;

+-------------+-------+------------+-------------+---------------+
| customer_id | name | address_id | customer_id |street |
+-------------+-------+------------+-------------+---------------+
| 1 | Jack | 1 | 1 |Jack's address |
| 3 | Paul | 2 | 3 |Paul's address |
+-------------+-------+------------+-------------+---------------+

22
 INNER JOIN
SELECT * FROM customers
JOIN addresses
ON customers.customer_id = addresses.customer_id;

+-------------+-------+------------+-------------+---------------+
| customer_id | name | address_id | customer_id |street |
+-------------+-------+------------+-------------+---------------+
| 1 | Jack | 1 | 1 |Jack's address |
| 3 | Paul | 2 | 3 |Paul's address |
+-------------+-------+------------+-------------+---------------+

Returned records are those for which both tables have a common (of the same value) customer_id
column.

23
 LEFT JOIN

LEFT JOIN returns all rows in the left table. Data

from the right table will only be included in rows
that meet the condition.

24
 LEFT JOIN

SELECT * FROM customers

LEFT JOIN addresses
ON customers.customer_id = addresses.customer_id;

+-------------+-------+------------+-------------+----------------+
| customer_id | name | address_id | customer_id |street |
+-------------+-------+------------+-------------+----------------+
| 1 | Jack | 1 | 1 |Jack's address |
| 3 | Paul | 2 | 3 |Paul's address |
| 4 | Jacob | NULL | NULL |NULL |
+-------------+-------+------------+-------------+----------------+

25
 LEFT JOIN

SELECT * FROM customers

LEFT JOIN addresses
ON customers.customer_id = addresses.customer_id;

+-------------+-------+------------+-------------+----------------+
| customer_id | name | address_id | customer_id |street |
+-------------+-------+------------+-------------+----------------+
| 1 | Jack | 1 | 1 |Jack's address |
| 3 | Paul | 2 | 3 |Paul's address |
| 4 | Jacob | NULL | NULL |NULL |
+-------------+-------+------------+-------------+----------------+

Returned are all the records from the left table (customers) and the corresponding records in the right
table, which, if they do not exist, are returned as null.

26
 RIGHT JOIN

RIGHT JOIN returns all rows in the right table.

Data from the left table will only be included in
rows that meet the condition.

27
 RIGHT JOIN

SELECT * FROM customers

RIGHT JOIN addresses
ON customers.customer_id = addresses.customer_id;

+-------------+-------+------------+-------------+----------------+
| customer_id | name | address_id | customer_id |street |
+-------------+-------+------------+-------------+----------------+
| 1 | Jack | 1 | 1 |Jack's address |
| 3 | Paul | 2 | 3 |Paul's address |
| NULL | NULL | 3 | 10 |Wrong address |
+-------------+-------+------------+-------------+----------------+

28
 RIGHT JOIN

SELECT * FROM customers

RIGHT JOIN addresses
ON customers.customer_id = addresses.customer_id;

+-------------+-------+------------+-------------+----------------+
| customer_id | name | address_id | customer_id |street |
+-------------+-------+------------+-------------+----------------+
| 1 | Jack | 1 | 1 |Jack's address |
| 3 | Paul | 2 | 3 |Paul's address |
| NULL | NULL | 3 | 10 |Wrong address |
+-------------+-------+------------+-------------+----------------+

Returned are all the records from the right table (customers) and the corresponding records in the left
table, which, if they do not exist, are returned as null.

29
 Relations
between tables

30
 One-to-one relationship

A relation in which one element from a table can

be combined with only one element from another
table.
The customer can only have one address.
The address must have one customer.

31
 One-to-one relationship

The one-to-one relationship is created by making the primary key of an entry dependent on the primary
key of another object. The primary key cannot exist without this relation. In our case it will be typing the
client's primary key as the primary address key.
CREATE TABLE customers(
customer_id int NOT NULL AUTO_INCREMENT,
name varchar(255) NOT NULL,
PRIMARY KEY(customer_id)
);
CREATE TABLE addresses(
customer_id int NOT NULL, street varchar(255),
PRIMARY KEY(customer_id),
FOREIGN KEY(customer_id) REFERENCES customers(customer_id)
ON DELETE CASCADE
);

32
 One-to-one relationship

The one-to-one relationship is created by making the primary key of an entry dependent on the primary
key of another object. The primary key cannot exist without this relation. In our case it will be typing the
client's primary key as the primary address key.
CREATE TABLE customers(
customer_id int NOT NULL AUTO_INCREMENT,
name varchar(255) NOT NULL,
PRIMARY KEY(customer_id)
);
CREATE TABLE addresses(
customer_id int NOT NULL, street varchar(255),
PRIMARY KEY(customer_id),
FOREIGN KEY(customer_id) REFERENCES customers(customer_id)
ON DELETE CASCADE
);

Relationship between the primary keys of two tables

33
 One-to-one relationship

The one-to-one relationship is created by making the primary key of an entry dependent on the primary
key of another object. The primary key cannot exist without this relation. In our case it will be typing the
client's primary key as the primary address key.
CREATE TABLE customers(
customer_id int NOT NULL AUTO_INCREMENT,
name varchar(255) NOT NULL,
PRIMARY KEY(customer_id)
);
CREATE TABLE addresses(
customer_id int NOT NULL, street varchar(255),
PRIMARY KEY(customer_id),
FOREIGN KEY(customer_id) REFERENCES customers(customer_id)
ON DELETE CASCADE
);

ON DELETE CASCADE is an optional parameter.

34
 FOREIGN KEY

The FOREIGN KEY attribute added to a column simply says that this column indicates the primary
key of another table.
It speeds up the work of our database and makes it impossible to enter incorrect data (e.g. it will not
allow to enter a key that does not appear in the second table).

35
 ON DELETE CASCADE

While building a relationship we can add the ON DELETE CASCADE option. This option causes the
deletion of record in the main table, which is indicated by foreign keys, to automatically delete all records
in other tables, which are connected to it by a relation.
For example: if we delete a user, we want all his addresses in the system to be thrown out together.
If we do not add this option, SQL will not allow us to delete a record as long as any entries in other tables
are related to it.
For example: SQL will not allow us to delete a user as long as there are records assigned to him in the
address table.

36
 One-to-one relationship
INSERT INTO customers(name) VALUES ("John"), ("Jack"), ("William");
INSERT INTO addresses(customer_id, street)
VALUES (1, "John's street"), (2, "Jacob's street");

SELECT * FROM customers
JOIN addresses
ON customers.customer_id = addresses.customer_id
WHERE customers.customer_id = 2;

+-------------+------+-------------+--------------+
| customer_id | name | customer_id | street |
+-------------+------+-------------+--------------+
| 2 | Jack | 2 | Jack's street|
+-------------+------+-------------+--------------+

37
 FOREIGN KEY - adding elements
SELECT * FROM customers;

+-------------+--------+
| customer_id | name |
+-------------+--------+
| 1 | John |
| 3 | William|
+-------------+--------+

INSERT INTO addresses(customer_id, street) VALUES(5, "xxx");

ERROR 1452 (23000): Cannot add or update a child row:

a foreign key constraint fails ('db_name'.'addresses',
CONSTRAINT 'addresses_ibfk_1' FOREIGN KEY ('customer_id')
REFERENCES 'customers' ('customer_id'))

38
 FOREIGN KEY - adding elements
SELECT * FROM customers;

+-------------+--------+
| customer_id | name |
+-------------+--------+
| 1 | John |
| 3 | William|
+-------------+--------+

INSERT INTO addresses(customer_id, street) VALUES(5, "xxx");

ERROR 1452 (23000): Cannot add or update a child row:

a foreign key constraint fails ('db_name'.'addresses',
CONSTRAINT 'addresses_ibfk_1' FOREIGN KEY ('customer_id')
REFERENCES 'customers' ('customer_id'))

If there is no primary key in the second table that you want to refer to with an external key, then SQL will
return the error.
In our case, the customers table does not have a record customer_id=5.
39
 FOREIGN KEY - deleting elements

SELECT * FROM addresses; DELETE FROM customers

WHERE customer_id = 2;
+-------------+---------------+
| customer_id | street |
+-------------+----------------+
| 1 | John's street |
+-------------+----------------+
| 2 | Jacob's street |
+-------------+----------------+

40
 FOREIGN KEY - deleting elements

SELECT * FROM addresses; DELETE FROM customers

WHERE customer_id = 2;
+-------------+---------------+
| customer_id | street | Calling such a query will automatically remove the
+-------------+----------------+ associated record(s) from the addresses table
| 1 | John's street | because they are bound by a foreign key and ON
+-------------+----------------+ DELETE CASCADE.
| 2 | Jacob's street |
+-------------+----------------+

41
 Exercises

Exercise time
Day 2
One-to-one relationship

42
 One-to-many relationship

A relation in which one element from a table can

be combined with many elements from another
table.
A customer may have many orders. An order must
have only one customer.

43
 One-to-many relationship

Relationship one to many is created by adding an additional column, in which we hold the primary key of
the object from the second table.

CREATE TABLE orders(

order_id int NOT NULL AUTO_INCREMENT,
customer_id int NOT NULL,
order_details varchar(255),
PRIMARY KEY(order_id),
FOREIGN KEY(customer_id)
REFERENCES customers(customer_id)
);

44
 One-to-many relationship
INSERT INTO orders(customer_id, order_details)
VALUES(3, "Order1"), (3, "Order2"), (1, "Order3");

SELECT * FROM customers
JOIN orders
ON customers.customer_id = orders.customer_id
WHERE customers.customer_id = 3;

+-------------+--------+----------+------------+---------------+
| customer_id | name | order_id | customer_id|order_details |
+-------------+---------+----------+------------+--------------+
| 3 | William | 1 | 3|Order1 |
| 3 | William | 2 | 3|Order2 |
+-------------+---------+----------+------------+--------------+

45
 Exercises

Exercise time
Day 2
One-to-many relationship

46
 Many-to-many relationship

A relation in which many elements from a table can

be combined with many elements from another
table.
For example, an order has many items in it, an
item can be in many orders.

47
 Many-to-many relationship

In SQL, it is not possible to do something like a

many-to-many relationship. Many-to-many
relationship are created by adding an additional
table that describes the relationship and that has
two relationships one to many.
This table can hold more information than just the
primary keys of its relationship.

48
 Many-to-many relationship
CREATE TABLE items(
item_id int NOT NULL AUTO_INCREMENT,
description varchar(255),
PRIMARY KEY(item_id)
);

INSERT INTO items(description)
VALUES ("Item 1"),
("Item 2"),
("Item 3");

49
 Many-to-many relationship
CREATE TABLE items_orders(
item_id int NOT NULL,
order_id int NOT NULL,
PRIMARY KEY(id),
FOREIGN KEY(order_id) REFERENCES orders(order_id),
FOREIGN KEY(item_id) REFERENCES items(item_id)
);

50
 Many-to-many relationship
CREATE TABLE items_orders(
item_id int NOT NULL,
order_id int NOT NULL,
PRIMARY KEY(id),
FOREIGN KEY(order_id) REFERENCES orders(order_id),
FOREIGN KEY(item_id) REFERENCES items(item_id)
);

The name of the auxiliary table to the relation is usually created by combining the names of 2 tables, the
relation of which we want to do many-to-many relationship.

51
 Many-to-many relationship
CREATE TABLE items_orders(
item_id int NOT NULL,
order_id int NOT NULL,
PRIMARY KEY(id),
FOREIGN KEY(order_id) REFERENCES orders(order_id),
FOREIGN KEY(item_id) REFERENCES items(item_id)
);

We add also 2 one-to-many relations

52
 Many-to-many relationship
INSERT INTO items_orders(order_id, item_id)
VALUES (1,1), (2,1), (2,2);
SELECT * FROM orders
JOIN items_orders
ON orders.order_id=items_orders.order_id
JOIN items
ON items.item_id=items_orders.item_id;

+--------+-----------+-------------+-------+--------+-------+-----------+
|order_id|customer_id|order_details|item_id|order_id|item_id|description|
+--------+-----------+-------------+-------+--------+-------+-----------+
| 1 | 3 |Order1 | 1 | 1 | 1 | Item 1 |
| 2 | 3 |Order2 | 1 | 2 | 1 | Item 1 |
| 2 | 3 |Order2 | 2 | 2 | 2 | Item 2 |
+--------+-----------+-------------+-------+--------+-------+-----------+

53
 Exercises

Exercise time
Day 2
Many-to-many relationship

54
Advanced SQL

55
 SQL built-in functions

SQL language also implements many functions that make it easier to work on strings, numbers and
dates.
Full list of these functions (with descriptions) can be found here:
http://www.w3schools.com/sql/sql_ref_mysql.asp

56
 Indexes

Indexes are special search tables that speed up the search of a table in one of the columns.
We should use them when many WHERE clauses depend on this column.
Too many indexes can slow down the operation of the database - that's why you should pay attention to
them and add them after a thorough analysis of the database.
The operation of indexes is most visible in the query execution speed if the index is added to a column
with a numeric type.

CREATE INDEX index_name

ON table_name (column_name);

57
 Transactions

SQL also allows for transactions. This is a group of SQL queries called entirely from the database. If any
of these queries fails, the database returns to the state before the transaction.
This is an advanced mechanism, which occurs e.g. in banking systems.
A query in transactions means that all or none of the queries must be executed correctly.
More about transactions:
http://www.tutorialspoint.com/sql/sql-transactions.htm
http://www.w3resource.com/mysql/mysql-transaction.php

An example of a transaction (in SQL sense not literally) can be a currency exchange e.g. PLN -> EUR
where the balance of EUR will be credited ONLY when the balance of PLN is sufficient. Look at the code
on the next slide.

58
 Transactions
An example of a transaction:
try {
$conn->beginTransaction();
$result = $conn->query("UPDATE pln_acc SET bal=bal-199 WHERE bal>=199");
if ($result->numRows() > 0){
$conn->query("UPDATE eur_acc SET bal=bal+50");
$conn->commit();
} else {
$conn->rollBack();
}
} catch (PDOException $e) {
$conn->rollBack();
echo "Error: " . $e->getMessage();
}

59
 Transactions
An example of a transaction:
try {
$conn->beginTransaction();
$result = $conn->query("UPDATE pln_acc SET bal=bal-199 WHERE bal>=199");
if ($result->numRows() > 0){
$conn->query("UPDATE eur_acc SET bal=bal+50");
$conn->commit();
} else {
$conn->rollBack();
}
} catch (PDOException $e) {
$conn->rollBack();
echo "Error: " . $e->getMessage();
}

We inform database that further inquiries will be a transaction.

60
 Transactions
An example of a transaction:
try {
$conn->beginTransaction();
$result = $conn->query("UPDATE pln_acc SET bal=bal-199 WHERE bal>=199");
if ($result->numRows() > 0){
$conn->query("UPDATE eur_acc SET bal=bal+50");
$conn->commit();
} else {
$conn->rollBack();
}
} catch (PDOException $e) {
$conn->rollBack();
echo "Error: " . $e->getMessage();
}

We check whether the balance change has been successful.

61
 Transactions
An example of a transaction:
try {
$conn->beginTransaction();
$result = $conn->query("UPDATE pln_acc SET bal=bal-199 WHERE bal>=199");
if ($result->numRows() > 0){
$conn->query("UPDATE eur_acc SET bal=bal+50");
$conn->commit();
} else {
$conn->rollBack();
}
} catch (PDOException $e) {
$conn->rollBack();
echo "Error: " . $e->getMessage();
}

If so, we change the balance of the second currency and confirm both queries in the transaction.

62
 Transactions
An example of a transaction:
try {
$conn->beginTransaction();
$result = $conn->query("UPDATE pln_acc SET bal=bal-199 WHERE bal>=199");
if ($result->numRows() > 0){
$conn->query("UPDATE eur_acc SET bal=bal+50");
$conn->commit();
} else {
$conn->rollBack();
}
} catch (PDOException $e) {
$conn->rollBack();
echo "Error: " . $e->getMessage();
}

If not, it means that we do not have sufficient funds, we revoke the transaction.

63
 Transactions
An example of a transaction:
try {
$conn->beginTransaction();
$result = $conn->query("UPDATE pln_acc SET bal=bal-199 WHERE bal>=199");
if ($result->numRows() > 0){
$conn->query("UPDATE eur_acc SET bal=bal+50");
$conn->commit();
} else {
$conn->rollBack();
}
} catch (PDOException $e) {
$conn->rollBack();
echo "Error: " . $e->getMessage();
}

In the event of any error, we reverse the transaction.

64
 Triggers

In SQL it is possible to create triggers.

These are functions that will be started automatically if a situation specified by us occurs (usually
DELETE, INSERT, DELETE).
More about triggers:
http://www.tutorialspoint.com/plsql/plsql_triggers.htm
http://www.sqlteam.com/article/an-introduction-to-triggers-part-i

65