New Topic/Question
Reply
Maybe something wrong in functions?
Also warning that it couldn't fethch mysqli- how to get rid of this warning?
<?php
/*
* To change this template, choose Tools | Templates
* and open the template in the editor.
*/
/**
* Description of database_api
*
* @author xxx*/
class DatabaseAPI extends MysqlConnection {
var $daily_fine = 0.75;
/**
* Acts as a filter on the library catalog, returning all books if both
* parameters are empty. Filters results depending on input. Each
* input type (title/author) is applied as a separate filter (i.e. more data
* narrows search, doesn't increase results). Partial strings are supported.
*
* @param String title any title keywords, may be empty string
* @param String author_name author partial or full name on the form
* "first_name last_name", my be empty string
* @return Book[] an array of book objects, each book object
* represents a book returned by the search
*/
//CORRECT BY NEW TABLES
function Search($title = '', $author_name = '') {
$Select_sql = "SELECT `id`,`isbn`,`book_title`,`author_id`,`book_edition` FROM `book` WHERE `book_title`=$title or `author_name`=$author_id";
// Create/prepare the statement and errors if problems
$Select_stmt = $this->connection->stmt_init();
if (!$Select_stmt->prepare($Select_sql)) {
$this->error($Select_stmt->error);
}
// Bind user data to the sql statement and execute it
$Select_stmt->bind_param('sssssss', $first_name, $last_name,
$email, $address, $city, $state, $zip);
$Select_stmt->execute();
// If there's been an in execution error, display it
if ($Select_stmt->error) {
$this->error($insert_stmt->error);
}
// Close the statement resource
$Select_stmt->close();
}
/**
* Reserves a book for a user if that book is available
*
* @param int inventory_id the inventory id of the book to be reserved
* @param int user_id the user id of the reservee
* @return boolean true on successful reserve, false if the book wasn't in
* circulation when the reserve was attempted
*/
function ReserveBook($inventory_id, $user_id) {
// Put the reserve expiration, 1 week from today
$reserve_expiration = strtotime('now +1 week');
$reserve_expiration = date('Y-m-d', $reserve_expiration);
// Update inventory, set book to reserved for a particular user
// but only if the status is currently set to "in" (i.e. no changes
// have occurred since reserve process began)
$update_sql = "UPDATE inventory SET status = 'reserved', user_id = ?, "
. "reserve_expiration = ? WHERE status = 'in' AND id = ?";
$update_stmt = $this->connection->stmt_init();
if (!$update_stmt->prepare($update_sql)) {
$this->error($update_stmt->error);
}
$update_stmt->bind_param('isi', $user_id, $reserve_expiration, $inventory_id);
$update_stmt->execute();
// Capture how many rows were updated
// Should be one on successful reserve, 0 otherwise
$rows_updated = $update_stmt->affected_rows;
$update_stmt->close();
// Return true if the UPDATE actually updated a row
return ($rows_updated == 1);
}
/**
* Attempts to checkout a book
*
* @param int inventory_id the inventory id of the book to checkout
* @param int user_id the user id of the user to check the book out to
* @return boolean true if checkout was successful, false if book was
* out of circulation
*/
//CORRECT BY NEW TABLES
function CheckoutBook($inventory_id, $user_id) {
$update_sql = "insert into `transactions` (`id`,`book_id`,`issue_date`,`return_date`,`act_rtn_Date`,`user_id`,`charges`,`fine') Values (Null,$book_id,$issue_date`,$return_date,$act_rtn_Date,$user_id,$charges,$fine) ";
$update_stmt = $this->connection->stmt_init();
if (!$update_stmt->prepare($update_sql)) {
$this->error($update_stmt->error);
}
// Bind the amount and user id to the query and execute it
$update_stmt->bind_param('di', $amt, $user_id);
$update_stmt->execute();
// If there's been an error in execution, display it
if ($update_stmt->error) {
$this->error($update_stmt->error);
}
$update_stmt->close();
}
/**
* Check a book back in, applying any accrued late fees to the user who
* had the book using AdjustBalance
*
* @param int inventory_id the inventory id of the book that was checked in
*/
//CORRECT BY NEW TABLES
function CheckinBook($inventory_id,$status) {
$update_sql = "UPDATE `inventory` SET STATUS =$status WHERE `id` =$inventory_id";
$update_stmt = $this->connection->stmt_init();
if (!$update_stmt->prepare($update_sql)) {
$this->error($update_stmt->error);
}
// Bind the amount and user id to the query and execute it
$update_stmt->bind_param('di', $amt, $user_id);
$update_stmt->execute();
// If there's been an error in execution, display it
if ($update_stmt->error) {
$this->error($update_stmt->error);
}
$update_stmt->close();
}
/**
* Changes a users account balance. Pass a negative number to decrease balance.
*
* @param float amt the amount to increase or decrease a users balance (negative for decrese)
* @param int user_id the user id to set the account balance for
*/
//CORRECT BY NEW TABLES
function AdjustBalance($amt, $user_id) {
// Query to update the users account balance
$update_sql = "UPDATE `user` SET acc_balance =$amt WHERE ID =$user_id"; //CORRECT this sql query BY NEW TABLES
$update_stmt = $this->connection->stmt_init();
if (!$update_stmt->prepare($update_sql)) {
$this->error($update_stmt->error);
}
// Bind the amount and user id to the query and execute it
$update_stmt->bind_param('di', $amt, $user_id);
$update_stmt->execute();
// If there's been an error in execution, display it
if ($update_stmt->error) {
$this->error($update_stmt->error);
}
$update_stmt->close();
}
/**
* Add a new book to the database
*
* @param String isbn
* @param String title
* @param int year
* @param String[][] authors see description in AssociateAuthorsWithBook()
* @param int copies the number of copies the library has of this book
*/
//CORRECT BY NEW TABLES
function AddBook($isbn, $title, $year, $authors, $copies) {
// Insert the user query
$insert_sql = "INSERT INTO user "
. "(first_name, last_name, email, address, city, state, zip)"
. " VALUES (?, ?, ?, ?, ?, ?, ?)";
// Create/prepare the statement and errors if problems
$insert_stmt = $this->connection->stmt_init();
if (!$insert_stmt->prepare($insert_sql)) {
$this->error($insert_stmt->error);
}
// Bind user data to the sql statement and execute it
$insert_stmt->bind_param('sssssss', $first_name, $last_name,
$email, $address, $city, $state, $zip);
$insert_stmt->execute();
// If there's been an in execution error, display it
if ($insert_stmt->error) {
$this->error($insert_stmt->error);
}
// Close the statement resource
$insert_stmt->close();
}
/**
* Called by AddBook. Add a row to the inventory table for each copy of a book
*
* @param String isbn
* @param int copies the number of copies the library has of this book
*/
//CORRECT BY NEW TABLES
function AddBookCopies($isbn, $copies) {
$update_sql = "UPDATE `book` SET copies =$copies WHERE `isbn` =$isbn";
$update_stmt = $this->connection->stmt_init();
if (!$update_stmt->prepare($update_sql)) {
$this->error($update_stmt->error);
}
// Bind the amount and user id to the query and execute it
$update_stmt->bind_param('di', $amt, $user_id);
$update_stmt->execute();
// If there's been an error in execution, display it
if ($update_stmt->error) {
$this->error($update_stmt->error);
}
$update_stmt->close();
}
/**
* Called by AddBook to associate all authors with a book.
* Attempts to match authors currently in the database to authors
* in the authors array. If no author is found, one is added to the db.
*
* @param String[][] authors a 2D array of author first and last names where
* authors[0][0] is the first author's first name
* and authors[0][1] is the first author's last name
* @param String isbn the isbn of the book the authors should be added to
*/
function AssociateAuthorsWithBook($authors, $isbn) {
// Prepare a statement to look for an author in the database by name
$select_sql = "SELECT id FROM author WHERE first_name = ? AND last_name = ?";
$select_stmt = $this->connection->stmt_init();
if (!$select_stmt->prepare($select_sql)) {
$this->error($select_stmt->error);
}
// Prepare a statement to insert the book/author into the association
// table
$insert_sql = "INSERT INTO book_author VALUES (?, ?)";
$insert_stmt = $this->connection->stmt_init();
if (!$insert_stmt->prepare($insert_sql)) {
$this->error($insert_stmt->error);
}
// For each author in the authors array, look
// them up in the database, and add an entry to book_author
foreach($authors as $author) {
// Search for this author
$select_stmt->bind_param('ss', $author[0], $author[1]);
if (!$select_stmt->execute()) {
$this->error($select_stmt->error);
}
// If an author is found, it's id will be bound here
// author_id will either be set here or in the lines that follow
$select_stmt->bind_result($author_id);
// If no row returned, this author doesn't exist in the database
// yet, so add them and get the inserted row id
if (!$select_stmt->fetch()) {
$author_id = $this->AddAuthor($author[0], $author[1]);
}
// Free the result so we can use it next time around the loop
$select_stmt->free_result();
// Insert the author/book into the association table
$insert_stmt->bind_param('si', $isbn, $author_id);
if (!$insert_stmt->execute()) {
$this->error($insert_stmt->error);
}
}
// Close the statement resources
$insert_stmt->close();
$select_stmt->close();
}
/**
* Add an author to the database
*
* @param String fname
* @param String lname
* @return int the auto generated author id
*
*/
function AddAuthor($fname, $lname) {
// Prepare query to insert a new author
$insert_sql = "INSERT INTO author VALUES (null, ?, ?)";
// Create/prepare the statement, error if problems
$insert_stmt = $this->connection->stmt_init();
if (!$insert_stmt->prepare($insert_sql)) {
$this->error($insert_stmt->error);
}
// Bind author name to the query and execute it
$insert_stmt->bind_param('ss', $fname, $lname);
$insert_stmt->execute();
// If there's been an error in execution, display it
if ($insert_stmt->error) {
$this->error($insert_stmt->error);
}
// Retrieve the insert id from the inserted author and close the stmt
$author_id = $insert_stmt->insert_id;
$insert_stmt->close();
return $author_id;
}
/**
* Add a user (patron) to the database
*
* @param String first_name
* @param String last_name
* @param String email
* @param String address
* @param String city
* @param String state
* @param String zip
*/
function AddUser($first_name, $last_name, $email, $address, $city, $state, $zip) {
// Insert the user query
$insert_sql = "INSERT INTO user"
. "(first_name, last_name, email, address, city, state, zip, acc_balance)"
. " VALUES (?, ?, ?, ?, ?, ?, ?, ?)";
// Create/prepare the statement and errors if problems
$insert_stmt = $this->connection->stmt_init();
if (!$insert_stmt->prepare($insert_sql)) {
$this->error($insert_stmt->error);
}
// Bind user data to the sql statement and execute it
$insert_stmt->bind_param('sssssss', $first_name, $last_name,
$email, $address, $city, $state, $zip);
$insert_stmt->execute();
// If there's been an in execution error, display it
if ($insert_stmt->error) {
$this->error($insert_stmt->error);
}
// Close the statement resource
$insert_stmt->close();
}
}
?>
MultiQuote
| 