PHP & MySQL - Class 3 - Girl Develop It

Intro to PHP and MySQL

Class 3

Recap

Reading files

http://au1.php.net/manual/en/function.file.php

Writing files

Managing directories

Interacting with a database

Threats: SQL Injection

Resources: http://wiki.hashphp.org/Validation

SQL Syntax

CREATE

SELECT

INSERT
UPDATE
DELETE

TRUNCATE

DROP

Resources:

http://en.wikipedia.org/wiki/Where_(SQL)

http://dev.mysql.com/doc/refman/5.0/en/select.html

Create Table

CREATE TABLE IF NOT EXISTS `action_log` (
                  `action_id` int(10) unsigned NOT NULL auto_increment,
                  `user_id` int(10) unsigned NOT NULL default '0',
                  `site_id` int(10) unsigned default NULL,
                  `type` int(1) unsigned NOT NULL default '0',
                  `category` int(2) unsigned NOT NULL default '0',
                  `date_entered` datetime NOT NULL default '0000-00-00 00:00:00',
                  `ip` varchar(40) NOT NULL default '',
                  `user_agent` varchar(255) NOT NULL default '',
                  `action` varchar(255) NOT NULL default '',
                  PRIMARY KEY  (`action_id`),
                  KEY `user_id` (`user_id`),
                  KEY `site_id` (`site_id`)
                ) ENGINE=InnoDB  DEFAULT CHARSET=latin1;

Resources:

http://dev.mysql.com/doc/refman/5.0/en/sql-syntax-data-definition.html

Drop Table

Remove the table completely

 DROP TABLE `table_name`

Truncate table

Remove all the records and reset the auto increment

 TRUNCATE TABLE `table_name`

Retrieving records

 SELECT * FROM `table_name`;

WHERE Conditions

Operators

 =, <>, >, >=, <, <=, IN, BETWEEN, LIKE, IS NULL or IS NOT NULL

Predicates

 IN, BETWEEN, LIKE

INSERT records

 INSERT INTO `table` (user, age) VALUES ('nick', 20);

UPDATE Records

UPDATE table_name
SET column1=value, column2=value2,...
WHERE some_column=some_value

DELETE Records

DELETE FROM `table_name` WHERE tutorial_id=3;

PHP Data Objects (PDO)

 <?php

$db = new PDO('mysql:host=localhost;dbname=testdb;charset=utf8', 'username', 'password');
$db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$db->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);

Running a query

 <?php

foreach($db->query('SELECT * FROM table') as $row) {
    echo $row['field1'].' '.$row['field2']; //etc...
}

Query with error handling

 <?php

function getData($db) {
   $stmt = $db->query("SELECT * FROM table");
   return $stmt->fetchAll(PDO::FETCH_ASSOC);
}
 
//then much later
try {
   getData($db);
} catch(PDOException $ex) {
   //handle me.
}

Get Row Count

<?php

$stmt = $db->query('SELECT * FROM table');
$row_count = $stmt->rowCount();
echo $row_count.' rows selected';

INSERT, UPDATE, DELETE

<?php

$affected_rows = $db->exec("UPDATE table SET field='value'");
echo $affected_rows.' were affected'

Without PDO

$results = mysql_query(sprintf("SELECT * FROM table WHERE id='%s' AND name='%s'", 
                       mysql_real_escape_string($id), mysql_real_escape_string($name))) or die(mysql_error());
$rows = array();
while($row = mysql_fetch_assoc($results)){
    $rows[] = $row;
}

Queries with parameters

<?php

$stmt = $db->prepare("SELECT * FROM table WHERE id=? AND name=?");
$stmt->execute(array($id, $name));
$rows = $stmt->fetchAll(PDO::FETCH_ASSOC);

 <?php
$stmt = $db->prepare("SELECT * FROM table WHERE id=:id AND name=:name");
$stmt->bindValue(':id', $id, PDO::PARAM_INT);
$stmt->bindValue(':name', $name, PDO::PARAM_STR);
$stmt->execute();
$rows = $stmt->fetchAll(PDO::FETCH_ASSOC);

<?php
$stmt = $db->prepare("SELECT * FROM table WHERE id=:id AND name=:name");
$stmt->execute(array(':name' => $name, ':id' => $id));
$rows = $stmt->fetchAll(PDO::FETCH_ASSOC);

INSERT, UPDATE, DELETE

<?php
$stmt = $db->prepare("INSERT INTO table(field1,field2,field3,field4,field5) VALUES(:field1,:field2,:field3,:field4,:field5)");

$stmt->execute(array(':field1' => $field1, ':field2' => $field2, ':field3' => $field3, ':field4' => $field4, ':field5' => $field5));
$affected_rows = $stmt->rowCount();

<?php
$stmt = $db->prepare("DELETE FROM table WHERE id=:id");
$stmt->bindValue(':id', $id, PDO::PARAM_STR);
$stmt->execute();
$affected_rows = $stmt->rowCount();

<?php
$stmt = $db->prepare("UPDATE table SET name=? WHERE id=?");
$stmt->execute(array($name, $id));
$affected_rows = $stmt->rowCount();

PHPMyAdmin

http://10.10.10.10/phpmyadmin/

Windows

http://localhost/phpmyadmin/

Problem

The content for the page should pull from a database

Task

Add tables for any section of the page and display the content from it