PHP: PDO snippets

gistfile1.php

// PDO Connection to MySQL
$conn = new PDO('mysql:host=localhost;dbname=yourdbname', 'username', 'password');

// PDO Connection to PostgreSQL
$conn = new PDO('pgsql:host=localhost;dbname=yourdbname', 'username', 'password');

// A quick Select Query with For Loop
foreach ($conn->query("SELECT * FROM profile") as $row) 
    echo $row['fullname'];

// Variable Injection
$query = $conn->prepare("SELECT * FROM profile WHERE username = :username LIMIT 1");
$query->bindParam(":username", "knightofarcadia");
$query->execute();
$profile = $query>fetch( PDO::FETCH_ASSOC );
echo $profile['fullname'];
 
// Variable Injection with multi-row set
$query = $conn->prepare("SELECT * FROM profile WHERE hometown = :hometown");
$query->bindParam(":hometown", "Wessex");
$query->execute();
foreach($query->fetch(PDO::FETCH_ASSOC) as $row) {
    echo $row["fullname"];
}

// Creation
$createsql = $conn->prepare("CREATE TABLE profiles (username VARCHAR(64), fullname VARCHAR (128), hometown VARCHAR(128)"));
$conn->query($createsql);

// Insertion
$query = $conn->prepare($insertsql);
$query->bindParam(":username", "knightofarcadia");
$query->bindParam(":fullname", "Arthur Pendragon");
$query->bindParam(":hometown", "Wessex");
$query->execute();

// Updating
$query = $conn->prepare("UPDATE profiles SET fullname = :fullname WHERE username = :username");
$query->bindParam(":fullname", "Arthur Pendragoon");
$query->bindParam(":username", "knightofarcadia");
$query->execute();

// Deletion
$query = $conn->prepare("DELETE FROM profiles WHERE `username` = :username");
$query->bindParam(":username", "knightofarcadia");
$query->execute();

// MS SQL Server Connection
$conn = new PDO("sqlsrv:server=localhost;database=yourdbname", "username", "password");

// IBM DB2 Connection
$conn = new PDO("ibm:DRIVER={IBM DB2 ODBC DRIVER};DATABASE=yourdbname;HOSTNAME=localhost;PORT=56789;PROTOCOL=TCPIP;","username",  "password");

// Transactions
try {  
    $conn->beginTransaction();
    $insertsql = $conn->prepare("INSERT INTO profiles (username, fullname, hometown) VALUES ('wilfred', 'Wilfred Jones', 'Scarborough')");
    $deletesql = $conn->prepare("DELETE FROM profiles WHERE username = 'username'" );
    $conn->exec($insertsql);
    $conn->exec($deletesql);
    $conn->commit();
} catch (Exception $e) {
    $conn->rollBack();
    // message accessible with: $e->getMessage();
}


/** SQL for random numbers:
*
* MySQL: SELECT RAND([seed]);
* MS SQL: SELECT RAND([seed]);
* PostgreSQL: SELECT RANDOM(); (to set the seed you must run SETSEED([seed])  beforehand)
* Oracle DB: SELECT dbms_random.random FROM dual;
* SQLite: SELECT RANDOM();
*
**/