PHP and MySQL

1

• PHP and MySQL
• Alex Matan
• St. David CSS, Waterloo
• http//alex.matan.ca/contact
• http//www.WebStaffRoom.ca

2
Agenda

• Introduction
• XAMPP
• phpMyAdmin
• SQL
• Create a Database and User
• Create a Table
• CRUD
• PHP Code Example

3
Introduction

• PHP
• PHP Hypertext Preprocessor
• HTML-embedded scripting language
• Free, open-source
• Apache
• Web server
• Free, open-source
• MySQL
• Database management system (DBMS)?
• Free, Open-source
• XAMPP easily brings them all together

4
XAMPP

• Single file download
• Windows, Mac, Linux
• 10 minute installation (yes/yes/OK/I
  agree/Finish)?
• Apache web server
• MySQL database management system (DBMS)?
• phpMyAdmin web-based database administrator
• Can run off of a memory stick
• Shortcut to desktop launches control panel

5
phpMyAdmin

• Web-based database administration tool for MySQL
• Written in PHP
• Allows you to manage a database without knowing
  any SQL

6
SQL

• Structured Query Language
• English-like syntax for interacting with a
  database

7
Create a Database and User

• http//192.168.1.50/phpMyAdmin
• Click SQL button on left sidebar
• CREATE DATABASE yourdatabasename
• GRANT ALL PRIVILEGES ON yourdatabasename. TO
  'yourusername'_at_'localhost' IDENTIFIED BY
  'yourpassword'
• Click Go.
• Your SQL query has been executed successfully

8
Create a Table

• Click on yourdatabasename on leftside
• Create a new table called students with 5 fields
• id, INT, auto_increment, primary key
• firstname, varchar, 50
• lastname, varchar, 50
• city, varchar, 50
• start, datetime
• Click Go
• CREATE TABLE test1.students ( id INT NOT
  NULL AUTO_INCREMENT PRIMARY KEY , firstname
  VARCHAR( 50 ) NOT NULL , lastname VARCHAR( 50 )
  NOT NULL , city VARCHAR( 50 ) NOT NULL ,
  start DATETIME NOT NULL ) ENGINE MYISAM

9
CRUD

• Create, Read, Update, Destroy
• The basic operations that you can perform on any
  database table.
• Create record(s)?
• Read record(s)?
• Update record(s)?
• Delete record(s)?

10
CRUD - Create

• Create
• Insert a row into a table
• INSERT INTO students (firstname, lastname,
  city) VALUES('Alex', 'Matan', 'Waterloo' )?

11
CRUD - READ

• Read
• Get all rows in a table
• SELECT FROM students
• Get only rows where firstname'bob'
• SELECT FROM students WHERE firstname'Alex'
• Get only rows where firstname include the string
  'ba'
• SELECT FROM students WHERE firstname LIKE 'le'

12
CRUD Update and Delete

• Update
• UPDATE students SET firstname'Al' WHERE id'1'
• Delete
• DELETE FROM students WHERE lastname'Matan'

13
PHP Code Example

• Konqueror
• smb//192.168.1.50
• Create your own folder called lastname
• Open another Konqueror
• http//192.168.1.50/lastname

14
Thank You

• Questions?