This example shows how to populate an instance of the Book class with an existing row in the table. In this example, we will retrieve a row with a BookID of 23. Any table with an auto-increment primary key column will have the retrieveByPK() method, a static method that will search for a row with a primary key that matches the given value. If it finds one, it will populate and return a new object with the values of that row.
require_once('config.php');
//Retrieve the row from the database and populate a Book object
$myBook = Book::retrieveByPK(23);
//Set the values of some of the columns
$myBook->setTitle('Jurassic Park');
$myBook->setAuthor('Michael Crichton');
//Update the row in the database with the new values
$myBook->save();
?>
retrieveByPKs() is used whenever a table has multiple primary keys, which is much less common. The primary keys must be provided in the order that they appear in the table structure. The following example retrieves a row from the User_Company table, which has two primary keys: UserID and CompanyID:
require_once('config.php');
//Retrieve the row from the database with a UserID of 10 and a CompanyID of 349 and populate a User_Company object
$myBook = Book::retrieveByPKs(10, 349);
?>
IMPORTANT: This method will not escape any of the SQL for you, making custom queries vulnerable to SQL injection if they are not escaped properly. The DB class has a static method for escaping strings that can be used. For this and other reasons, I recommend the doSelect() method instead. This method also does not support joining multiple talbles. If you need to join, use the fetch() or doSelect() methods.
The following example will populate the $allBooks variable with an array containing objects representing every row in the Book table.
require_once('config.php');
//Retrieve all the books from the database. Equal to "SELECT * FROM Book"
$allBooks = Book::getAll();
//Loop through the array and print the book Titles
foreach($allBooks as $b){
echo $b->getTitle()."";
}
?>
This method is not very useful without the ability to narrow, group and sort the results, so you can provide custom MySQL to the method.
require_once('config.php');
//Retrieve all the books from the database by Dr. Seuss. Equal to "SELECT * FROM Book WHERE Author='Dr. Seuss' "
$allBooks = Book::getAll(" WHERE Author='Dr. Suess' ");
//Retrieve all the books from the database. Equal to "SELECT * FROM Book ORDER BY Author "
$allBooks = Book::getAll(" ORDER BY Author ");
//Loop through the array and print the book Titles
foreach($allBooks as $b){
echo $b->getTitle()."";
}
?>
Example using checkInput() to escape.
require_once('config.php');
//Author name for search
$author = $_POST['Author'];
//Escape the search string using checkInput(). Equal to mysql_real_escape_string();
$conn = Book::getConnection();
$authorEscaped = $conn->checkInput($author);
//Retrieve all the books from the database by this author
$allBooks = Book::getAll(" WHERE Author=$authorEscaped ");
//Loop through the array and print the book Titles
foreach($allBooks as $b){
echo $b->getTitle()."";
}
?>
IMPORTANT: Like getAll(), this method will not escape any of the SQL for you, making custom queries vulnerable to SQL injection if they are not escaped properly. The DB class has a static method for escaping strings that can be used. For this and other reasons, I recommend the doSelect() method instead.
Fetch will execute a raw SQL select query and convert the result set into an array of objects. This takes a little more code than getAll(), but offers more flexibility. The following example will populate the $allBooks variable with an array containing objects representing every row in the Book table.
require_once('config.php');
//Retrieve all the books from the database. Equal to "SELECT * FROM Book"
$allBooks = Book::fetch("SELECT * FROM Book");
//Loop through the array and print the book Titles
foreach($allBooks as $b){
echo $b->getTitle()."";
}
?>
doSelect() is another method for selecting multiple rows, but instead of using raw SQL strings for custom searches, it uses the Query object to create queries. The Query object is very useful because it can be easily passed around and modified by different scripts in your application, without complex string manipulation. It escapes inputs automatically, and the same Query could be used to delete, select, or count. The following example will populate the $allBooks variable with an array containing objects representing every row in the Book table.
require_once('config.php');
//Create new instance of Query
$q = new Query;
//Add search criteria
$q->add('Author',$_POST['Author']);
//Retrieve all the books from the database that match the query criteria
$allBooks = Book::doSelect($q);
//Loop through the array and print the book Titles
foreach($allBooks as $b){
echo $b->getTitle()."";
}
?>
(503) 746-9116 | 17933 NW Evergreen Parkway, Suite 220 | Beaverton, Oregon 97006