To accompany the mysqli cheatsheet here is a PDO cheatsheet.

Give me one record

$sql= "SELECT filmName, filmDescription FROM movies WHERE filmID = 10";
$stmt = $pdo->query($sql);
$row =$stmt->fetchObject();
echo $row->filmName;
echo $row->filmDescription;

Give me the whole lot

$sql= "SELECT * FROM movies";
$stmt = $pdo->query($sql);
while($row = $stmt->fetchObject()){
	echo $row->filmName;
}

One row from user input using prepare

$sql= "SELECT filmID, filmName, filmDescription, filmImage, filmPrice, filmReview FROM movies WHERE filmID = :filmID";
$stmt = $pdo->prepare($sql);
$stmt->bindParam(':filmID', $filmID, PDO::PARAM_INT);
$stmt->execute();
$row =$stmt->fetchObject();
//output
echo $row->filmName;

Multiple rows from user input using prepare

$filmName = "%".$_GET['filmName']."%";
$sql= "SELECT * FROM movies WHERE filmName LIKE :filmName";
$stmt = $pdo->prepare($sql);
$stmt->bindParam(':filmName', $filmName, PDO::PARAM_STR);
$stmt->execute();
$total = $stmt->rowCount();
//output
while ($row = $stmt->fetchObject()) {
// or could use
// while ( $row = $stmt->fetch( PDO::FETCH_OBJ ) ) {
	echo $row->filmName;
}

INSERT

$sql = "INSERT INTO movies(filmName,
			filmDescription,
			filmImage,
			filmPrice,
			filmReview) VALUES (
			:filmName,
			:filmDescription,
			:filmImage,
			:filmPrice,
			:filmReview)";
$stmt = $pdo->prepare($sql);
$stmt->bindParam(':filmName', $_POST['filmName'], PDO::PARAM_STR);
$stmt->bindParam(':filmDescription', $_POST['filmDescription'], PDO::PARAM_STR);
$stmt->bindParam(':filmImage', $_POST['filmImage'], PDO::PARAM_STR);
// use PARAM_STR although a number
$stmt->bindParam(':filmPrice', $_POST['filmPrice'], PDO::PARAM_STR);
$stmt->bindParam(':filmReview', $_POST['filmReview'], PDO::PARAM_STR);
$stmt->execute();

Get Last Insert ID

$stmt->execute();
$newId = $pdo->lastInsertId();

UPDATE

$sql = "UPDATE movies SET filmName = :filmName,
			filmDescription = :filmDescription,
			filmImage = :filmImage,
			filmPrice = :filmPrice,
			filmReview = :filmReview
			WHERE filmID = :filmID";
$stmt = $pdo->prepare($sql);
$stmt->bindParam(':filmName', $_POST['filmName'], PDO::PARAM_STR);
$stmt->bindParam(':filmDescription', $_POST['$filmDescription'], PDO::PARAM_STR);
$stmt->bindParam(':filmImage', $_POST['filmImage'], PDO::PARAM_STR);
// use PARAM_STR although a number
$stmt->bindParam(':filmPrice', $_POST['filmPrice'], PDO::PARAM_STR);
$stmt->bindParam(':filmReview', $_POST['filmReview'], PDO::PARAM_STR);
$stmt->bindParam(':filmID', $_POST['filmID'], PDO::PARAM_INT);
$stmt->execute();

DELETE

$sql = "DELETE FROM movies WHERE filmID =  :filmID";
$stmt = $pdo->prepare($sql);
$stmt->bindParam(':filmID', $_POST['filmID'], PDO::PARAM_INT);
$stmt->execute();

5 Thoughts to “PDO Cheatsheet”

  1. Joe Bradfield

    Great Site i can't tell you how helpful it has been in understanding PDO.

    Do you have any resources on building PDO pagination of query results.

    I can't seem to find simple ones that work

    Thanks

    1. admin

      Hi Joe - glad you found it useful. I don't have anything on pagination yet but can add it do my to do list. Essentially though I would approach it my using $_GET as your query source and then providing navigation through the results by passing a query string ie query=stuff&start=11. In the query you then use SQL's LIMIT to return a set of records. Experiment with LIMIT - sqlzoo a great place to just play . You would use something like:

      SELECT yr, city FROM games LIMIT 3,2

      This would return two records, starting at record 4.

      Apart from not using PDO this looks like a good explanation http://www.phpsimplicity.com/tips.php?id=1

  2. Genius !!!!!!! Ok sorry but you have saved me alot of time with your simple guides and this cheat sheet should help me fill in my knowledge gaps so i can start moving everything to PDO.

  3. Dave

    Maybe would be good to add the connection statements too. this relies on one already having a $db object set up 🙂

  4. Antonio

    Good resume. You can add "$stmt->rowCount();" for count the rows affected for a update or delete. This is very useful.
    Bye

Leave a Comment