PDO.php

<?php

// why PDO
// PDO provides a data-access abstraction layer
// PDO does not provide a database abstraction -->
// Full Structure of PDO
// PDO {
// __construct ( string $dsn [, string $username [, string $password [, array $driver_options ]]] )
// bool beginTransaction ( void )
// bool commit ( void )
// mixed errorCode ( void )
// array errorInfo ( void )
// int exec ( string $statement )
// mixed getAttribute ( int $attribute )
// static array getAvailableDrivers ( void )
// bool inTransaction ( void )
// string lastInsertId ([ string $name = NULL ] )
// PDOStatement prepare ( string $statement [, array $driver_options = array() ] )
// PDOStatement query ( string $statement )
// string quote ( string $string [, int $parameter_type = PDO::PARAM_STR ] )
// bool rollBack ( void )
// bool setAttribute ( int $attribute , mixed $value ) }mp($var_name);
// }



//Connect to database

$host = 'localhost';
$user = 'root';
$password = '';
$dbname = 'test-bm';
$charset = 'utf8mb4'; //for persian support language
//Handling errors
try {
    // Set DSN-> PDO_MYSQL 'Data Source Name'
    $dsn = 'mysql:host=' . $host . ';dbname=' . $dbname . ';charset=' . $charset;
    $pdo = new PDO($dsn, $user, $password);
    $pdo->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_ASSOC);
    // echo 'successfull connection;
} catch (PDOException $error) {
    $error->getMessage();
    echo "database faild to connect!!";
}
//Prepared statements
//Positional
$stmt = $pdo->prepare('SELECT * FROM users WHERE email = ? AND status=?');
$stmt->execute([$email, $status]);
$user = $stmt->fetch();
//named
$stmt = $pdo->prepare('SELECT * FROM users WHERE email = :email AND status=:status');
$stmt->execute(['email' => $email, 'status' => $status]);
$user = $stmt->fetch();
//explicit binding
/* Execute a prepared statement by binding PHP variables */
$calories = 150;
$colour = 'red';
$sth = $dbh->prepare('SELECT name, colour, calories
    FROM fruit
    WHERE calories < :calories AND colour = :colour');
$sth->bindValue(':calories', $calories, PDO::PARAM_INT);
$sth->bindValue(':colour', $colour, PDO::PARAM_STR);
$sth->execute();
//foreach
//ex.1
$stmt = $pdo->prepare('UPDATE users SET bonus = bonus + ? WHERE id = ?');
foreach ($data as $id => $bonus) {
    $stmt->execute([$bonus, $id]);
}
//ex.2
$stmt = $pdo->query('SELECT name FROM users');
foreach ($stmt as $row) {
    echo $row['name'] . "\n";
}
//method chaining
$sql = "UPDATE users SET name = ? WHERE id = ?";
$pdo->prepare($sql)->execute([$name, $id]);
//getting data fetch()
//only a row
$row = $stmt->fetch(PDO::FETCH_ASSOC);
//use data before proccess(ex...edit,show,work on it....)
$stmt = $pdo->query('SELECT name FROM users');
while ($row = $stmt->fetch()) {
    echo $row['name'] . "\n";
}
//get obj from class
$news = $pdo->query('SELECT * FROM news')->fetchAll(PDO::FETCH_CLASS, 'News');
//retuning values on emulate mode = off
// create table typetest (string varchar(255), `int` int, `float` float, `null` int);
// insert into typetest values('foo',1,1.1,NULL);
// array(4) {
//     ["string"] => string(3) "foo"
//     ["int"]    => int(1)
//     ["float"]  => float(1.1)
//     ["null"]   => NULL
//   }
//fetchColumn
$stmt = $pdo->prepare("SELECT name FROM table WHERE id=?");
$stmt->execute([$id]);
$name = $stmt->fetchColumn();
//from multi row
$count = $pdo->query("SELECT count(*) FROM table")->fetchColumn();
//fetchAll
//simple array
$data = $pdo->query('SELECT name FROM users')->fetchAll(PDO::FETCH_ASSOC);
var_export($data);
/*
array (
0 => array('John'),
1 => array('Mike'),
2 => array('Mary'),
3 => array('Kathy'),
)*/
//column
$data = $pdo->query('SELECT name FROM users')->fetchAll(PDO::FETCH_COLUMN);
/* array (
0 => 'John',
1 => 'Mike',
2 => 'Mary',
3 => 'Kathy',
)*/
//key-value
$data = $pdo->query('SELECT id, name FROM users')->fetchAll(PDO::FETCH_KEY_PAIR);
/* array (
104 => 'John',
110 => 'Mike',
120 => 'Mary',
121 => 'Kathy',
)*/
//FETCH_UNIQUE
$data = $pdo->query('SELECT * FROM users')->fetchAll(PDO::FETCH_UNIQUE);
/* array (
104 => array (
'name' => 'John',
'car' => 'Toyota',
),
110 => array (
'name' => 'Mike',
'car' => 'Ford',
),
120 => array (
'name' => 'Mary',
'car' => 'Mazda',
),
121 => array (
'name' => 'Kathy',
'car' => 'Mazda',
),
)*/
//FETCH_GROUP 
$data = $pdo->query('SELECT sex, name, car FROM users')->fetchAll(PDO::FETCH_GROUP);
array(
    'male' => array(
        0 => array(
            'name' => 'John',
            'car' => 'Toyota',
        ),
        1 => array(
            'name' => 'Mike',
            'car' => 'Ford',
        ),
    ),
    'female' => array(
        0 => array(
            'name' => 'Mary',
            'car' => 'Mazda',
        ),
        1 => array(
            'name' => 'Kathy',
            'car' => 'Mazda',
        ),
    ),
);
//search [sql = like]
$search = "%$search%";
$stmt  = $pdo->prepare("SELECT * FROM table WHERE name LIKE ?");
$stmt->execute([$search]);
$data = $stmt->fetchAll();
//IN
$arr = [1, 2, 3];
$in  = str_repeat('?,', count($arr) - 1) . '?';
$sql = "SELECT * FROM table WHERE column IN ($in)";
$stm = $db->prepare($sql);
$stm->execute($arr);
$data = $stm->fetchAll();
//stored procedure
$stmt = $pdo->prepare("CALL foo()");
$stmt->execute();
do {
    $data = $stmt->fetchAll();
    var_dump($data);
} while ($stmt->nextRowset() && $stmt->columnCount());
// search by date range
$date1 = date("Y-m-d H:i:s", $_GET['dateFirst']);
$date2 = date("Y-m-d H:i:s", $_GET['dateSecond']);
$results = $pdo->prepare("SELECT * FROM `workorders` WHERE `work_order_date` BETWEEN '$date1' AND '$date2'");
$query = "SELECT * FROM table_name WHERE `date` BETWEEN '{$date1}%' AND '{$date2}%'";
//'$date 00:00:00' AND '$date 00:00:00'
$query = "SELECT * FROM table_name WHERE `date` LIKE '{$date}%'";