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

More Related Content

What's hot

Similar to PHP Database Programming Basics -- Northeast PHP

More from Dave Stokes

Recently uploaded

PHP Database Programming Basics -- Northeast PHP