php & MySQL

Więcej takich

VARIABLES

przez Vanessa Canales

Categories of grammar

przez LEONEL ARIZA BALLEN

ARRAYS

przez Muhamad fadzil Fadzil

1.1 Understand computer storage and data types

przez Gary Sibbitts

SETTINGS

CREATING USERS

FLUSH PRIVILEGES;

obligatory! it resets and updates premisions

DDL permissions

GRANT ALTER CREATE DROP ON treehouse_movie_db.* TO user3@'127.0.0.1' IDENTIFIED BY 'password';

Permission to read and write, delete (all CRUD)

GRANT SELECT, INSERT, UPDATE, DELETE ON treehouse_movie_db.* TO user2@'127.0.0.1' IDENTIFIED BY 'password';

Permission to read

GRANT SELECT ON database_name. * TO user1@'%' IDENTIFIED BY 'password';

'%' is a wildcart to host location of a user

user1 can connect from any IP

Turn off the safe updates mode

SET SQL_SAFE_UPDATES = 0;

SYMBOLS

INDEXING

CREATE INDEX index_name ON users(last_name);

EXPLAIN SELECT * ...

gives us a raport about the query

," ",

adds a space inbetween

Filtering string results

SUBSTRING(email, 1, 10)

ex. CONCAT(SUBSTRING(LOWER(email), 1,10), "...") AS partial_email

andrew@cha...

SQL is 1 based index

it gives us a partial email to 10 character

CONCAT(first_name, UPPER(last_name)) AS full_name

LENGHT(username)

shows column with length

UPPER(last_names)

LOWER(email)

shows column in lowercase

Functions

HAVING

way of filtering after GRUPING and AGREGATING

SELECT title, MIN(score) AS minimum_score, MAX(score) AS maximum_score, AVG(score) AS average FROM movies LEFT OUTER JOIN reviews ON movies.id = reviews.movie_id WHERE year_released > 1999 GROUP BY movie_id HAVING average > 3;

long jonhson...

GRUP BY movies_id HAVING average > 3;

IFNULL

IFNULL (AVG(score),0)

gives us 0 instead of null

GROUP BY

shows us grupped results by content of chosen column

AVG

SELECT AVG(score) AS average WHERE movie_id = 1;

SUM

SELECT SUM(score) WHERE movie_id = 1;

MAX

SELECT MAX(score) AS maximum_score FROM reviews WHERE movie_id = 1;

shows the maximum score for the film

MIN

SELECT MIN(score) AS minimum_score FROM reviews WHERE movie_id = 1;

shows the minimum score for the film

COUNT

SELECT COUNT (*) FROM rewievs WHERE movie_id = 1;

shows us how many reviews the film has

ALIAS

temporary gives the table name we want

SELECT movies.title, genres.name AS genre_name...

Joining columns

OUTER JOIN

SELECT * FROM movies [LEFT | RIGHT] OUTER JOIN genres ON movies.genre_id = genres.id;

INNER JOIN

SELECT * FROM movies INNER JOIN genres ON movies.genre_id = genres.id;

CONSTRAINT

By this keyword A FOREIGN KEY in one table points to a PRIMARY KEY in another table

Column order

AFTER column_name

FIRST

ex. ALTER TABLE movies ADD COLUMN id INTEGER AUTO_INCREMENT PRIMARY KEY FIRST;

add a column as a first column in table

AUTO_INCREMENT

increment number by one after creating a new row

for integers

LIMIT

OFFSET

1

counting from 1

counting from 0

LIMIT 20, 10;

offset 20, limit 10

limit the results to given number

ORDER BY

uporządkowane według

ASC

ascending

from small to large

ex. SELECT * FROM movies ORDER BY year ASC, title DESC;

DESC

descending

from large to small

ex. ORDER BY year;

LIKE

%

wildcard

ex. WHERE title LIKE "%gotfather%";

The Godfather

The Godfather: Part II

ex. WHERE title LIKE "%gotfather";

The Godfather

part of a string

ex. WHERE title LIKE "gotfather";

nothing

BETWEEN

range of

ex. WHERE year BETWEEN 1999 AND 2004;

OR

if first condition is not meet

AND

...another condition

OPERATORS

<= >=

or equal to

< >

LESS, MORE than sth

!

not equal

ex. WHERE year != 1999;

=

test for condition

WHERE

ex. WHERE year = 1999;

adds a condition

*

SELECT * FORM movies;

or

SELECT movie.title, movie.year FROM movies;

all data from

QUERIES

DML (CRUD)

DELETE

DELETE FROM movies WHERE title = "Avatar 2" AND year = 2016;

UPDATE

UPDATE movies SET year=2016, title = "Avatar Reloaded" WHERE title="Avatar 2";

UPDATE movies SET year=2015 WHERE title="Avatar 2";

INSERT

INSERT INTO users (username, email, first_name, last_name) VALUES ("henry", "henry@email.com", "Henry", "Chalkley");

INSERT INTO movies SET title = "Back to the Future", year = 1985;

INSERT INTO movies VALUES ('Avatar', 2009), ('Avatar 2'), NULL);

multiple

INSERT INTO movies VALUES ("Avatar",2009);

SELECT

order is important!

filtering data like from objects

ex. movies.title

SELECT * FROM movies;

DDL

DELETING DATABASE

DROP DATABASE | SCHEMA [IF EXISTS] movies_db;

ALTERING COLUMNS

ALTER TABLE actors_table DROP date_of_birth;

ALTER TABLE actors_table CHANGE [COLUMN] pob place_of_birth VARCHAR(100);

ALTER TABLE actors_table ADD (pob VARCHAR(100), dob DATE);

ALTER TABLE table_name ADD [COLUMN] column_name VARCHAR(100);

CLEANING TABLES

TRUNCATE [TABLE] movies_table;

it deletes all the table nat creates new

DELETING TABLES

DROP TABLE movie_table;

DROP TABLE IF EXIST table_name;

warrning not an error

RENAMING TABLES

RENAME TABLE old_table_name TO new_table_name;

SHOW ENGINES;

listeing diffrent things

default engine from MySQL 5 is InnoDB

CREATE

CREATING TABLES

column definitions

defining an engine

ex. ...ENGINE = InnoDB;

it is the dafault value

(name VARCHAR(50) NOT NULL);

it don't let you left the field empty;

CREATE TEMPORARY TABLE;

accesible only for the connection

CREATE TABLE actors (name VARCHAR (50));

Table 'actors' with one column 'name' up to 50 characters

CREATE TABLE movies (title VARCHAR(200), year INTEGER);

CREATING DATABASES

CREATE SCHEMA 'movies_db_1';

DEFAULT CHARACTER SET utf8;

or CHARACTER SET = utf8;

for setting uncode

CREATE DATABASE IF NOT EXISTS 'movies_db_1';

if yes it will show an warning, not an error

CREATE DATABASE 'movies_db_1';

is possible

creates a new database

USE movies_db_1;

select the database

KEYS

Can't be null or duplicated

Foreign keys

ex. ALTER TABLE movies ADD COLUMN genre_id INTEGER NULL, ADD CONSTRAINT FOREIGN KEY (genre_id) REFERENCES genre(id);

aka. reference keys

relationschip between two tables

Unique keys

CREATE TABLE genres (id INTEGER [AUTO_INCREMENT]PRIMARY KEY, name VARCHAR(50) UNIQUE);

ssn

set of numbers ex. seciuruty number

email_adress

Primary keys

ex. CREATE TABLE genres (id INTEGER [AUTO_INCREMENT]PRIMARY KEY, name VARCHAR(50));

id

integers

DATATYPES

NULL

filtering null

WHERE year IS NOT NULL

= oparator not working

you have to use IS

ex. WHERE year IS NULL;

no data

Data

TIMESTAMP

with seconds

Format: YYYY-MM-DD HH:MI:SS

YEAR

DATATIME

DATA

TIME

Numbers

INTEGERS

DECIMALS

FLOAT

Strings

CHAR

VARCHAR

TEXT

special

$this

PSR

Basic coding standards

filter_var($varName, filter)

$cleanName = filter_var($name, FILTER_SANITIZE_STRING);

$cleanEmail = filter_var($email, FILTER_SANITIZE_EMAIL);

$cleanMsg = filter_var($msg, FILTER_SANITIZE_STRING);

filters variable with a specified filter

others

sanitize filters

FILTER_SANITIZE_EMAIL

FILTER_SANITIZE_STRING

they remove all the characters we don't want

validate filters

empty()

if (!empty($name) && !empty($email) && !empty($msg)) {

};

!empty($varName)

checks if sth is empty or not

var_dump()

<?php

$b = 3.1;

$c = true;

var_dump($b, $c);

?>

gives us output

float(3.1)

bool(true)

gives us information about variable

use Path\Name\File;

setting path like a variable on the begining save us from repeting it in our code (only the file name in our code)

date

date_default_timezone_set($timezone_identifier);

sets time for ex. in case of log to my timezone

require 'folder/package_name';

including sth into our project

echo 'Hello World';

like console.log in JS

Twig

DRY our HTML

<! DOCTYPE html>

<html>

<head>

{% block head %}

<link rel="stylesheet" href-"style.css" />

<title>{% block title %} {% endblock %} - My Webpage</title>

{% endblock %}

</head>

<body>

<div id="content">{% block content %} {% endblock%}</div>

</body>

</html>

every site with unique content

{% extends 'main.twig' %}

then {% block content %}

comments

{# comments goes here #}

including templates

{% include 'sidebar.html' %}

functions

control structure - if

{% if user | length > 0 %}

<ul>

{% for user in users %}

<li>{{ user.username|e }}</li>

{% endfor %}

</ul>

{% endif %}

for function - range

{% for i in range(0, 3) %}

{{ i }} //it will be printed

{% endfor %}

filters

uppercase

{% filter upper %}

This text become uppercase

{% endfilter %}

run some code

{% code %}

variables goeas without $

echoing sth

{{ it appers on the screen }}

setting vairiable in html

{% set name = 'Hampton' %}

ex, making multiple list items in navigation

<ul id="navigation">

{% for item in navigation %}

<li><a href="{{ item.href }} "> {{ item.caption }}</a></li>

{% endfor %}

</ul>

including it to slim/views

setting twig extension

$view->parserExtensions = array(

new \Slim\Views\TwigExtension(),

);

helpers like urlFOR, siteURL, baseURL, currentURL

siteURL

Contact

Home

baseURL

{{ baseUrl() }}

link for a main domain

setting options for twig

debbuging

$view = $app->view();

$view->parserOptions = array(

'debug' => true,

);

in our index.php

<?php

require 'vendor/autoload.php';

$app = new \Slim\Slim(array(

'view' => new \Slim\Views\Twig()

));

insall with composer

twig

PHP templeting language

Slim Microframework

post data validation

if there is an error

$app->redirect('/contact');

$app->post('/contact', function() use($app) {

$name = $app->request->post('name');

$email = ;$app->request->post('email');

$msg = ;$app->request->post('msg');

});

from simple contact form

routing based on URL

$app = new \Slim\Slim();

$app->get('/', function() {

echo "Hello, this is the home page.";

});

to point at html file

$app->get('/', function() use($app) {

$app->render('index.html');

});

but

we need to create .htaccess file to tell apache to route all traffic to our site through the index.php file

RewriteEngine On

# Some hosts may require you to use the `RewriteBase` directive.

# If you need to use the `RewriteBase` directive, it should be the

# absolute physical path to the directory that contains this htaccess file.

#

# RewriteBase /

RewriteCond %{REQUEST_FILENAME} !-d

RewriteCond %{REQUEST_FILENAME} !-f

RewriteRule ^ index.php [QSA,L]

basic instance of routing

$app = new \Slim\Slim();

$app=>get('/hello/:name', function ($name) {

echo "Hello, $name";

});

$app=>run();

it takes argument form URL, and use it in the function or anything

instaling via composer

composer require

slim

0

Composer - dependency menager

autoload

can by made simplu with composer

allows us to load automaticly requided classes, when we use the name of class in our code :)

composer.json

MVC

Model

COntain database related code

Cotroller

controls the flow of information between...

View

Front-End

HTML+CSS+Forms

what the user see

Object oriented PHP

tools & tricks

is_a()

class_exist()

make it short with variables

$class = "Product";

$p = new $class;

$m = "getName";

$name = $p->m();

is_subclass_of(object, class_name)

class Product

{

//parent class content

}

class Soda extends Product

{

//child of product

}

$s = new Soda();

is_subclass_of($s, "Product");

//true in this case

check if object is the child of a parent class

like wih DNA samples :)

method_exist(object, method_name)

ex2.

$p = new Product("Name", 20, "Description");

return mehod_exist($p, "getPrice");

return method_exist("Product", "getPrice");

//will return true or false...

it can check if method exist inside a class

inheritance

class Child extends Parent {

<definition body>

}

function overriding

addnig return to the functions

function getPrice(){

echo $this->price;

return $this->price;

}

function getTitle(){

echo $this->title;

return $this->title;

}

function inherited form parent class can be modified

class Novel extends Books{

var publsher;

function setPublisher($par){

$thtis->publisher = $par;

}

function getPublisher() {

echo $this->publisher;

}

}

and also extends parent class with new variables nad functions

child class will inherit all variables, and functions from parent class

for creating new-child class based on parent class

interfaces

then we implements it to class

class Report implements Mail {

// sendMail() Definition goes here

}

first we craete interface

interface Mail {

public function sendMail();

}

way of easy addnig interface functions to new implementations of classes

constansts

calling it

echo calssName::constName;

class MyClass {

const requiedMargin = 1.7;

}

without $

like a variable but once you declare it, it never changes value

class

abstract classes

abstract class MyAbstractClass {

abstract functions

myAbstractFunction() {

}

}

can contain abstract methods

only abstract class can contain abstract methods

those classes can only be inherited. Exists only virtually

giving access to calss properties and methods

public

gives acces to your mrthods and properties form outsite the class

it is set by default

static keywords

accessing

self::method();

self::property_name;

setting

class {

public static $manufacturer = "Bart Taylor";

public function getMaker() {

return self::$manufacturer;

}

}

make methods and properties accessible without creating new instance of class

limiting accesibility to class properties and metods

final keywords

class ParentClass {

final publc function funcName() {

echo "BaseClass::moreTesting()";

}

//you can't change it in child class

prevent parent methods and properties from beeing override by child class

protected

class MyClass {

protected $car = "Aston Martin";

$driver = "Gossling";

function __construct(par1) {

}

protected stuff is accessible only form parent, and child class

private

inherited class can'not acces private stuff

class MyClass {

private $car = "aston martin";

$driver = "Gosling";

function __construct(par1) {

}

it limits accesibility to the class in with it is declared

descructor

__destruct()

constructor

function __construct(par1, par2){

$this->price = $par1;

$this->title = $par2;

}

using parent constructor

function __construct($paremeters) {

parent::__construct($parent_parameters);

}

class Soda extends Product

{

public $flavor;

function __construct($name, $price, $desc, $flavor) {

parent::__construct($name, $price, $desc);

$this->flavor = $flavor;

}

we can use constructor form parent function to make our code DRY

remember! when writing inside constructor $this

$this->name = $name;

ex.

class books{

function __construct(par1, par2){

$this->price = $par1;

$this->title = $par2;

}

$physics = new Books("Physics for High School", 10);

is usefull to construct new object

is called automaticly when new object is created

special type of function

member function

calling mf

$physics->getTitle(); $physics->getPrice();

it will print:

Physics for High School

15

$physics->setPrice(15);

$physics->setTitle("Physics for High School");

Subtopic

you can call it after creating new object

in OOP function is called a method

function created inside class

member variable

$viariable->

$log->addWarning('log');

variable + object operator

accesed by member function

by default it is accesible outiste the class

variable created inside class

Basic

objects are based on classes

a template that includes datatypes, functions etc,

implements

objects

creating a new object

ex. $physics = new Books;

ex. $app = new \Slim\Slim();

new

unique instance of a class

Syntax

variables

$variable_name = value;

php tag