Web Development 3

PHP & MySQL (Part 1)

Ashley Cahill
[email protected]
 Recap
• Web Processing

• Web History

• PHP History

• Echo

• Variables

• Arithmetic Operations

• Forms

• Validation
 Recap
• Exercise 1
• Discount Calculator

• Exercise 2
• Discount Calculator with Validation
 Learning Outcomes
• History of MySQL.

• Connecting to a database.

• Executing SELECT statements.

• Getting data from a result set.

• INSERT, UPDATE and DELETE statements.

• try/catch for Exception handling.

 Installation/Setup
• XAMPP

• MySQL Workbench

• NetBeans
NetBeans Configuration
 XAMPP Configuration

• Apache and MySQL services must be running.

• Take note of the MySQL Port (usually 3306 or 3307)
• You may need to reconfigure this in my.ini file if the port is blocked)
 History of MySQL
• MySQL is one of the most widely used open source relational database
management systems in the world.

• Developed in 1994 by a Swedish company, MySQL AB.

• Founded by David Axmark, Allan Larsson & Michael "Monty" Widenius.

• Named after co-founder Monty Widenius's daughter, My.

• First version internally released in 1995.

 History of MySQL
• 1996, version 3.18 released.

• 1998, version 3.20 released for Windows.

• 2000, changed to Open Source.

• 2003 version 4.0 released.

 History of MySQL
• 2005, Oracle purchased Innobase, the company which managed MySQL’s
Innobase storage backend.

• 2005, version 5.0 released.

• 2006, Oracle purchased Sleepycat, the company that manages the

Berkeley DB transactional storage engine of MySQL.
 History of MySQL
• 2008, MySQL was acquired by Sun Microsystems.

• 2009, Oracle purchased Sun Microsystems along with MySQL copyrights

and trademarks.

• 2010, version 5.5 released.

• 2018, version 8.0 released.

 Setting Up a Database
• Launch XAMPP and start Apache and MySQL.
• Check the MySQL Port (usually 3306/3307)

• Launch MySQL Workbench.

• Create MySQL Connection

• Create database using MySQL Workbench.

 Setting Up a Database
• Open MySQL Workbench

• Create a new connection

 Setting Up a Database
• Open MySQL Workbench

• Create a new connection

• Give it a meaningful name and set the Port to the Port your MySQL
Service is running in XAMPP
Setting Up a Database
 Lab
• Create a Database “WebDev3_2024”

• Create a database table “car_details”

• Insert 1 record

… Check out the car_setup.sql file on Moodle

 Creating a Database
DROP DATABASE IF EXISTS WebDev3_2024;

CREATE DATABASE IF NOT EXISTS WebDev3_2024;

USE WebDev3_2024;
 Creating a Table
CREATE TABLE car_details
(

id int NOT NULL AUTO_INCREMENT,

make varchar(233)NOT NULL,

modelvarchar(233)NOT NULL,

PRIMARY KEY(id)

);
 Inserting Data

INSERT INTO car_details (make, model) VALUES (‘Ford’, ‘Fiesta’);

 Connecting To A Database
• Connecting to a database makes use of the PHP Data Object (PDO)
interface.

• Defines a lightweight, consistent interface for accessing databases.

• Must use a database-specific PDO driver to access a database server.

• PDO provides a data-access abstraction layer, which means that,

regardless of the database, you use the same functions to issue
queries and fetch data.
Connecting To A Database (contd.)

• How to connect to a MySQL database

$dsn = 'mysql:host=localhost;dbname=WebDev3_2024';

$username = 'root';

$password = '';

// create PDO object

$db = new PDO($dsn, $username, $password);

Connecting To A Database (contd.)
• How to connect to a MySQL database

$dsn='mysql:host=127.0.0.1:337;dbname=WebDev3_2024';

$username = 'root';

$password = '';

// creates PDO object

$db = new PDO($dsn, $username, $password);

 Lab

• Write a PHP file named “connect.php” to connect to the

WebDev3_2023 database.

• Print out a message to the browser “Connected to the Database!”

• Remember … clicking the green arrow in NetBeans will run every file
you have open

• Right click on your file and select “Run File” to run the individual file
 Lab
<?php

//$dsn='mysql:host=localhost;dbname=WebDev3_2024';

$dsn='mysql:host=127.0.0.1:3307;dbname=WebDev3_2024';

$username = 'root';

$password = '';

$db = new PDO($dsn, $username, $password);

echo 'Connected to Database!';

?>
 Programming
• Programming takes perseverance

• Often requires trial and error

• Also requires some independent thought!

• Also, read the error messages. PHP tries to tell you the
exact cause (file, line number etc.)
• Don’t just give up!!
 Common Issues With Connect

• $dsn='mysql:host=127.0.0.1:3307;dbname=WebDev3_2024’;

Check Port?
Is XAMPP running on
3306/3307/3309?!?!
 Common Issues With Connect

• $dsn='mysql:host=127.0.0.1.3307;dbname=WebDev3_2024';

Port listed as part of IP Address

127.0.0.1.3307
127.0.0.1:3307
 Common Issues With Connect

• $password = '';

Password set as '' instead of ''

Double Quote instead of 2 single quotes
 Exception Handling
• try/catch

• The syntax for a try/catch statement

try {

// statements that might throw an exception

} catch (ExceptionClass $exception_name) {
// statements that handle the exception
}
Exception Handling – Any Exception
• How to handle any type of exception

try {

// statements that might throw an exception

} catch (Exception $e) {

$error_message = $e->getMessage();

echo "<p>Error message: $error_message </p>";

}
 PDO Exception Handling
• How to handle a PDO exception

try {

$db = new PDO($dsn, $username, $password);

echo '<p>You are connected to the database!</p>';

} catch (PDOException $e) {

$error_message = $e->getMessage();

echo '<p>An error occurred while connecting to the database: ‘ . $error_message . '</p>';

}
 Lab
• Add exception handling to the php file used to connect to the database

• On successful connection, display “Connected To Database”

• If error display “An error has occurred” and then display the actual PDO error
message