Transcript Design data
Intro to PHP & MySQL
http://www.meetup.com/Rippowam-Labs/files/
http://rippowamlabs.org/htmlformforphp.htm
http://rippowamlabs.org/welcomefriends.php
http://rippowamlabs.org/viewmembers.php
Objective:
Even if you don't know anything about either
PHP or MySQL, you will finish this class with
scripts you can use over and over again to:
Grab data from a web database and display data to
users.
Authenticate usernames and passwords for web
security.
Update and append data to a web database
I want to save you time in the first 8-10 chapters
of a PHP/MySQL intro book.
What you'll need...
Notepad (or other simple word processing
application).
An account with GoDaddy, ixWebhosting or
other Web hosting provider … or your own web
server (you'll be using mine today).
Our approach
Consider data needs
Design data table(s)
UI design follows
What is PHP?
Server-side programming language that
enables you to collect, store and manipulate
data on the web.
More than HTML (which is mostly concerned with
presenting data).
Used often with forms to collect data.
Can generate dynamic page content.
Don't have to display data – can just do work for
you.
An alternative to Microsoft ASP
What is MySQL?
An open-source database used to store data,
frequently for Web sites.
Absolutely free and available with most web
hosting plans (and automatically part of most
Web server install packages)
Yahoo, Google, Facebook, eBay all use(d) it as
a backend.
Syntax nearly identical to Oracle.
Somehow owned by Oracle now
Part of the LAMP stack (i.e., Linux, Apache,
A powerful combination
Mark Zuckerberg created the first edition of
Facebook using these tools to create a script
that captured photos and text from Harvard
dorm web sites and repurpose this into a social
networking site.
I used it to create a program that rips home
sales data from the Stamford city web site and
store it in a database. My friend, a home
appraiser, used it to find out comps for homes.
Great cross-platform tools!
Why it is/was revolutionary
Before LAMP tools became ubiquitous, the
barrier to entry was $$$ for average person.
Now setting up your own web server is
relatively easy and the tools are FREE!!!
Creating your own web server cost $ for hardware
and software… and then you had to pay extra for a
backend (e.g., SQL Server)
Tools are also available for free on GoDaddy,
ixWebhosting, etc. (You used to have to pay extra
for database support.)
They made it almost laughably easy for
Zuckerberg to build out his site and become a
PHP basics
It lives in-line with HTML in your source …
<!DOCTYPE html>
<html>
<body>
<?php
echo "My first PHP script!";
?>
Variables
A variable starts with the $ sign, followed by
the name of the variable
A variable name must start with a letter or the
underscore character
A variable name cannot start with a number
A variable name can only contain alphanumeric characters and underscores (A-z, 0-9,
and _ )
Variable names are case sensitive ($y and
$Y are two different variables)
Variables (coolest part)
PHP is a “loosely typed language.” It
automatically converts the variable to the
correct data type, depending on its value.
$txt="Hello world!";
$x=5;
$y=10.5;
You can even set a variable to a whole dataset
returned by a query
All the tools of a typical
programming language
Arrays
Loops (While, Do...While,For, For … Each)
If/else statements
Objects
Built-in string functions
Strlen() - gets length of string
Strpos() - gets position of string
Constants
define("GREETING", "Rippowam Labs!");
Tools at your disposal
Array functions
Calendar functions
Date functions
Directory functions
Error functions
Filesystem functions
FTP functions
HTTP functions
Mail functions
Math functions
Useful and Cool tools ...
$_SERVER['REMOTE_ADDR'] - Returns the IP
address from where the user is viewing the
current page
$_SERVER['REMOTE_PORT'] - Returns the
port being used on the user's machine to
communicate with the web server
$_GET (for use with forms)
$_POST (for use with forms)
Let's get into it - Forms
<html>
<body>
<form
action="http://www.rippowamlabs.org/welcomefrien
ds.php" method="post">
<p><b> Full Name: </b><input type="text"
name="name" size="20" maxlength="60" /><p>
<p><b> Zip Code:</b><input type="text"
PHP Receiver file
<html>
<body>
<?php
echo "The registrant's name is: " .
$_POST["name"];
echo "<br>The registrant's zip is: " .
$_POST["zip"];
echo "<br>The registrant's e-mail address is: " .
$_POST["email"];
?>
What's happening ...
htmlformforphp.htm-->
Welcomefriends.php
Let's add some error handling
if (empty($_POST["name"]) || empty($_POST["zip"]) ||
empty($_POST["email"]) ) {
echo "You forgot to enter values for one or all of the
input boxes";
echo 'Please return to last page and complete form.';
}
else
{
echo "The registrant's name is: " . $_POST["name"];
echo "<br>The registrant's zip is: " . $_POST["zip"];
MySQL
Popular apps: Microsoft Access, Oracle, Sybase
Virtually identical to Oracle in so many different
ways.
Relational database
A collection of structured data
Each row of each table should be unique and
each field has defined data type characteristics
CREATE TABLE RipLabMembers (
MyKey INT NOT NULL AUTO_INCREMENT,
MembName VARCHAR(100) NOT NULL,
MembEmail VARCHAR(100) NOT NULL,
MembZip VARCHAR(50),
MembIP VARCHAR(50) );
Relational Database
Before relational database, they had to store
information in these huge files that had ALL the
data.
Performace was slow and development was
difficult.
Relating tables
EmpID
EmpFirstNm
EmpLastNm
EmpTitle
1
Cameron
Yick
CEO
2
John
Robert
Streetsweeper
3
Barack
Obama
CFO
DeptID
DepartmentNM
DeptDesc
DeptBudget
DeptHead
1
Executive
Lead company
$1,000,000
1
50
Janitorial
Clean company
$10
2
2
Finance
Provide $
$100,000
3
Use SELECT statement to return
data
SELECT MembName, MembEmail, MembZip
FROM RipLabMembers;
SELECT * FROM RipLabMembers;
Use INSERT to append data
"INSERT INTO RipLabMembers VALUES
(DEFAULT, '$name', '$zip', '$email', '$ip')";
Use UPDATE statement to change
values in fields
UPDATE RipLabMembers
SET MembZip = '06901'
WHERE MembName = 'John Robert”;
Use DELETE statement to get rid of
entire rows
DELETE RipLabMembers
WHERE MembName = 'John Robert';
Go to ...
http://rippowamlabs.org/htmlformforphp.htm
http://rippowamlabs.org/welcomefriends.php
http://rippowamlabs.org/viewmembers.php
http://rippowamlabs.org/usernameandpasswordform.htm
http://www.rippowamlabs.org/login.htm
Injection attacks
mysql_real_escape_string() - cleans the string.
(Example: 'DROP TABLE MyMembers;')
Must be used after connection to db is open!
It literally prepends backslashes to the following
'
characters: \x00, \n, \r, \, , " and \x1a
Try to do this at:
http://rippowamlabs.org/htmlformforphp.htm
How to use:
mysql_real_escape_string()
#connecting to db
require_once ('../mysql_connect.php');
#setting variables
$uname =
mysql_real_escape_string($_POST["uname"]);
$pwd =
mysql_real_escape_string($_POST["pwd"]);
Other defense against injection
attacks
Bind variables
Instead of a hardcoded string with variables, you
dynamically build the SQL.
PDO (PHP Data Objects)
https://drupal.org/requirements/pdo
Places to learn PHP
Php.net
https://www.udemy.com/courses/search/?ref=ho
me&q=php
Lynda.com
Courseweb.net
http://www.w3schools.com/php/php_mysql_intro
.asp
Cameron's Project
Consider data needs
Design data table(s)
UI design follows