PHP và MySQL – CRUD bảng order_items

Thêm order_item mới

Câu lệnh SQL

INSERT INTO ORDER_ITEMS(ID, PRODUCT_ID, ORDER_ID, QUANTITY) VALUES(NULL, :product_id, :order_id, :quantity)

Thực thi

<?php

require_once './core/mysql.php';
$pdo = get_pdo();

//Insert order
function insert_order_item($product_id, $order_id, $quantity){
    $sql = "INSERT INTO ORDER_ITEMS(ID, PRODUCT_ID, ORDER_ID, QUANTITY) VALUES(NULL, :product_id, :order_id, :quantity)";
    global $pdo;
    $stmt = $pdo->prepare($sql);
   
    $stmt->bindParam(':product_id', $product_id);
    $stmt->bindParam(':order_id', $order_id);
    $stmt->bindParam(':quantity', $quantity);

    $stmt->execute();
}

Cập nhật order_item

Câu lệnh SQL

UPDATE ORDER_ITEMS SET PRODUCT_ID=:product_id, ORDER_ID=:order_id, QUANTITY=:quantity WHERE ID=:id

Thực thi

function update_order_item($product_id, $order_id, $quantity){
    $sql = "UPDATE ORDER_ITEMS SET PRODUCT_ID=:product_id, ORDER_ID=:order_id, QUANTITY=:quantity WHERE ID=:id";
    global $pdo;
    $stmt = $pdo->prepare($sql);
   
    $stmt->bindParam(':product_id', $product_id);
    $stmt->bindParam(':order_id', $order_id);
    $stmt->bindParam(':quantity', $quantity);

    $stmt->execute();
}

Xoá order_item

Câu lệnh SQL

DELETE FROM ORDER_ITEMS WHERE ID=:id

Thực thi

function delete_order_item($id){
    $sql = "DELETE FROM ORDER_ITEMS WHERE ID=:id";
    global $pdo;
    $stmt = $pdo->prepare($sql);
    
    $stmt->bindParam(':id', $id);

    $stmt->execute();
}

Tìm kiếm order_item theo id

Câu lệnh SQL

SELECT * FROM ORDER_ITEMS WHERE ID=:id

Thực thi

function find_order_item($id){
    $sql = "SELECT * FROM ORDER_ITEMS WHERE ID=:id";
    global $pdo;
    $stmt = $pdo->prepare($sql);
    
    $stmt->bindParam(':id', $id);
    $stmt->execute();
    $stmt->setFetchMode(PDO::FETCH_ASSOC); 
     
    // Lấy danh sách kết quả
    $result = $stmt->fetchAll();
     
    // Lặp kết quả
    foreach ($result as $row){
        return array(
            'id' => $row['id'],
            'product_id' => $row['product_id'],
            'order_id' => $row['order_id'],
            'quantity' => $row['quantity'],
        );
    }

   

Lấy toàn bộ order_items

Câu lệnh SQL

SELECT * FROM ORDER_itemS

Thực thi

function get_order_items(){
    $sql = "SELECT * FROM ORDER_ITEMS";
    global $pdo;
    $stmt = $pdo->prepare($sql);
    

    $stmt->execute();
    $stmt->setFetchMode(PDO::FETCH_ASSOC); 
     
    // Lấy danh sách kết quả
    $result = $stmt->fetchAll();
     
    // Lặp kết quả
    $order_list = [];

    foreach ($result as $row){
        array_push($order_list, array(
            'id' => $row['id'],
            'product_id' => $row['product_id'],
            'order_id' => $row['order_id'],
            'quantity' => $row['quantity'],
        ));
    }

    return $order_list;
}

Tìm kiếm theo order

Câu lệnh SQL

SELECT * FROM ORDER_ITEMS WHERE ORDER_ID=:order_id

Thực thi

function get_order_item_by_order($order_id){
    $sql = "SELECT * FROM ORDER_ITEMS WHERE ORDER_ID=:order_id";
    global $pdo;
    $stmt = $pdo->prepare($sql);
    $stmt->bindParam(':order_id', $order_id);
    

    $stmt->execute();
    $stmt->setFetchMode(PDO::FETCH_ASSOC); 
     
    // Lấy danh sách kết quả
    $result = $stmt->fetchAll();
     
    // Lặp kết quả
    $order_list = [];

    foreach ($result as $row){
        array_push($order_list, array(
            'id' => $row['id'],
            'product_id' => $row['product_id'],
            'order_id' => $row['order_id'],
            'quantity' => $row['quantity'],
        ));
    }

    return $order_list;
}