Inserting values from one table to another table sql and php

user_12191112 0
Viewed 66

I'm currently doing a Book Rental System using PHP with a bit of OOP. I'm trying to insert the information from table book (id, bookTitle) and table users (user_id, fullname) to table borrowbook but I don't know how. I'm just a beginner in PHP

TABLE BOOK
id | bookId | bookTitle | authorName

TABLE USERS
user_id | uname | upass | fullname | uemail

TABLE BORROWBOOK
borrow_id | borrowDate | returnDate | b_Title | u_Borrower | status | b_id | u_id

u_id and b_id are foreign keys of user_id and id

Here's the output

Output Image

Here's the code

<?php
class Borrow {
    public $bookId;
    public $bookTitle;
    public $bookAuthor;
    public $id;

    public $a_id;
    public $authorId;
    public $fName;
    public $lName;

    public $borrow_id;
    public $borrowDate;
    public $returnDate;
    public $b_Title;
    public $u_Borrower;
    public $status;

    private $conn;
    public function __construct($db) {
        $this->conn = $db;
    }

    public function displayAll() {
        $sql = "SELECT * FROM book";
        $stmt = $this->conn->prepare($sql);
        $stmt->execute();

        return $stmt;
    }

    public function displayRequest() {
        $sql = "SELECT * FROM borrowbook";
        $stmt = $this->conn->prepare($sql);
        $stmt->execute();

        return $stmt;
    }

    public function selectBook() {
        $sql = "SELECT * 
                FROM book as A 
                LEFT JOIN borrowbook AS BK ON A.bookId = BK.b_id 
                LEFT JOIN users AS U ON U.user_id = BK.u_id 
                WHERE id = ?";
        
        //$sql = "SELECT * FROM book WHERE id = ?";
        $stmt = $this->conn->prepare($sql);
        $stmt->bindparam(1, $this->id);
        $stmt->execute();

        $row = $stmt->fetch(PDO::FETCH_ASSOC);

        $this->id = $row['id'];
        $this->bookId = $row['bookId'];
        $this->bookTitle = $row['bookTitle'];
        $this->authorName = $row['authorName'];
    }

    public function insertBook() {
        $sql = "INSERT INTO borrowbook SET borrowDate = ?, returnDate = ?";
        $stmt = $this->conn->prepare($sql);
        $stmt->bindparam(1, $this->borrowDate);
        $stmt->bindparam(2, $this->returnDate);
        $stmt->execute();
    }

    public function insertInfo() {
        $sql = "INSERT INTO borrowbook (u_Borrower, b_Title, b_id, u_id) 
                SELECT u.fullname, b.bookTitle, b.id, u.user_id 
                FROM users AS u, book AS b 
                WHERE u.user_id = b.id WHERE id = ?";
        
        $stmt = $this->conn->prepare($sql);
        $stmt->bindparam(1, $this->id);
        $stmt->execute();
    }

    public function readAll($from_record_num, $records_per_page) {
        $sql = "SELECT * FROM book ORDER BY bookId ASC LIMIT {$from_record_num}, {$records_per_page}";
        $stmt = $this->conn->prepare($sql);
        $stmt->execute();

        return $stmt;
    }

    public function countAll() {
        $sql = "SELECT id FROM book";
        $stmt = $this->conn->prepare($sql);
        $stmt->execute();

        $num = $stmt->rowCount();

        return $num;
    }
}
?>
<!-- INSERT INTO `bookborrow`(`b_id`, `b_Title`, `u_Borrower`) VALUES ((SELECT id FROM book WHERE id = 1),(SELECT bookTitle FROM book WHERE id = 1), (SELECT authorName FROM book WHERE id = 1)); -->

<!-- INSERT INTO `borrowbook`(`borrowDate`, `returnDate`, `b_Title`, `u_Borrower`, `status`) VALUES('June 10', 'June 13', (SELECT bookTitle FROM book WHERE id = 1), (SELECT fullname FROM users WHERE user_id = 5), 1) -->
mysql oop php
1 Answers

A few things:

The borrow table connects the book and user table, so you need the id from each table:

Insert borrowed book:

public function insertInfo($user_id, $book_id) {
    $sql = "INSERT INTO borrowbook (u_Borrower, b_Title, b_id, u_id) 
            SELECT u.fullname, b.bookTitle, b.id, u.user_id 
            FROM users AS u, book AS b 
            WHERE u.user_id = {$user_id} AND b.id = {$book_id}";
    
    $stmt = $this->conn->prepare($sql);
    $stmt->execute();
}

When updating a row, use UPDATE, not INSERT. And be sure to include the where clause or every row will be updated.

Update book:

public function updateBook($user_id, $book_id) {
    $sql = "UPDATE borrowbook SET borrowDate = ?, returnDate = ? WHERE u_id = {$user_id} AND b_id = {$book_id}";
    $stmt = $this->conn->prepare($sql);
    $stmt->bindparam(1, $this->borrowDate);
    $stmt->bindparam(2, $this->returnDate);
    $stmt->execute();
}

As @Barmar mentioned, when joining tables, you only want to store the IDs and new information. Don't save the book title because it is already stored in the book table.

The preferred structure of the borrow table is this:

TABLE BORROWBOOK
borrow_id | borrowDate | returnDate | status | b_id | u_id

If you move the status to the book table, you can update the status using this method:

public function setBookStatus() {
    $sql = "UPDATE book SET status = ? WHERE id = ?";
    $stmt = $this->conn->prepare($sql);
    $stmt->bindparam(1, $this->status);
    $stmt->bindparam(2, $this->b_id);
    $stmt->execute();
}

In the page, you need to store the book id (b_id) for the update.

user_138780340
Related