Lecture 18 - SQL: Create, Insert, PHP

Today's Agenda

HW 6 Introduction

Database manipulation (create/insert)

• CREATE TABLE
• INSERT INTO TABLE

Accessing SQL from PHP

Course outline

HTML: Webpage content

CSS: Webpage presentation

JavaScript: Webpage functionality (client-side)

AJAX: Fetching data from the internet

PHP: Server-side code with PHP

Regular Expressions: Validating input

SQL: Storing data

Full-stack website organization

A Full stack website consists of three layers.

1. Web Browser (client): HTML, CSS, JS
2. Web Server: PHP
3. Database Server: SQL

• The web server makes requests of the database server much like our javascript used AJAX
• Unlike our asynchonous AJAX calls, our PHP code will only be accessing our databases in a synchronous manner.

Running a MySQL server on cloud9

Note: full instructions will be in tomorrows lab.

1. Open a new terminal (Window menu or Alt-T)
2. Type in mysql-ctl start to start the MySQL server
3. Type in mysql-ctl cli to begin entering SQL commands

Databases

Each MySQL server can have multiple databases.

CREATE DATABASE database_name; creates a new database

SHOW DATABASES; displays a list of the databases

USE database_name; selects a database for use

CREATE TABLE

CREATE TABLE is used to create a new table.

Syntax:

CREATE TABLE table_name(
   column1 datatype,
   column2 datatype,
   column3 datatype,
   .....
   columnN datatype,
   PRIMARY KEY( one or more columns)
);

Data types are listed here.

Other table commands

SHOW TABLES; displays a list of the tables

DESCRIBE table_name; display the columns of the table

DROP TABLE table_name; deletes a table

INSERT

INSERT is used to insert a new record into an existing table.

Syntax:

INSERT INTO TABLE_NAME (column1, column2,...columnN) 
  VALUES (value1,value2,...valueN);

Example:

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY)
  VALUES (1, 'Ramesh', 32, 'Ahmedabad', 2000.00);

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY)
  VALUES (2, 'Khilan', 25, 'Delhi', 1500.00);

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY)
  VALUES (3, 'kaushik', 23, 'Kota', 2000.00);

Querying a Database in PHP with PDO

$name = new PDO("mysql:dbname=database;host=server;charset=utf8", username, password);
$name->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$name->query("SQL query"); 

$db = new PDO("mysql:dbname=world;host=localhost;charset=utf8", "root", "");
$db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$db->query("SELECT * FROM countries WHERE population > 100000000;");
 

PDO object's query function returns rows that match a query

Result rows: query

$db = new PDO("mysql:dbname=world;host=localhost;charset=utf8", "root", "");
$db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$rows = $db->query("SQL query");
foreach ($rows as $row) {
   do something with $row;
}
 

query returns all result rows

• each row is an associative array of [column name -> value]
• example: $row["population"] gives the value of the population column

Inserting through PHP

$count = $db->exec("INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY)
  VALUES (1, 'Ramesh', 32, 'Ahmedabad', 2000.00);");
 

exec runs the given SQL code and returns the number of rows affected.

Including variables in a query

# get query parameter for name of movie
$title = $_GET["movietitle"];
$rows = $db->query("SELECT year FROM movies WHERE name = '$title'");
 

NOTE: THIS CODE IS NOT SECURE!

We will discuss security and how to make this code safe later in the quarter.