PHP: PDO snippets
// 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();
*
**/