SQL | PHP Tutorial

1
SQL PHP Tutorial

• at 8am. god, its early.

2
SQL intro

• There are many different versions of SQL
  available for usage.
• Oracle
• MySQL
• SQLite
• DB2
• Mimer
• The popular ones are Oracle and MySQL with MySQL
  quickly gaining ground. Ill be showing you
  MySQL. The syntax between SQL domains varies
  little and with google skills you can adjust this
  knowledge for SQLite, which Ive never used.

3
Databases _ creation
CREATE TABLE tableName ( name VARCHAR(55), sex
CHAR(1), age INT(3), birthdate DATE, salary
DECIMAL(10,2), primary key(name) ) Types of
attributes char, varchar, int, smallint,
decimal, date, float, etc. varchar is a string
with varying of characters. In our example, 55
is the characters longest possible string
allowed. decimal(10,2) indicated 2 places after
the decimal point and 10 total digits (including
the decimal numbers)
4
Databases _ creation 2
CREATE TABLE tableName ( name VARCHAR(55), sex
CHAR(1) NOT NULL, age INT(3), birthdate
DATE, salary DECIMAL(10,2) DEFAULT
0.00, primary key(name) ) Primary key
primary key is a UNIQUE value. For every entry in
your database this must be unique and not null
and every DB must have one. NOT NULL column must
have a value DEFAULT you can set a default value
if no other value is inputted for that column.
5
Databases _ indexed primary keys
Instead of specifying a column as a primary key
you can have the database create a column of
numbers that will automatically increment with
each entry inserted into the DB. Example CREATE
TABLE tableName ( id INT AUTO_INCREMENT, name
VARCHAR(55), sex CHAR(1), age
INT(3), birthdate DATE, salary
DECIMAL(10,2), primary key(id) ) Entry 1 will
have 1 as a key. Entry 2 will have 2 and so forth.
6
Databases _ deletion
DROP TABLE tableName
7
Databases _ insertion
Inserting data in the database INSERT INTO
tableName(name,sex,age) VALUES(Mr.
Freeze,M,42) Also valid INSERT INTO
tableName(sex,name,age) VALUES(F,Mr.
Freeze,42) Order doesnt matter.
8
Databases _ the meat
Always in the form of SELECT . FROM . WHERE
. So select a column from your database. From a
database Where x meets y condition. Except in
the case of modification
9
Databases _ updating
Suppose we want to change Mr. Freezes age to
52. UPDATE tableName SET age 52 WHERE name
LIKE Mr. Freeze And so forth.
10
Databases _ aggregates
This is the actual meat of using SQL. These are
where you set your conditions, narrow down your
table into a usable set. Here are the usable
functions, Ill show a quick example with each.
The only way to really know this stuff is
practice. Group by Count Sum Avg Min/Max Order
by
11
Databases _ group by
This is the actual meat of using SQL. These are
where you set your conditions, narrow down your
table into a usable set. Here are the usable
functions, Ill show a quick example with each.
The only way to really know this stuff is
practice. Group by lumps all the common
attributes into one row. SELECT employee_id,
MAX(salary) FROM Works_In GROUP BY dept_id
MAX selects the maximum value in its () likewise
for MIN
12
Databases _ count
Count counts the number of columns with the
specified attribute. SELECT term,
COUNT(course_id) FROM teaches GROUP BY term We
counted the number of courses taught during x
term. AVG SUM function pretty much the same
way.
13
(No Transcript)
14
PHP _ connecting to the db
This is the basic connect script for accessing
your db lt?php mysql_connect(localhost,usernam
e,password) or die(mysql_error())
?gt Localhost indicates the current machine. So
youre asking the machine to connect to itself.
The die(mysql_error) part says if theres an
error halt everything and display this error. If
it errors on this part, it means either your
host, username, or password are wrong.
15
PHP _ error checking w/ echo
Consider the connection script again with this
modification lt?php mysql_connect(localhost,us
ername,password) or die(mysql_error()) echo
Connected to database. ?gt Later on you may be
unable to differentiate where the error occurred.
So while developing your code throw in some echo
statements, they just print stuff to the screen.
When PHP is done connecting to our database it
tell us.
16
PHP _ select the database.
lt?php mysql_connect(localhost,username,passwo
rd) or die(mysql_error()) echo Connected
MySQL! mysql_select_db(ljlayou_comp353 or
die(mysql_error()) echo Connected to database
353 ?gt
17
PHP _ create/drop table
lt?php mysql_connect(localhost,username,pw)
or die(mysql_error()) mysql_select_db(ljlayou_c
omp353 or die(mysql_error()) mysql_query(CREAT
E TABLE Works_In()) or die(mysql_error()) ?gt W
ere querying PHP to tell MySQL to do something,
in this case create the table. The same applies
for dropping a table. As you can see our code is
being reused over and over. It gets pretty
repetitive like this. Again we tell php to stop
everything if an error occurs.
18
PHP _ insertion
lt?php mysql_connect(localhost,username,pw)
or die(mysql_error()) mysql_select_db(ljlayou_c
omp353 or die(mysql_error()) mysql_query(INSER
T INTO Works_In(company,position)
VALUES(McDonalds,fry cook)) ?gt Were
querying PHP to tell MySQL to do something, in
this case create the table. The same applies for
dropping a table. As you can see our code is
being reused over and over. It gets pretty
repetitive like this.
19
PHP _ selecting a table
In order to manipulate, fetch, etc data from your
database you must have PHP remember the result.
So we store it in an array (?) to preserve
columns. PHP variables unlike Java do not need
a type declaration. From now on Ill be omitting
the connect stuff. lt?php result
mysql_query(SELECT FROM Works_In) or
die(mysql_error()) row mysql_fetch_array(resu
lt) echo company .rowcompany echo
position .rowposition ?gt From these
lines we see that each cell in the area is
labeled under the column name. Using this method
we can output or even compare data.
20
PHP _ selecting a table
In order to manipulate, fetch, etc data from your
database you must have PHP remember the result.
So we store it in an array (?) to preserve
columns. PHP variables unlike Java do not need
a type declaration. From now on Ill be omitting
the connect stuff. lt?php result
mysql_query(SELECT FROM Works_In) or
die(mysql_error()) row mysql_fetch_array(resu
lt) echo company .rowcompany echo
position .rowposition ?gt From these
lines we see that each cell in the area is
labeled under the column name. Using this method
we can output or even compare data. The
symbol in the SELECT statement just means that we
select all the columns in the table. The above
statement however results in the first row only
being shown.
21
PHP _ selecting a table 2
To solve this problem, we loop continuously until
there are no more rows to choose from. lt?php
while (row mysql_fetch_array(result))
echo company .rowcompany.
position .rowposition echo
ltbr/gt ?gt If you have noticed the . symbol
signifies a concatenation.
22
PHP _ the formula
We looked over it all. Heres the general
formula lt?php mysql_connect(localhost,userna
me,pw) or die(mysql_error()) mysql_select_db(
databaseName or die(mysql_error()) result
mysql_query(yourQuery) or die(mysql_error()) row
mysql_fetch_array(result) while (row
mysql_fetch_array(result)) ?gt (Show 255
final)
23
(No Transcript)
24
PHP _ form processing
Topic job but its all good. Im assuming you
know how to create forms in HTML. Else, well,
google it. Its pretty straight forward. So
lets take this form as our example, this is a
snippet of the form code ltform nameanimas
actionprocessform.php" method"postgt
ltbgtFUNlt/bgt ltinput type"radio"
name"extra" value"horns"gtHorns ltinput
type"radio" name"extra" value"wings"gtWings
ltinput type"radio" name"extra"
value"mane"gtMane ltinput type"radio"
name"extra" value"giraffe"gtGiraffe Neck ltinput
type"submit" name"submit" value"imagine it"
class"submit" /gt lt/formgt
25
PHP _ form processing 2
Our form action tells the form what to do with
the data. POST is a method that sends an array of
variables, our data. Only when the submit button
is pressed is the data sent. ltform
nameanimals actionprocessform.php"
method"postgt ltinput type"submit"
name"submit" value"imagine it" class"submit"
/gt lt/formgt It is common practice to create a
separate file for form processing.
26
PHP _ form processing 3
Our data is now winding through the PHP tubes.
Lets look how its processed. _POSTsubmit
if( dbc _at_mysql_connect(localhost,username,
pw)) if(!_at_mysql_select_db(database))
die(mysql_error()) else
die(mysql_error()) query INSERT INTO
animals(id,data,appendages, tail,
sound,extra) VALUES(0,_POSTbody,_POST
appendages, ) if(_at_mysql_query(query)
print success else print you
lose mysql_close()
27
PHP _ form processing 4

• Some notes on the previous slide
• _at_ symbol means success or halt script.
• mysql_close() its very important to close your
  connection when youre done