MySQL, PHP and Databases

• MySQL and PHP are to be covered
• The course is from the University of Halabja, College of Science, Department of Computer
• Bashdar A. MOHAMMED prepared this lesson on April 7, 2025

Table of Contents

• Discusses Databases
• Discusses introduction Structured Query Language(SQL)
• Topics include how to SELECT, INSERT, UPDATE and DELETE
• Discusses using MySQL and PHP

Table of Contents

• Database introductions occur
• Shows the interaction between a browser, internet, server, web server, file, PHP and database

Data Storage

• Storing data is not the primary reason to use a database
• Flat storage eventually runs into issues with Size, Ease of updating, accuracy, security, redundancy and importance

Database Engine Flow

• SQL Server uses the Client-Server Model
• Clients make a Query to the Engine which then Accesses the Database

Datatypes with MySQL

• Numeric datatypes can be integers with different data sizes
• Numeric datatypes can also represent real numbers such as float, double and decimal
• String datatypes include char(size) which are fixed string lengths
• varchar(size) is a variable size string
• Text data blocks have datatypes called text, tinytext, mediumtext and longtext
• Binary datatypes include binary(size), varbinary(size) which are fixed and variable string lengths respectively
• Date and time have datatypes such as date which can range from 1000-01-01 to 9999-12-31
• datetime can range from 1000-01-01 00:00:00 to 9999-12-31 23:59:59
• timestamp can range from '1970-01-01 00:00:01 UTC to 2038-01-19 03:14:07 UTC

Structured Query Language(SQL)

• Structured Query Language acronym SQL
• Used as the declarative language when working with relational data
• Formatted in regular English as much as possible
• MySQL dialect support definition, manipulation, and access control of records
• Also support control statements such as if statements or loops
• Designed for writing logic inside the DataBase

SQL Examples

• SELECT first_name, last_name, job_title FROM employees
• SELECT * FROM projects WHERE start_date = '1/1/2006'
• INSERT INTO projects(name, start_date) VALUES('Introduction to MySQL', '1/1/2006')
• UPDATE projects SET end_date = '8/31/2006' WHERE start_date = '1/1/2006'
• DELETE FROM projects WHERE start_date = '1/1/2006'

SQL Queries

• SQL allows communication with the database engine
• Queries provide greater control and flexibility to create a database with SQL
• SQL Keywords are traditionally capitalized
• CREATE DATABASE people(Database name)

Table Creation in SQL

• CREATE TABLE employees (id int NOT NULL, email varchar(50) NOT NULL, first_name varchar(50),last_name varchar(50))

Retrieving entries with SQL

• Retrieve all columns from departments table example "SELECT * FROM departments"
• Retrieve specific columns example "SELECT department_id, name FROM departments"

Column Aliases

• Aliases rename a table or column heading
• Example: SELECT employee_id AS id, first_name, last_name FROM employees
• Can also be used to shorten field or add clarification
• Example: SELECT c.duration, c.ACG AS 'Access Control Gateway' FROM Calls AS c

Concatenation Operation

• Column names or strings can be concatenated with CONCAT
• Enclose string literals in single or double quotes
• Table and column names containing special symbol use "" or ''
• Example: SELECT CONCAT(first_name, ' ', last_name) AS Full Name, employee_id AS No. FROM employees

Employee Summary

• Goal is to find information about all employees, and list their full name, job title and salary
• Concatenation displays the first and last names as one field
• SELECT CONCAT(first_name, ' ', last_name) AS Full Name, job_title, salary FROM employees

Filtering Selection with SQL

• DISTINCT is used to eliminate duplicate results
• Example: SELECT DISTINCT department_id FROM employees
• WHERE clause is used to filter rows by specific conditions
• Example: SELECT last_name, department_id FROM employees WHERE department_id = 1

Comparison Condition

• Conditions can be combined with NOT, OR, and AND with brackets
• Example: SELECT last_name FROM employees WHERE NOT (manager_id = 3 OR manager_id = 4)
• BETWEEN operations can specify a range
• Example: SELECT last_name, salary FROM employees WHERE salary BETWEEN 20000 AND 22000
• IN / NOT IN to specify a set of values
• Example: SELECT first_name, last_name, manager_id FROM employees WHERE manager_id IN (109, 3, 16)

Comparing with NULL

• NULL is a special value which signifies a missing value
• NULL is not the same as zero or a blank space
• To check for NULL values in SQL use IS NULL:
  • Example: SELECT last_name, manager_id FROM employees WHERE manager_id IS NULL

Sorting with ORDER BY Clause

• An ORDER BY clause sorts rows
• ASC sorts values by ascending order
• DESC sorts vcalues by descending order
• Example: SELECT last_name, hire_date FROM employees ORDER BY hire_date DESC

Inserting Data

• Insert a new employee such as INSERT INTO employees(email, first_name, last_name) VALUES ('[email protected]', 'Radina', 'Georgieva')

Updating table

• The SQL UPDATE command can update table values
• Ensure to use the WHERE cause, such as UPDATE employees SET last_name = 'Brown' WHERE id = 1

Examples of updating Data

• Update existing post - change title
• UPDATE posts SET title = 'Title Updated!' WHERE id = 2;
• Update existing post - change date
• UPDATE posts SET date = STR_TO_DATE('31-12-2018', '%d-%m-%Y') WHERE YEAR(date) = 2018;

Update Projects tips

• To mark all unfinished projects as being completed today, ensure your end_date is set to Null
• UPDATE projects SET end_date = '2017-01-23' WHERE end_date IS NULL

Deleting Data

• SQL command DELETE FROM employees WHERE id = 6 can be used to delete rows from a table
• Ensure you use the WHERE clause, otherwise all data from the table may be deleted
• Example: DELETE FROM employees WHERE first_name = 'Hristo';

Deleting Databases items

• Structures can be deleted such as keys, constraints tables and entire datasets
• Ensure these actions are used with caution, as they cannot be undone

Dropping, Deleting and Truncating

• Delete entries in a table such as "DELETE FROM employees" or "TRUNCATE TABLE employees"
• Drop a table: DROP TABLE employees
• Drop a Databse: DROP DATABASE people

Setting up

• To setup the database, use commands such as CREATE TABLE drivers(id INT PRIMARY KEY,name VARCHAR(50));
• CREATE TABLE cars(id INT PRIMARY KEY, driver_id INT UNIQUE, CONSTRAINT fk_cars_drivers FOREIGN KEY (driver_id) REFERENCES drivers(id));

Foreign Keys

• FOREIGN KEY (driver_id) REFERENCES drivers(id)
• fk_cars_drivers : Constraint name
• drivers : referent Table
• primary Key : primary Key

MySQLi vs PDO

• PHP Data Objects (PDO) and MySQL Improved (MySQLi) are both PHP extensions that allow PHP developers to interact with MySQL databases.
• PDO provides a versatile and consistent approach to multiple database systems due to its prepared statements
• MySQLi is tailored for MySQL databases and provides both procedural and object-oriented interfaces
• The choice between PDO and MySQLi depends on factors such as personal preference, project requirements, and compatibility with existing codebases.

MySQL in PHP Connections

• Use mysqli class to connect to MySQL from PHP script
• Example: $host = 'localhost'; $user = 'root'; $pass = ''; $db = 'blog'; $mysqli = new mysqli($host, $user, $pass, $db);
• $mysqli->set_charset("utf8")
• die is similar to echo $ print but exits the execution

MysQL in PHP

• $result = $mysqli->query('SELECT * FROM posts');
• if (!$result) die('Cannot read posts table');

Fetch Records

• Process the returned result set (table rows / records)
• $title = $row[ title'];
• $content = $row['content'];

Prepared Statement

• Example: deletePost($mysqli, $id) { $statement = $mysqli->prepare("DELETE FROM posts WHERE id = ?"); $statement->bind_param("i", $id); $statement->execute(); return $statement->affected_rows > 0; }
• s = string
• i = integer
• d = double

PDO

• $host = "localhost";
• $db = "erp_system";
• $user = "root";
• Note: always put the connection code in a separate file.

Connect and process Queues

• Try{ $db = new PDO('mysql:host=localhost;dbname=test',$user, $pass); $result = $db->query('SELECT * FROM Users', PDO::FETCH_ASSOC); foreach ($result as $row) {
• print_r($row); }
• Always null out the results

Prepared Statements

• $stmt = $db->prepare("SELECT * FROM Users WHERE fname = ?"); if ($stmt->execute(array($_GET['fname']))) print "Error!: ". $e->getMessage(). "";

Bind parameters

$stmt->bindParam(1, $fname); $stmt->bindParam(2, $lname);

PDO INSERT Statement

• $fname = 'Joro'; $lname = 'Petrov'; $stmt->execute(); $fname = 'Vasil'; $lname = 'Georgiev'; $stmt->execute();

PDO Named Parameter INSERT

• $stmt->execute(["fname" => "Azad" "Iname"=> "Kamaran"]);

Including and Requiring

• include and require load and evaluate a file holding PHP code
• header.php has echo...
• footer.php has echo...
• conn.php has $db = new PDO(...);
• Main.php require "conn.php"; require "header.php"; , echo "page body"; and include "footer.php";
• If a file is not found include produces a warning. require produces a fatal error

Require_once vs Include_once

• require "header.php"; function test(); echo "Page body"; include "header.php";
• Results in "Fatal error: Cannot redeclare test()..."