Professional PHP for working with MySQ

index

<?php
require_once('util/main.php');

require_once('model/database.php');
require_once('model/product_db.php');
require_once('model/category_db.php');
require_once('model/product.php');
require_once('model/category.php');

/*********************************************
 * Select some products
 **********************************************/

// Sample data
$cat_id = 1;

// Get the products
$products = ProductDB::getProductsByCategory($cat_id);

/***************************************
 * Delete a product
 ****************************************/

// Sample data for the product name
$product_name = 'Fender Telecaster';

// get by mane
$products = ProductDB::getProductsByName($product_name);

$product_id = $products[0]->getID();

// delete by id
$deleted_rows = ProductDB::deleteProduct($product_id);

// see if delete
if ($deleted_rows > 0) {
    $delete_message = "Product was deleted successfully.";
} else {
    $delete_message = "Failed to delete product '$product_name'.";
}


/***************************************
 * Insert a product
 ****************************************/
// Sample data
$category_id = 1;
$code = 'telesssss';
$name = 'Fender Telecaster';
$description = 'NA';
$price = '949.99';
$discount_percent = '10';

// make new product to add
$category = CategoryDB::getCategory($category_id);
$new_product = new Product($category, $code, $name, $description, $price, $discount_percent);

// add product
$db_inserted_id = ProductDB::addProduct($new_product);

// see if add
if ($db_inserted_id !== false) {
    $insert_message = "Product added successfully";
} else {
    $insert_message = "Failed to add product.";
}

// Get the refresehd list
$products = ProductDB::getProductsByCategory($cat_id);


include 'home.php';

product_db

<?php
class ProductDB {    
    public static function getProductsByCategory($category_id) {
        $db = Database::getDB();
        $category = CategoryDB::getCategory($category_id);
        $query = 'SELECT categoryID, productID, productCode, productName, 
                     description, listPrice, discountPercent 
                  FROM products
                  WHERE categoryID = :category_id
                  ORDER BY productID';
        try {
            $statement = $db->prepare($query);
            $statement->bindValue(':category_id', $category_id);
            $statement->execute();
            
            $rows = $statement->fetchAll();
            $statement->closeCursor();
            
            $products = [];
            foreach ($rows as $row) {
                $products[] = self::loadProduct($row, $category);
            }
            return $products;
        } catch (PDOException $e) {
            Database::displayError($e->getMessage());
        }
    }
    
    private static function loadProduct($row, $category) {
        $product = new Product($category,
                               $row['productCode'],
                               $row['productName'],
                               $row['description'],
                               $row['listPrice'],
                               $row['discountPercent']);
        $product->setID($row['productID']);
        return $product;
    }

    public static function getProduct($product_id) {
        $db = Database::getDB();
        $query = 'SELECT categoryID, productID, productCode, productName, 
                     description, listPrice, discountPercent 
                  FROM products
                  WHERE productID = :product_id';
        try {
            $statement = $db->prepare($query);
            $statement->bindValue(':product_id', $product_id);
            $statement->execute();
            
            $row = $statement->fetch();
            $statement->closeCursor();
            
            $category = CategoryDB::getCategory($row['categoryID']);
            return self::loadProduct($row, $category);
        } catch (PDOException $e) {
            Database::displayError($e->getMessage());
        }
    }

    public static function addProduct($product) {
        $db = Database::getDB();
        $query = 'INSERT INTO products
                    (categoryID, productCode, productName, description,
                     listPrice, discountPercent, dateAdded)
                 VALUES
                    (:category_id, :code, :name, :description, :price,
                     :discount_percent, NOW())';
       try {
           $statement = $db->prepare($query);
           $statement->bindValue(':category_id', 
                   $product->getCategory()->getID());
           $statement->bindValue(':code', $product->getCode());
           $statement->bindValue(':name', $product->getName());
           $statement->bindValue(':description', $product->getDescription());
           $statement->bindValue(':price', $product->getPrice());
           $statement->bindValue(':discount_percent', 
                   $product->getDiscountPercent());
           $statement->execute();
           $statement->closeCursor();

           // Get the last product ID that was automatically generated
           return $db->lastInsertId();
       } catch (PDOException $e) {
           Database::displayError($e->getMessage());
       }
    }
    
    public static function updateProduct($product) {
        $db = Database::getDB();
        $query = 'UPDATE Products
                  SET productName = :name, productCode = :code,
                      description = :description, listPrice = :price,
                      discountPercent = :discount_percent,
                      categoryID = :category_id
                  WHERE productID = :product_id';
        try {
            $statement = $db->prepare($query);
            $statement->bindValue(':category_id', 
                   $product->getCategory()->getID());
            $statement->bindValue(':code', $product->getCode());
            $statement->bindValue(':name', $product->getName());
            $statement->bindValue(':description', $product->getDescription());
            $statement->bindValue(':price', $product->getPrice());
            $statement->bindValue(':discount_percent', 
                   $product->getDiscountPercent());
            $statement->bindValue(':product_id', $product->getID());
            $statement->execute();
            
            $row_count = $statement->rowCount();
            $statement->closeCursor();
            return $row_count;
        } catch (PDOException $e) {
            Database::displayError($e->getMessage());
        }
    }
    
    public static function deleteProduct($product_id) {
        $db = Database::getDB();
        $query = 'DELETE FROM products
                  WHERE productID = :product_id';
        try {
            $statement = $db->prepare($query);
            $statement->bindValue(':product_id', $product_id);
            $statement->execute();
            
            $row_count = $statement->rowCount();
            $statement->closeCursor();
            return $row_count;
        } catch (PDOException $e) {
            Database::displayError($e->getMessage());
        }
    }
    public static function getProductsByName($productName) {
        $db = Database::getDB();
        
        $query = 'SELECT categoryID, productID, productCode, productName, 
                     description, listPrice, discountPercent 
                  FROM products
                  WHERE productName LIKE :productName
                  ORDER BY productName';
                  
        try {
            $statement = $db->prepare($query);
            $statement->bindValue(':productName', '%' . $productName . '%');
            $statement->execute();
            
            $rows = $statement->fetchAll();
            $statement->closeCursor();
            
            $products = [];
            foreach ($rows as $row) {
                $category = CategoryDB::getCategory($row['categoryID']);
                $products[] = self::loadProduct($row, $category);
            }
            return $products;
        } catch (PDOException $e) {
            Database::displayError($e->getMessage());
        }
    }


}