Tools

  1. MAMP
  2. MySQLWorkBench
  3. Restlet client

SQL Script by MySQLWorkBench

New Model -> Add diagram -> Design tabels -> Export SQL Script file

Create Database on MAMP

  1. Enter MAMP MySQL in terminal:
    $/Applications/MAMP/Library/bin/mysql -uroot -p
    password : ‘root’

  2. excute .sql file
    $source '.sql file directory'

  1. MySQL Commands
    1
    2
    3
    4
    5
    6
    show databases;
    use 'DB_name';
    show tables;
    insert into 'table_name' values (v1 for column 1, v2, ...); 
    // can also insert by phpMyAdmin or MySQLWorkBench
    select * from 'table_name';

MySQL column flags meaning

flag meaning
PK primary key
NN not null
UQ unique
BIN binary (larger than text)
UN unsigned (not negtive)
ZF zero fill (fill with 0, e.g int(4) 1 -> 0001)
AI auto increment (for id#)
G generated column

\g \G in SELECT statement:
\g -> ; (a statement terminator)
\G -> causes the display to be laid out vertically

php connect to database in MAMP

change MySQL port to 8889

1
2
3
// /lib/db.php
$pdo = new PDO('mysql:host=localhost:8889;dbname=mydb','root','root');
return $pdo;

full example to test if it connected to database

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
$dbms='mysql';     //database type
$host='localhost:8889'; //host_name
$dbName='mydb';    //database_name
$user='root';      
$pass='root';          
$dsn="$dbms:host=$host;dbname=$dbName";

try {
    $dbh = new PDO($dsn, $user, $pass); //initialize a PDO object
    echo "connected!<br/>";
    $user = new User($dbh);
    print_r($user->signup('admin','admin')); 
    
    $dbh = null;
} catch (PDOException $e) {
    die ("Error!: " . $e->getMessage() . "<br/>");
}