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
UPDATE
DELETE
TRUNCATE
DROP
Resources:
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:
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
Windows
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
Copy of Copy of PHP & MySQL - Class 3 - Girl Develop It
By yhoan andres galeano urrea
Copy of Copy of PHP & MySQL - Class 3 - Girl Develop It
- 987