using mysql in php

63
webdev@rgu using mysql in php

Upload: mike-crabb

Post on 07-Jan-2017

1.129 views

Category:

Software


1 download

TRANSCRIPT

Page 1: Using mySQL in PHP

webdev@rguusing mysql in php

Page 2: Using mySQL in PHP

what we are covering:

web tier design introduction to sql rules for creating tables rules for writing sql using databases in php

Page 3: Using mySQL in PHP

web tier design

Page 4: Using mySQL in PHP

2-tier

CLIENT SERVER

Page 5: Using mySQL in PHP

3-tier

CLIENT SERVER DATABASE

Page 6: Using mySQL in PHP

CLIENT SERVER DATABASE

PRESENTATION TIER

LOGIC TIER

DATA TIER

HTML & CSS PHP MYSQL

‘Machine’ names

‘tier’ name

‘technology’ name

Page 7: Using mySQL in PHP

n-tier

Page 8: Using mySQL in PHP

benefits of n-tier

AVAILABILITY REDUNDANCY SCALABILITY PERFORMANCE

Page 9: Using mySQL in PHP

availability

IF ONE SERVER GOES DOWN THE REST STILL RUN

Page 10: Using mySQL in PHP

redundancy

BACKUPS ENSURE THAT DATA IS ALWAYS AVAILABLE

Page 11: Using mySQL in PHP

performance

LOAD BALANCING CAN MAKE SURE THAT SERVERS ARE USED OPTIMALLY

Page 12: Using mySQL in PHP

scalability

MORE SERVERS CAN BE ADDED AS AND WHEN THEY ARE NEEDED

Page 13: Using mySQL in PHP

disadvantages of n-tier

• LATENCY • INCREASED NUMBER OF HOPS • UNPREDICTABLE LINK QUALITY • NETWORK IS THE SLOWEST LINK

• COMPLEXITY • THERE IS NO MANUAL FOR WEB-

SCALE COMPUTING • FACEBOOK / REDDIT / TWITTER HAD

TO LEARN AS THEY WENT ALONG

Page 14: Using mySQL in PHP

introduction to SQL

Page 15: Using mySQL in PHP

mysql•Released in 1995 •Lots of different pronunciations

•My Ess Que Ell •My Sequel (I prefer this one)

•Most popular open source relational database management system (RDBMS

Page 16: Using mySQL in PHP

mysql•http://www.mysql.com provides more information and documentation

•Its very popular •Open source licence •Fast, reliable and easy to use •Free to use! •mySQL Server works in local and remote systems

•Used by a lot of mainstream companies •Wordpress, Drupal, other CMS •Facebook (ish)

Page 17: Using mySQL in PHP

mysqlwe will be using azure!

(this means you’ll need to log into it again…and remember your password)

Page 18: Using mySQL in PHP

mysql visualising a database

there are lots of different ways that we can use to visualise a database. No one way is the best/worst. It’s mostly down to personal preference (of who you’re working for!)

Page 19: Using mySQL in PHP

1. Tables are represented by boxes similar to this

Page 20: Using mySQL in PHP

1. Tables are represented by boxes similar to this 2. The name of the table goes in the box at the top

Page 21: Using mySQL in PHP

1. Tables are represented by boxes similar to this 2. The name of the table goes in the box at the top 3. The rows in the table go in the boxes underneath

Page 22: Using mySQL in PHP

1. Tables are represented by boxes similar to this 2. The name of the table goes in the box at the top 3. The rows in the table go in the boxes underneath 4. Some table rows have a special notation attached to

them

Page 23: Using mySQL in PHP

1. Tables are represented by boxes similar to this 2. The name of the table goes in the box at the top 3. The rows in the table go in the boxes underneath 4. Some table rows have a special notation attached to

them 1. Primary Keys are signified by an underline

Page 24: Using mySQL in PHP

1. Tables are represented by boxes similar to this 2. The name of the table goes in the box at the top 3. The rows in the table go in the boxes underneath 4. Some table rows have a special notation attached to

them 1. Primary Keys are signified by an underline 2. Foreign Keys are signified by an asterisk

Page 25: Using mySQL in PHP

• The PRIMARY KEY constraint uniquely identifies each record in a database table.

• Primary keys must contain UNIQUE values.

• A primary key column cannot contain NULL values.

primary keys

Page 26: Using mySQL in PHP

• A FOREIGN KEY in one table points to a PRIMARY KEY in another table.

foreign keys

Page 27: Using mySQL in PHP

• Reduce repeating data

• Easier to change one thing than to change lots of things

reasons for linking tables

Page 28: Using mySQL in PHP

One to One

One to Many

Many to One

Many to Many

types of relationships (links)

Page 29: Using mySQL in PHP

One to One

One to Many

Many to One

Many to Many

types of relationships (links)

Page 30: Using mySQL in PHP

Rules for creating tables in

mysql

Page 31: Using mySQL in PHP

this is a fairly standard table design

myFirstTable

email username password

Page 32: Using mySQL in PHP

CREATE TABLE `myFirstTable` ( `email` VARCHAR( 50 ) NOT NULL , `username` VARCHAR( 25 ) NOT NULL , `password` VARCHAR( 255 ) NOT NULL , PRIMARY KEY (`email`) )

you should know how to turn

myFirstTable

email username password

Page 33: Using mySQL in PHP

CREATE TABLE `myFirstTable` ( `email` VARCHAR( 50 ) NOT NULL , `username` VARCHAR( 25 ) NOT NULL , `password` VARCHAR( 255 ) NOT NULL , PRIMARY KEY (`email`) )

Page 34: Using mySQL in PHP

CREATE TABLE `myFirstTable` ( `email` VARCHAR( 50 ) NOT NULL , `username` VARCHAR( 25 ) NOT NULL , `password` VARCHAR( 255 ) NOT NULL , PRIMARY KEY (`email`) )

Golden Rule All names must be presented

‘likeThis’

Page 35: Using mySQL in PHP

CREATE TABLE `myFirstTable` ( `email` VARCHAR( 50 ) NOT NULL , `username` VARCHAR( 25 ) NOT NULL , `password` VARCHAR( 255 ) NOT NULL , PRIMARY KEY (`email`) )

1. When we are creating a table we use the keywords create table followed by the name that we want to call it

Golden Rule All names must be presented

‘likeThis’

Page 36: Using mySQL in PHP

CREATE TABLE `myFirstTable` ( `email` VARCHAR( 50 ) NOT NULL , `username` VARCHAR( 25 ) NOT NULL , `password` VARCHAR( 255 ) NOT NULL , PRIMARY KEY (`email`) )

1. When we are creating a table we use the keywords create table followed by the name that we want to call it

2. Any row that we want to create in the table are then placed between brackets

Golden Rule All names must be presented

‘likeThis’

Page 37: Using mySQL in PHP

CREATE TABLE `myFirstTable` ( `email` VARCHAR( 50 ) NOT NULL , `username` VARCHAR( 25 ) NOT NULL , `password` VARCHAR( 255 ) NOT NULL , PRIMARY KEY (`email`) )

1. When we are creating a table we use the keywords create table followed by the name that we want to call it

2. Any row that we want to create in the table are then placed between brackets

3. Each row that we want to create has its own line

Golden Rule All names must be presented

‘likeThis’

Page 38: Using mySQL in PHP

CREATE TABLE `myFirstTable` ( `email` VARCHAR( 50 ) NOT NULL , `username` VARCHAR( 25 ) NOT NULL , `password` VARCHAR( 255 ) NOT NULL , PRIMARY KEY (`email`) )

1. When we are creating a table we use the keywords create table followed by the name that we want to call it

2. Any row that we want to create in the table are then placed between brackets

3. Each row that we want to create has its own line 1. Start with the name of the row

Golden Rule All names must be presented

‘likeThis’

Page 39: Using mySQL in PHP

CREATE TABLE `myFirstTable` ( `email` VARCHAR( 50 ) NOT NULL , `username` VARCHAR( 25 ) NOT NULL , `password` VARCHAR( 255 ) NOT NULL , PRIMARY KEY (`email`) )

1. When we are creating a table we use the keywords create table followed by the name that we want to call it

2. Any row that we want to create in the table are then placed between brackets

3. Each row that we want to create has its own line 1. Start with the name of the row 2. Put in the data type

Golden Rule All names must be presented

‘likeThis’

Page 40: Using mySQL in PHP

CREATE TABLE `myFirstTable` ( `email` VARCHAR( 50 ) NOT NULL , `username` VARCHAR( 25 ) NOT NULL , `password` VARCHAR( 255 ) NOT NULL , PRIMARY KEY (`email`) )

1. When we are creating a table we use the keywords create table followed by the name that we want to call it

2. Any row that we want to create in the table are then placed between brackets

3. Each row that we want to create has its own line 1. Start with the name of the row 2. Put in the data type 3. Define the length of the data type in brackets

Golden Rule All names must be presented

‘likeThis’

Page 41: Using mySQL in PHP

CREATE TABLE `myFirstTable` ( `email` VARCHAR( 50 ) NOT NULL , `username` VARCHAR( 25 ) NOT NULL , `password` VARCHAR( 255 ) NOT NULL , PRIMARY KEY (`email`) )

1. When we are creating a table we use the keywords create table followed by the name that we want to call it

2. Any row that we want to create in the table are then placed between brackets

3. Each row that we want to create has its own line 1. Start with the name of the row 2. Put in the data type 3. Define the length of the data type in brackets 4. Then think about null/not null

Golden Rule All names must be presented

‘likeThis’

Page 42: Using mySQL in PHP

CREATE TABLE `myFirstTable` ( `email` VARCHAR( 50 ) NOT NULL , `username` VARCHAR( 25 ) NOT NULL , `password` VARCHAR( 255 ) NOT NULL , PRIMARY KEY (`email`) )

1. When we are creating a table we use the keywords create table followed by the name that we want to call it

2. Any row that we want to create in the table are then placed between brackets

3. Each row that we want to create has its own line 1. Start with the name of the row 2. Put in the data type 3. Define the length of the data type in brackets 4. Then think about null/not null

4. Finally think about primary keys

Golden Rule All names must be presented

‘likeThis’

Page 43: Using mySQL in PHP

rules for writing sql

Page 44: Using mySQL in PHP

we’ll use this database as an example

Page 45: Using mySQL in PHP

(sorry if you don’t like marvel)

Page 46: Using mySQL in PHP

sql selectWhen we want to get information from the database we use a select statement

SELECT columnName, columnName FROM tableName;

Page 47: Using mySQL in PHP

sql select *If we want to select everything in a table we use the * character

SELECT * FROM tableName;

Page 48: Using mySQL in PHP

sql whereThe where keyword can be used to filter the information that we get back from the database

SELECT columnName, columnName FROM tableName WHERE condition;

Page 49: Using mySQL in PHP

sql whereLots of different operators that we can use for this

= equals <> does not equal > greater than < less than >= greater than or equal to <= less than or equal to LIKE search for a pattern

Page 50: Using mySQL in PHP

sql and / orCan use AND and OR to further filter the data and add more conditions to our search

SELECT columnName, columnName FROM tableName WHERE condition AND condition;

Page 51: Using mySQL in PHP

sql insertInsert can be used to put information into the database

INSERT INTO tableName (column1, column2, column3) VALUES (value1, value2, value3)

goes into our database. Note that I didn’t put in the ID (primary key) this was auto generated for me

Page 52: Using mySQL in PHP

sql updateUpdate can be used to edit data that exists in our database

UPDATE tableName SET column1=value1, column2=value2 WHERE condition

Page 53: Using mySQL in PHP

sql deleteDelete is used to remove information from our database

DELETE FROM tableName WHERE condition

Page 54: Using mySQL in PHP

using databases in

php

Page 55: Using mySQL in PHP

CLIENT SERVER DATABASE

WE WILL BE USING A MYSQL DATABASE HOSTED BY CLEARDB

Page 56: Using mySQL in PHP

not this one!

Page 57: Using mySQL in PHP

finding your connection details

Page 58: Using mySQL in PHP

connecting your database to php

THERE ARE A NUMBER OF DIFFERENT WAYS THAT YOU CAN USE TO CONNECT A DATABASE TO PHP

WE ARE GOING TO BE USING MYSQLI

Page 59: Using mySQL in PHP

connecting your database to phpTHERE ARE A NUMBER OF DIFFERENT WAYS THAT YOU CAN USE TO CONNECT A DATABASE TO PHP

WE ARE GOING TO BE USING MYSQLI

THE BEST WAY TO USE THIS IS TO CREATE A NEW PHP FILE CALLED SOMETHING LIKE DBCONNECT.PHP

Page 60: Using mySQL in PHP

dbconnect.php

HINT: IF YOU COPY AND PASTE THE TEXT FROM THIS SLIDE YOU CODE MAY BREAK AND I WILL LAUGH.

WRITE THIS OUT YOURSELF!

<?php

define(‘DB_SERVER’, ‘servername’); define(‘DB_USERNAME’, ‘username’); define(‘DB_PASSWORD’, ‘password’); define(‘DB_DATABASE’, ‘database’);

$db = mysqli_connect(DB_SERVER, DB_USERNAME, DB_PASSWORD, DB_DATABASE);

Page 61: Using mySQL in PHP

using dbconnect.phpANY TIME THAT WE WANT TO USE OUR DATABASE WE INCLUDE THE FOLLOWING LINE AT THE TOP OF OUR PHP PAGE:

include(“dbconnect.php”);

AND CAN THEN CALL $DB ANY TIME THAT WE WANT TO USE IT

Page 62: Using mySQL in PHP

$sql = “SELECT * from marvel movies”;

$result = mysqli_query($db, $sql);

$row = mysql_fetch_array($result, MYSQLI_ASSOC);

//SETS OUR SQL STATEMENT

//RUNS THE SQL STATEMENT ON OUR DATABASE

//FETCHES THE RESULT AS AN ARRAY

Page 63: Using mySQL in PHP

recapweb tier design introduction to sql rules for creating tables rules for writing sql using databases in php