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();
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
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
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.
Maybe would be good to add the connection statements too. this relies on one already having a $db object set up 🙂
Good resume. You can add “$stmt->rowCount();” for count the rows affected for a update or delete. This is very useful.
Bye