Class Overview

PHP

• A Breif History, Applications, Big users of PHP
• General Syntax
• Variables & Arrays
• Conditional Statements
• Loops and Functions
• Input & Output
• Touch on Classes and Objects
• Maybe more? Best Pratices, Frameworks / Librarys etc...

MySql

• What are Relational Databases?
• Creating a Database and Defining a Schema
• Manipulating our Database
  • Create
  • Retrieve
  • Update
  • Delete
• Types of Relations
• Maybe more? More advanced Queries, More advanced Joins

Putting it together

• Engineer our simple blog
• Group discussion and programing
• Optional Challenges

Breif Histroy

PHP was invented in 1994 by Rasmus Lerdorf originally named after Personal HomePage tools, in 1995 Rasmus released the code to the public and the rest is history... More Here

• "As of January 2013, 2.1M out of 4.3M web-facing computers are running PHP." — netcraft
• It's built for the web
• It's open source, with a HUGE world-wide community
• It's fun an easy to learn, yet very powerful when used well.

Applications

PHP is a turing complete language, which means anything a program can potentially do, PHP can do.

• Dynamic websites
• Intra-net web portals
• E-commerce
• Server task automation
• Parsing large data sets
• Social Media Networks
• General Scripting / Web scraping
• ...

Who Uses it?

• Facebook
• Wikipedia
• Drupal
• Wordpress
• Zynga
• Flickr
• Yahoo
• Google
• Youtube
• Amazon
• ...

The Language

• DRY
• KISS
• PHP is case sensitive!
• camelCase vs StudlyCaps vs under_scores
• The Request Lifecycle
• Basic operators / comparators
• Expressions

Scope!

• The scope of a variable within which it is defined.
• More or less PHP variables only have a single scope

Variables

• Types && PHP Typing
• Constants
• Arrays
• Objects
• Super Globals

Conditionals / Control Structures

• If
• Else
• If else
• Switch / Break / Continue
• Return / Require / Include

Loops - Control Structures Continued

• For loops
• Do While
• While
• Foreach

Functions

• What is a function?
• How to define functions
• Fucntion scope
• Calling Functions

Classes and Objects

Objects are an Object Oriented programing concept (that was redundant). Objects represent data that describes components or things in a system. Objects have properties(variables) which describe them, and methods(functions) which allow the objects to preform actions.

Classes are the templates from which a developer can 'spawn' or instanciate new instances of their class; thus creating objects. Think of them like blueprints.

Creating a Database

Time to explore PHP My Admin!

Defining a Schema

A schema is a fancy word for the structure of your database

• There is a lot to good schema design, but lets not worry about that now
• The way you structure your data effects how easy it is to preform certian types of queries

Connecting

To connect to a database you need at least 3 things

• Host
• Database user
• Database user password

We will be using PDO, a PHP 'binding' for MySQL which will do most of the heavy lifting for us

DBAL

DataBase Abstraction Layer (DBAL) is a term used to describe a tool that serves as an interface to a database. For our purposes PDO is this tool, exposed to us as a standard library.

• Its an object
• We will provide our PDO object with instructions by calling its methods, and giving it things to do.

Inserting Records

This is the primary way to add new data

• INSERT INTO tableName (col1, col2, col3) VALUES (val1, val2, val3)

Lets develop it!

We are going to be importing a CSV (comma seperated variable) file into our database, then reading it into a table on a webpage!

• Look at the file to see what our column names should be
• Create a new database
• create a table called crime_by_state
• add the right columns with their types
• Create a php file to parse your CSV file, the first row of text are the headers.
• We want to read a line, parse the string into an array, and use that array to generate our insert statement
• execute the statement and repeat until there are no more lines
• Run our script!
• check it

Updating Records

UPDATE tableName SET colname = val1, colname2 = val2 WHERE conditonal

Selecting Records

This is where the bulk of a databases power comes in. We can create queries to give us data in almost any manner we'd like.

Removing Records

Deleting things is permant!!! Normally in SQL databases things are never deleted, just 'soft deleted' or flagged so they are not returned in result sets

• DELETE FROM tableName WHERE condtional

Relations and Joins

This is where SQL is very defferent from other database, and along side its powerful Queries, another distinguishing feature.

• Primary Keys
• Foriegn Keys
• Normal Join
• Left Join - returns all rows from the left table (table1), with the matching rows in the right table (table2)
• Right Join - returns all rows from the right table (table2), with the matching rows in the left table (table1).

SELECT * FROM table1 JOIN table2 ON table1.column_name=table2.column_name

Building a Blog

The objective is to build the back end for an admin panel which manages a blog.

• We are focusing on the admin panel as there is more functionality. Any 'user facing' website would just retrieved records, and maybe sort them / styled them differently.
• We have provided you with HTML / CSS templates which you can edit.
• Lets look it over!

Instructions

Step 1: Set Up the Database

• Create a database!
• Make a table called 'posts'
• Your columns should be: id / title / author / body / tags/ created_at.. try and figure out what type they should be! If you have questions please ask!

Step 2: Controller

This is probably where most of the time will be spent, the controller contains most of our logic

• We have 2 main files for each page, and a shared model.
• Open the admin_controller.php file
• This file needs to do the following things:
  • check to see if the request is a POST
  • IF its a post, grab the POST data, sanitize it, and use PDO to enter it in the database
  • ALWAYS, after handling the post, query the database, and return a list of posts to the view

Step 3: PHP Templating!

• Create a database!
• Make a table called 'posts'
• Your columns should be: id / title / author / body / tags/ created_at.. try and figure out what type they should be! If you have questions please ask!