PHP Database Programming Basics -- Northeast PHP

Basic PHP
Database
programming

Hello!I am Dave Stokes
MySQl Community Manager
FORTRAN, Punch Cards, ‘Personal Home Page’, older than dirt, etc.

22 Years old!
MySQL 5.7 relased ~ 2 years ago
-- JSON Data Tyoe
MySQL 8 Developer Milestone
Release
-- Available for testing
-- Data dictionary
-- UTf8MB4
More Plug-in Features
-- Group replication
-- Document Store
Oracle MySQL Cloud
-- Enterprise edition of
software

1.
Basics
Client Server Model and simple
connections
Using MySQL
but concepts
will transpose
over to other
Relational
Database
Management
Systems.

● Network – tcp/ip
● Port 3306
● Windows, Mac, Linux,
& source code
+ containers

To connect you will need
0. Server name or IP address
1. Persmission to connect
2. Account/password
3. (more later)

<?php
$mysqli = new mysqli("127.0.0.1", "user", "password", "database");
if ($mysqli->connect_errno) {
echo "Failed to connect to MySQL: (" . $mysqli->connect_errno . ") " .
$mysqli->connect_error;
}
echo $mysqli->host_info . "n";
?>

<?php
$mysqli = new mysqli("127.0.0.1", "user", "password", "database");
Host – 127.0.0.1 (or 192.168.10.11 or db.foobar.com)
User – User on the MySQL server (mysql.user table), not OS user
Password – Clear text (other options later) -> Security issue
Database – Schema to be used. Can be selected/changed later
Port (optional) – defaults to 3306

if ($mysqli->connect_errno) {
echo "Failed to connect to MySQL: (" . $mysqli->connect_errno . ") " .
$mysqli->connect_error;
}
If MySQL there is a MySQL connection error
then provide details

<?php
$dbh = new PDO('mysql:host=localhost;dbname=test', $user, $pass);

● Resource Guide
● Examples
● First place to look for answers

Syntax
Programming is hard but the syntactical aspects are
usually simple (some times too simple)

2.
Get Data
After connecting we need to do
something

1970’s
Disks are expensive and slow
Relational Model emerges with
goal of efficiency
Data Normaliation to berak
data in smaller logically
consistent groups
SQL was designed at IBM
to provide efficient
access to data via a
descriptive language
based on relational
calulus.

if ($result = $mysqli->query("SELECT * FROM City")) {
printf("Select returned %d rows.n", $result->num_rows);
/* free result set */
$result->close();
}
$mysqli->close();
?>

SELECT * FROM City
• * is a wild card for ‘all column in table
• City is the table being queried
• SELECT is the action (SELECT, UPDATE, DELETE, etc)

$mysqli = new mysqli();
$result = $mysqli->query();
Do Something with the data in app
$result->close();
$mysqli->close();
The basic flow is very
simple – connect,
query, close.
Note: the above is without return codes!
Always check return codes!

SELECT * FROM City
$result = $mysqli->query("SELECT id, label FROM test WHERE id = 1");
$row = $result->fetch_assoc();
printf("id = %s (%s)n", $row['id'], gettype($row['id']));
printf("label = %s (%s)n", $row['label'], gettype($row['label']));

$row = $result->fetch_assoc();
fetch_assoc -- Fetch a result row as an associative array
fetch_array() Fetch a result row as an associative, a numeric
array, or both
fetch_row() Get a result row as an enumerated array
fetch_object() Returns the current row of a result set as an
object

Congratulations! You know have the BASICS
down. The bad news is that there are are lot of
other things to learn.

The longest journey begins
with getting up off your
backside!!

3.
INput Data
Here is where being careful pays off

NEVER EVER TRUST
data input from a user!
Period!
EVER!

INTEGERS
Check to see if
intergers are really
integers by casting
them! Range check if
you can!!
STRINGS
Check size, filter out
junk, and expect the
unexpected!!
FILES
Isolate, scan, and
doublecheck.
Paranoia in defense of your data is a virtue not a sin

Yes, there are people out there that would love
to mess up your work, scramble or delete your
data, usurp your server, and in general treat you
professional life like a Games of Thrones plot
line including a Lannister but you are not a
Lannister

$QUERY = “SELECT * FROM PayingCustomers WERE userId = $id”;
if (!$mysqli->query($QUERY) {
echo “Query failed: (" . $mysqli->errno . ") " . $mysqli->error;
}
Can you spot TWO PROBLEMS with the above
(assume $email is from a form filled in by user) ?

What if $id is =
‘me@mw.com’ or 1 = 1
EVALUATES as TRUE

SELECT * FROM Users WHERE Name ="John Doe"
AND Pass ="Pass“
Name and Pass = " or ""="
SELECT * FROM Users WHERE Name ="" or ""=""
AND Pass ="" or ""=""

SELECT * FROM Users WHERE UserId = $UserId
$UserId = 15; DROP TABLE suppliers

if (!($stmt = $mysqli->prepare("INSERT INTO test(id) VALUES (?)"))) {
echo "Prepare failed: (" . $mysqli->errno . ") " . $mysqli->error;
}
$id = 1;
if (!$stmt->bind_param("i", $id)) {
echo "Binding parameters failed: (" . $stmt->errno . ") " . $stmt->error;
}
if (!$stmt->execute()) {
echo "Execute failed: (" . $stmt->errno . ") " . $stmt->error;
}

for ($id = 2; $id < 5; $id++) {
if (!$stmt->execute()) {
echo "Execute failed:(" . $stmt->errno . ") " . $stmt-
>error;
}
}

4.
The dreaded N+1
Problem
Killing your database server
performance by a thousand cuts

Problem: You need a ride to work tomorrow and need to find an active
employee that lives in your city.
Query 1 – Find the active employees in your city.
Query 2 – Of those employees, find those who have a parking permit.
Versus
Query1 -- Find employees in your city that have a parking permit

Problem: You need to find all customers with unfulfilled orders over thirty days
that are not ready to ship and have already paid. A series of small queries is
doing to take more resources and time that one big query.
Each dive into the data has a cost – minimize!!
Let the database do the ‘heavy lifting’ – that is its purpose!

Every time you connect and send a query to a MySQL server it will check:
1. Is your computer allowed to connect?
2. Is your account/authentication-string valid?
3. Do you have permission to access the data requested?
This adds up with small queries

1. SQL Anti Patterns:SQL Antipatterns: Avoiding the Pitfalls of Database Programming
Bill Karwin
2. Database Design and Relational Theory: Normal Forms and All That Jazz
CJ Date

THANKS!Any questions?
You can find me at @stoker or david.stokes @ oracle.com
Elephantdolphin.blogger.com
Slideshare.net/davidmstokes https://joind.in/talk/ad37a

PHP Database Programming Basics -- Northeast PHP

Recommended

More Related Content

What's hot (20)

Similar to PHP Database Programming Basics -- Northeast PHP (20)

More from Dave Stokes (20)

Recently uploaded (20)

PHP Database Programming Basics -- Northeast PHP