Mysql Database 1.pdf

Database Programming

Outline • • • • • • • • • • • • •

MySQL How it works Data Types Data Definition Language Data Manipulation Language MySQL Comments Where clause Comparison Operators ORDER Clause LIMIT Clause Escaping Characters MySQL Joins Using MySQL in PHP

• • • • • • • • • •

Methods to use Connecting to a database Querying a database Exercise Exercise Solution Prepared Statements Tips and Tricks Resources Course Project Assignment

MySQL MySQL is a Relational Database Management System (RDBMS) which stores data in a structured way ( in tables ). It allows for retrieving data using Structured Query Language (SQL). Request

Query

Result set

Web Server ( Apache with PHP )

MySQL Server

Response

Web Browser

How it works How tables look like ? id

name

salary

job_title

1

Ahmed

100

Developer

2

Mohamed

200

Doctor

3

Sara

300

Engineer

4

Marwa

400

Designer

How it works • In MySQL, we can have multiple databases, every database has an unlimited amount of tables. • A table holds data about some entity ( person, employee, etc ). • The table consists of number of columns, each column can have a data type ( char, int, float, etc… )

Data Types Size

Name

1

TINYINT

2

SMALLINT

3

MEDIUMINT

4

INT

8

BIGINT

4

FLOAT

8

DOUBLE

arbitrary

DECIMAL[(M,[D])] Stored as string

M

CHAR(M)

M

VARCHAR(M)

Range -128 TO 127 [0 to 255 if UNSIGNED] -32,768 to 32,767 [0 to 65,535] -8,388,608 to 8,388,607 [0 to 16,777,215] -/+2.147E+9 [0 to 4.294E+9] -/+9.223E+18 [0 to 18.45E+18] p=0-24 Min=+/-2.225E-308 Max=+/-1.798E+308

Example 10 30000 8000000 80000000 80000000000 4.333333 .000000000000000000 003

Max Range = DOUBLE range 33333.4444 M=0-255 Characters, FIXED. Hello There Right padded with spaces. M=0-65,535 Characters Hello There M=0-255
Data Types Size arbitrary arbitrary arbitrary arbitrary arbitrary

Name TINYTEXT TEXT MEDIUMTEXT LONGTEXT BINARY[(M)]

Range 0-255 Characters 0-65,535 Char's 0-16,777,215 Char's 0-4,294,967,295 Char's M=0-255 bytes, FIXED. 0-65,535 bytes M=0-255
Example Hello There Hello There Hello There Hello There

arbitrary

VARBINARY(M)

arbitrary arbitrary arbitrary arbitrary

TINYBLOB BLOB MEDIUMBLOB LONGBLOB

3

DATE

8

DATETIME

3

TIME

"-838:59:59" - "838:59:59" 10:12:01

4

TIMESTAMP

19700101000000 2037+

2010-01-01 2010-01-01 10:12:01

19700101000000

Data Definition Language DDL in the language MySQL use to define its data structures ( databases and tables ).

Example : CREATE TABLE employees ( id INT PRIMARY KEY, first_name CHAR(50) NULL, last_name CHAR(75) NOT NULL, salary INT NOT NULL DEFAULT 0, dateofbirth DATE NULL );

Data Definition Language Altering tables: Adding or removing columns or changing the data types of columns. Examples: ALTER TABLE employees ADD salary INT(11); ALTER TABLE employees MODIFY salary FLOAT(10,5); ALTER TABLE employees DROP salary;

Data Definition Language Removing a table: DROP table employees Removing a database: DROP DATABASE db_name;

Data Manipulation Language DML is the language used by MySQL to manipulate data ( select, insert, update, delete )

SELECT statement: It is used to get data from a table. Example : SELECT * FROM employees The previous statement should select all the data from the employees table.

SELECT first_name FROM employees This should return the column “first_name”

Data Manipulation Language INSERT statements They are used to insert a new row into a table.

Examples: INSERT INTO employees SET id=1, first_name='John', last_name='Steve', dateofbirth = '1970-01-01‘

INSERT INTO employees (id, first_name, last_name, dateofbirth ) values ( '2', ‘John', 'Steve', '1970-01-01' )

Data Manipulation Language UPDATE statements These statements are used to update (change) data.

Example: UPDATE employees SET first_name = ‘Mohamed’

This will update all the rows in the employees table setting the “first_name” to “Mohamed”

Data Manipulation Language DELETE statements These statements are used delete rows from a table.

Example: DELETE FROM employees;

This will delete all rows from employees table.

MySQL Comments Just like any other programming language, MySQL has ways to comment code.

1. -- one line comment 2. # one line comment 3. /* Multi Line Comment */

Where clause Where clauses are used to restrict the operations to a limited number of rows (the rows the satisfy this condition).

Examples: SELECT * FROM employees WHERE first_name = ‘Mohamed’

DELETE FROM employees WHERE first_name = ‘John’ Update employees SET first_name = ‘Mohamed’ WHERE last_name = ‘Ahmed’

Comparison Operators Operator

Description

=

Equals

!=

Not Equals

<>

Not Equals

>=

Greater than or equal

<=

Less than or equal

IS NULL

True if the field is not null, false otherwise

IS NOT NULL

True if the field is null, false otherwise

BETWEEN … AND …

Checks if the value is between the specified numbers

NOT BETWEEN … AND …

Checks if the value is not between the specified numbers

IN ( …, …, …, )

Checks if the value is in the specified list

NOT IN

Checks if the value is not in the specified list

LIKE

Checks if the value is like the specified string.

NOT LIKE

Checks if the value is not like the specified string. See next slide

Comparison Operators Examples SELECT * FROM employees WHERE salary <= 20 SELECT * FROM employees WHERE salary BETWEEN 10 AND 20 SELECT first_name FROM employees WHERE last_name LIKE ‘%ham%’ # percentage ‘%’ matches zero or more characters SELECT * FROM employees WHERE first_name NOT LIKE ‘__ham__’ # underscore matches 1 character SELECT * FROM employees WHERE salary IN ( 10, 20, 30 )

ORDER Clause ORDER clauses are used to order the result set. Examples: SELECT * FROM employees ORDER BY first_name ASC # ASC = ascendingly

SELECT * FROM employees ORDER BY last_name DESC # DESC = descendingly

LIMIT Clause Limit clauses are used to limit the number of rows in a result set.

Examples: SELECT * FROM employees LIMIT 1 # gets only the first row SELECT * FROM employees LIMIT 1 , 2 # gets 2 rows starting from the first row ( it gets the second and third rows )

Escaping Characters MySQL is similar to PHP when escaping characters : SELECT * FROM employees WHERE first_name = ‘That\’s me’ SELECT * FROM employees WHERE first_name = “That\”s me”

But it adds another method of escaping : SELECT * FROM employees WHERE first_name = ‘That’’s me’

SELECT * FROM employees WHERE first_name = “That””s me”

MySQL Joins MySQL joins allow us to get data from multiple tables in the same query. Suppose we have the following tables: Employees table id

name

salary

job_title

1

Ahmed

100

Developer

2

Mohamed

200

Doctor

3

Sara

300

Engineer

4

Marwa

400

Designer

id

model

CC

employee_id

1

BMW

1600

1

2

Hyundai

1600

2

3

Honda

1800

3

4

Fiat

1600

4

Cars Table

MySQL Joins The previous tables are employees and cars tables. Every car has an owner which is an employee. If we need to get data from these 2 tables, we will need to join them like the following : SELECT employees.name, cars.model FROM employees, cars WHERE employee.id = cars.id Result set

name

model

Ahmed

BMW

Mohamed

Hyundai

Sara

Honda

Marwa

Fiat

Using MySQL in PHP To achieve dynamism, a connection between a language and database is essential. PHP has a great MySQL support. There are various methods/ approaches that we could use to connect to MySQL in PHP.

Methods to use We have 3 methods of code styles that we can use to connect to MySQL in PHP.

1- Procedural approach. 2- MySQLi approach. 3- PDO ( PHP Data Objects ) approach. We Will user PDO because it is OOP and it supports named parameters and exceptions plus it has a good abstraction model that supports many drivers.

Connecting to database getMessage(); } ?>

Querying a database Here we will need to display the last name of the people with first_name = ‘Mohamed’.

query($sql); foreach ($result as $row) { print $row['last_name'] . "
"; } ?>

Querying a database Inserting a new row into employees table : query($sql); ?>

Querying a database Getting the number of rows in the result set: query($sql); echo “Number of rows : “ . $result->rowCount(); ?>

Exercise Write a PHP application the inserts a new employee into the ‘employees’ table. There should be a form that looks like the following wireframe :

Exercise Solution We will have a script named “form.php” with the following HTML:

• First name
• Last name
• Salary
• Date of birth

Exercise Solution The other script is called “add.php” which contains : quote($_POST['first_name']) . "', last_name = '" . $connection->quote($_POST['last_name']) . "', salary = " . $connection->quote($_POST['salary']) . ", dateofbirth = '" . $connection->quote($_POST['dateofbirth']) . "'"; $connection->query($sql); echo "Record has been added."; } catch (PDOException $e) { echo 'Connection failed: ' . $e->getMessage(); } ?>

Escaping values passed to MySQL It is advised that any values sent to MySQL should be escaped using the function called “quote” (like the previous example). In addition to preventing syntax errors in SQL statements, it is a top security concern ( google “SQL injection”, for more information about this type of security issue).

PDO::quote($str);

Prepared statements Prepared statements are the ability to set up a statement once, and then execute it many times with different parameters. Example : SELECT * FROM employees WHERE first_name = ? SELECT * FROM employees WHERE first_name = :first_name

Prepared statements One of the key advantages of prepared statements is the ability to bind parameters.

Binding parameters can be done using one of the approaches: 1- The question marks “?”. 2- Named parameters ( e.g. “:first_name”, “:last_name”, etc).

Prepared statements 1- Question marks: prepare($sql);

$sth->bindParam(1, $_POST['first_name']); $sth->bindParam(2, $_POST['last_name']); $sth->bindParam(3, $_POST['salary']); $sth->bindParam(4, $_POST['dateofbirth']); $sth->execute()

?>

Prepared statements 2-Named parameters: prepare($sql); $sth->bindParam(':first_name', $_POST['first_name']); $sth->bindParam(':last_name', $_POST['last_name']); $sth->bindParam(':salary', $_POST['salary']); $sth->bindParam(':dateofbirth', $_POST['dateofbirth']); $sth->execute();

?>

Prepared statements Why to use prepared statements: 1- Security; as the developer is not responsible for escaping parameters passed to MySQL. 2- MySQL parses each query before executing it, so if we are going to execute the same query with different data values, prepared statements are the optimized solution since the query will be compiled ( prepared ) only one time, and then the data will be substituted in the query each time we execute it.

Resources http://dev.mysql.com/doc/ http://php.net/manual/en/book.mysql.php http://www.php.net/manual/en/intro.pdo.php

Course Project Detailed explanation of the project. The project document will be uploaded to the course group.

Assignment Using the employees table, write a PHP code snippet that does the following: 1- Shows all the entries in the table. 2- Deletes an entry. 3- Edits an entry. 4- Adds a new entry.

The resulting application should look like PHPMyAdmin interface.

What's Next? • Course Project and Performance tips & best practices

Questions?